How to count the number of rows by datetime field with the division into time intervals
variable interval_minutes number;
exec :interval_minutes := 10;
select
trunc(registered_at, 'HH24')
+ trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440,
count(*)
from
clients
group by
trunc(registered_at, 'HH24')
+ trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440
order by
1
;
Based on: Summarizing data over time - by time interval
SQL> select trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440 as "Start_Time",
(trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440)+ (.000694 * 10) as "End_Time",
count(*)
from clients
group by trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440,
(trunc(REGISTERED_AT,'hh')+trunc(to_char(REGISTERED_AT,'mi')/10)*10/1440)+ (.000694 * 10); 2 3 4 5 6
Start_Time End_Time COUNT(*)
------------------- ------------------- ----------
2016/08/01 15:00:00 2016/08/01 15:10:00 2
2016/08/01 16:30:00 2016/08/01 16:40:00 2
2016/08/01 15:40:00 2016/08/01 15:50:00 1
In case you need a more generic solution, you can use a function as this:
CREATE OR REPLACE FUNCTION MakeInterval(ts IN TIMESTAMP, roundInterval IN INTERVAL DAY TO SECOND) RETURN TIMESTAMP DETERMINISTIC IS
denom INTEGER;
BEGIN
IF roundInterval >= INTERVAL '1' HOUR THEN
denom := EXTRACT(HOUR FROM roundInterval);
IF MOD(24, denom) <> 0 THEN
RAISE VALUE_ERROR;
END IF;
RETURN TRUNC(ts) + TRUNC(EXTRACT(HOUR FROM ts) / denom) * denom * INTERVAL '1' HOUR;
ELSIF roundInterval >= INTERVAL '1' MINUTE THEN
denom := EXTRACT(MINUTE FROM roundInterval);
IF MOD(60, denom) <> 0 THEN
RAISE VALUE_ERROR;
END IF;
RETURN TRUNC(ts, 'hh') + TRUNC(EXTRACT(MINUTE FROM ts) / denom) * denom * INTERVAL '1' MINUTE;
ELSE
denom := EXTRACT(SECOND FROM roundInterval);
IF MOD(60, denom) <> 0 THEN
RAISE VALUE_ERROR;
END IF;
RETURN TRUNC(ts, 'mi') + TRUNC(EXTRACT(SECOND FROM ts) / denom) * denom * INTERVAL '1' SECOND;
END IF;
END MakeInterval;
select count(*)
MakeInterval(registered_at, INTERVAL '10' MINUTE) as start_,
MakeInterval(registered_at, INTERVAL '10' MINUTE) + INTERVAL '10' MINUTE as end_
from clients
group by MakeInterval(registered_at, INTERVAL '10' MINUTE);
Please note, the function will raise an exception if the interval does not fit in whole number, for example INTERVAL '11' MINUTE
would raise an error.