I'm trying to create a row for each MINUTE in a day and that appears to be working fine.
I am trying to group the data in 5 MINUTE intervals and was expecting to see '5' for each column under the count(*) but that doesn't seem to be the case.
Can someone please advise me on how to rectify this situation. Below is my test case. Thanks to all who answer.
CREATE TABLE time_data (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE
);
Generate a row for each minute in a day
INSERT INTO time_data(dt)
select timestamp '2022-11-01 00:00:00' + numtodsinterval(rownum-1*1,'MINUTE')
from dual
CONNECT BY LEVEL <= (24*60);
with rws as (
select trunc ( dt ) dy,
trunc (dt, 'mi' ) mins,
5 / 1440 time_interval
from time_data
), intervals as (
select dy + (
floor ( ( mins - dy ) / time_interval ) * time_interval
) start_datetime
from rws
)
select start_datetime, count(*) from intervals
group by start_datetime
order by start_datetime;