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;  

CodePudding user response：

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