Rounding and truncating DATE at 5,10,15 and 30 min

26/10/19 11:20
Quite common need in business application: round the minute of an hour to specific value of which common ones are 5,10,15 and 30 minutes
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS' ;
with data as (
select to_date('26-10-2019 13:59:59', 'DD-MM-YYYY HH24:MI:SS') as d from dual
union all
select to_date('26-10-2019 13:29:59', 'DD-MM-YYYY HH24:MI:SS') as d from dual
union all
select to_date('26-10-2019 13:45:12', 'DD-MM-YYYY HH24:MI:SS') as d from dual
union all
select to_date('26-10-2019 13:14:12', 'DD-MM-YYYY HH24:MI:SS') as d from dual
)
select
d,
trunc(d - mod( extract(minute from cast(d as timestamp)) , 30) / 1440, 'MI') as trunc_at_half_hour,
mod( extract(minute from cast(d as timestamp)) , 30) as mod_30,
trunc(d + (30 - mod( extract(minute from cast(d as timestamp)) , 30)) / 1440, 'MI') as round_at_half_hour,

trunc(d - mod( extract(minute from cast(d as timestamp)) , 15) / 1440, 'MI') as trunc_at_15_min,
mod( extract(minute from cast(d as timestamp)) , 15) as mod_15,
trunc(d + (15 - mod( extract(minute from cast(d as timestamp)) , 15)) / 1440, 'MI') as round_at_15_min,

trunc(d - mod( extract(minute from cast(d as timestamp)) , 10) / 1440, 'MI') as trunc_at_10_min,
mod( extract(minute from cast(d as timestamp)) , 10) as mod_10,
trunc(d + (10 - mod( extract(minute from cast(d as timestamp)) , 10)) / 1440, 'MI') as round_at_10_min,

trunc(d - mod( extract(minute from cast(d as timestamp)) , 5) / 1440, 'MI') as trunc_at_5_min,
mod( extract(minute from cast(d as timestamp)) , 5) as mod_5,
trunc(d + (5 - mod( extract(minute from cast(d as timestamp)) , 5)) / 1440, 'MI') as round_at_5_min
from data ;
D
TRUNC_AT_HALF_HOUR
MOD_30
ROUND_AT_HALF_HOUR
TRUNC_AT_15_MIN
MOD_15
ROUND_AT_15_MIN
TRUNC_AT_10_MIN
MOD_10
ROUND_AT_10_MIN
TRUNC_AT_5_MIN
MOD_5
ROUND_AT_5_MIN
2019-10-26T13:59:59Z
2019-10-26T13:30:00Z
29
2019-10-26T14:00:00Z
2019-10-26T13:45:00Z
14
2019-10-26T14:00:00Z
2019-10-26T13:50:00Z
9
2019-10-26T14:00:00Z
2019-10-26T13:55:00Z
4
2019-10-26T14:00:00Z
2019-10-26T13:29:59Z
2019-10-26T13:00:00Z
29
2019-10-26T13:30:00Z
2019-10-26T13:15:00Z
14
2019-10-26T13:30:00Z
2019-10-26T13:20:00Z
9
2019-10-26T13:30:00Z
2019-10-26T13:25:00Z
4
2019-10-26T13:30:00Z
2019-10-26T13:45:12Z
2019-10-26T13:30:00Z
15
2019-10-26T14:00:00Z
2019-10-26T13:45:00Z
0
2019-10-26T14:00:00Z
2019-10-26T13:40:00Z
5
2019-10-26T13:50:00Z
2019-10-26T13:45:00Z
0
2019-10-26T13:50:00Z
2019-10-26T13:14:12Z
2019-10-26T13:00:00Z
14
2019-10-26T13:30:00Z
2019-10-26T13:00:00Z
14
2019-10-26T13:15:00Z
2019-10-26T13:10:00Z
4
2019-10-26T13:20:00Z
2019-10-26T13:10:00Z
4
2019-10-26T13:15:00Z