Hello everybody,
I have a question hoping someone could advise. I was wondering how I can group by on a range of few hours. Let me take an example to illustrate the problem.
I have a table like this, with an id, a date and a label (which is not relevant in here).
CREATE TABLE logs
(
id NUMBER(10) UNIQUE,
creation_date DATE,
lbl VARCHAR2(100)
);
INSERT INTO logs(id, creation_date, lbl) VALUES(100, TO_DATE('01/01/2019 HH24:MI', 'DD/MM/YYYY 01:12'), 'op1');
INSERT INTO logs(id, creation_date, lbl) VALUES(102, TO_DATE('01/01/2019 HH24:MI', 'DD/MM/YYYY 01:13'), 'op2');
INSERT INTO logs(id, creation_date, lbl) VALUES(109, TO_DATE('01/01/2019 HH24:MI', 'DD/MM/YYYY 03:30'), 'op3');
INSERT INTO logs(id, creation_date, lbl) VALUES(110, TO_DATE('01/01/2019 HH24:MI', 'DD/MM/YYYY 06:18'), 'op4');
INSERT INTO logs(id, creation_date, lbl) VALUES(111, TO_DATE('01/01/2019 HH24:MI', 'DD/MM/YYYY 07:57'), 'op5');
INSERT INTO logs(id, creation_date, lbl) VALUES(120, TO_DATE('02/01/2019 HH24:MI', 'DD/MM/YYYY 04:57'), 'op6');
INSERT INTO logs(id, creation_date, lbl) VALUES(135, TO_DATE('05/01/2019 HH24:MI', 'DD/MM/YYYY 22:57'), 'op6');
I'd like to have a query that produces the following result. From the minimum creation date that is in my table, until midnight of the maximum creation date, I have 6 hours interval and count how many records I have in them.
START_RANGE END_RANGE COUNT(*)
--------------- ---------------- --------
01/01/2019 00:00 01/01/2019 06:00 3
01/01/2019 06:00 01/01/2019 12:00 1
01/01/2019 12:00 01/01/2019 18:00 0
01/01/2019 18:00 02/01/2019 00:00 0
02/01/2019 00:00 02/01/2019 06:00 1
02/01/2019 06:00 02/01/2019 12:00 0
02/01/2019 12:00 02/01/2019 18:00 0
02/01/2019 18:00 03/01/2019 00:00 0
03/01/2019 00:00 03/01/2019 06:00 0
03/01/2019 06:00 03/01/2019 12:00 0
03/01/2019 12:00 03/01/2019 18:00 0
03/01/2019 18:00 04/01/2019 00:00 0
04/01/2019 00:00 04/01/2019 06:00 0
04/01/2019 06:00 04/01/2019 12:00 0
04/01/2019 12:00 04/01/2019 18:00 0
04/01/2019 18:00 05/01/2019 00:00 1
Is this doable? Any suggestion how to achieve this? Im using Oracle 11g.
Regards,