Skip to Main Content

Group rows in 5 minute intervals

BeefStuNov 1 2022 — edited Nov 1 2022

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;

This post has been answered by Solomon Yakobson on Nov 1 2022
Jump to Answer
Comments
Post Details
Added on Nov 1 2022
15 comments
172 views