How can I round down hour data to quarters?
Eg.
| Hour (HH24:MI:SS) | Expected result | Query result |
|--|--|--|
| 10:04:00| 10:00:00|10:00:00|
| 10:12:00| 10:00:00|10:15:00|
| 10:14:59| 10:00:00|10:15:00|
| 10:15:00| 10:15:00|10:15:00|
| 10:15:02| 10:15:00|10:15:00|
| 10:16:00| 10:15:00|10:15:00|
I wrote something like this, but it only rounding to nearest quarter :/
with dates as (
select to_date('01/01/2022 10:04:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:12:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:14:59', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:02', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:16:00', 'mm/dd/yyyy hh:mi:ss') d from dual
)
select d
,TRUNC (d) + (ROUND ((d - TRUNC (d)) * 96) / 96)
from dates;
>Solution :
You can use the MODulo function to find the number of minutes and seconds to subtract:
with dates as (
select to_date('01/01/2022 10:04:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:12:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:14:59', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:00', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:15:02', 'mm/dd/yyyy hh:mi:ss') d from dual
union all
select to_date('01/01/2022 10:16:00', 'mm/dd/yyyy hh:mi:ss') d from dual
)
select d,
d - MOD((d - TRUNC(d))*24*60, 15)/24/60 AS rounded
from dates;
Which outputs:
D ROUNDED 2022-01-01 10:04:00 2022-01-01 10:00:00 2022-01-01 10:12:00 2022-01-01 10:00:00 2022-01-01 10:14:59 2022-01-01 10:00:00 2022-01-01 10:15:00 2022-01-01 10:15:00 2022-01-01 10:15:02 2022-01-01 10:15:00 2022-01-01 10:16:00 2022-01-01 10:15:00
db<>fiddle here