I would like to aggregate my data into 15 minute segments (quarters of the hour). To do this, I have written some code that generates 15 minute datetime blocks.
SELECT
TRUNC(SYSDATE,'hh') + 0.25/24 - (ROWNUM) *0.25/ 24
AS time_start,
ROWNUM,
TRUNC(SYSDATE,'hh') + 0.25/24 - (ROWNUM - 1) *0.25/ 24
AS time_end
FROM widsys.consist
WHERE ROWNUM <3000
ORDER BY sysdate
The problem with my code is because it uses an hour truncation, it will only generate time stamps from the beginning of the most recent hour. For example, it is 11:49AM
now so the first stamp generated is 11:00AM.
I need it to generate stamps from the beginning of the last 15 minute block (11:45AM
from the example above). Can anyone please help me?
This will get you the nearest quarter.
select sysdate,
trunc(sysdate,'mi') - --truncate to the nearest minute
numtodsinterval( --convert the minutes in number to interval type and subtract.
mod(to_char(sysdate,'mi'),15), --find the minutes from the nearest quarter
'minute'
) as nearest_quarter
from dual;
Output:
sysdate nearest_quarter
-----------------------------------------------------------------
October, 11 2013 05:54:24+0000 October, 11 2013 05:45:00+0000
October, 11 2013 05:22:24+0000 October, 11 2013 05:15:00+0000
Use this as your starting value and then iterate over this.
with cte as(
select trunc(sysdate,'mi') -
numtodsinterval(mod(to_char(sysdate,'mi'),15),'minute') as nearest_quarter
from dual
)
select nearest_quarter - numtodsinterval((level - 1)*15, 'minute'),
nearest_quarter - numtodsinterval((level - 2)*15, 'minute')
from cte
connect by level <= 10;
Demo.
Another with width_bucket:
SELECT trunc(SYSDATE, 'hh')+ (width_bucket(to_number(to_char(SYSDATE, 'mi')), 0 , 60, 4)-1)*15/(24*60) x
FROM dual;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With