Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DBTB ("TIMESTAMP" date, "ADDR" int);
INSERT ALL 
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 17:15:00', 'YYYY-MM-DD HH24:MI:SS'), 12345)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 17:20:00', 'YYYY-MM-DD HH24:MI:SS'), 12345)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 17:25:00', 'YYYY-MM-DD HH24:MI:SS'), 12345)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 17:27:00', 'YYYY-MM-DD HH24:MI:SS'), 67890)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 18:10:00', 'YYYY-MM-DD HH24:MI:SS'), 55555)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 18:20:00', 'YYYY-MM-DD HH24:MI:SS'), 55555)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 18:30:00', 'YYYY-MM-DD HH24:MI:SS'), 66666)
    INTO DBTB ("TIMESTAMP", "ADDR")
         VALUES (TO_DATE('2021-10-19 18:43:00', 'YYYY-MM-DD HH24:MI:SS'), 77777)
SELECT * FROM dual
;
8 rows affected

SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');


HOUR
COUNT


17
2

18
3


SELECT TRUNC("TIMESTAMP") DAY,
       TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
GROUP BY TRUNC("TIMESTAMP"), TO_CHAR("TIMESTAMP", 'HH24');


DAY
HOUR
COUNT


19-OCT-21
17
2

19-OCT-21
18
3


SELECT TO_CHAR("TIMESTAMP", 'HH24') HOUR,
       COUNT(DISTINCT "ADDR") COUNT
FROM DBTB
WHERE TRUNC("TIMESTAMP") = TO_DATE('19-Oct-2021', 'DD-MON-YYYY')
GROUP BY TO_CHAR("TIMESTAMP", 'HH24');


HOUR
COUNT


17
2

18
3