JayMaker
JayMaker

Reputation: 13

Oracle query Round up or down to nearest 15 minute interval

08-SEP-20 08:55:05
08-SEP-20 15:36:13

The query below is working correctly for 15:36:13 in that it rounds to 15:30 but the 8:55:05 is rounding down to 08:45 when it should be rounding to 09:00

select event_date,trunc(event_date,'mi') - numtodsinterval(  mod(to_char(event_date,'mi'),15),  'minute'  ) as nearest_quarter
from time_source_in_all where empno = '002307718' and event_date between  '8-sep-2020' and '9-sep-2020'

Upvotes: 0

Views: 4613

Answers (2)

MT0
MT0

Reputation: 167822

You can use:

SELECT event_date,
       TRUNC( event_date, 'HH' )
         + ROUND( EXTRACT( MINUTE FROM CAST( event_date AS TIMESTAMP ) ) / 15 )
           * INTERVAL '15' MINUTE
         AS rounded_15_event_date
FROM   table_name

or:

SELECT event_date,
       TRUNC( event_date, 'HH' )
         + ROUND( ( event_date - TRUNC( event_date, 'HH' ) ) * 24 * 4 )
           * INTERVAL '15' MINUTE
         AS rounded_15_event_date
FROM   table_name

Which, for your sample data:

CREATE TABLE table_name ( event_date ) AS
SELECT DATE '2020-09-08' + INTERVAL '08:55:05' HOUR TO SECOND FROM DUAL UNION ALL
SELECT DATE '2020-09-08' + INTERVAL '15:36:13' HOUR TO SECOND FROM DUAL

Both output:

EVENT_DATE          | ROUNDED_15_EVENT_DATE
:------------------ | :--------------------
2020-09-08 08:55:05 | 2020-09-08 09:00:00  
2020-09-08 15:36:13 | 2020-09-08 15:30:00  

db<>fiddle here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think this will do what you want:

select trunc(event_date, 'HH') + round(extract(minute from event_date) / 15) * interval '15' minute )
. . . 

Note: I prefer the extract() because it is standard SQL. But it assumes that the column is a timestamp and not a date.

or the equivalent:

select trunc(event_date, 'HH') + numtodsinterval(round(to_number(to_char(event_date, 'MI')) / 15) * 15, 'minute')

Upvotes: 1

Related Questions