WITH study AS ( -- Test Sample -- SELECT sysdate - (LEVEL-1)/24 study_dttm FROM dual CONNECT BY LEVEL <= 26 ) SELECT TO_CHAR(study_dttm, 'yyyymmdd') || LPAD(FLOOR(TO_CHAR(study_dttm, 'hh24') / 2) * 2, 2, '0') tm , COUNT(*) cnt FROM study -- WHERE study_dttm >= sysdate - 1 WHERE study_dttm >= TRUNC(sysdate) - 1 + (FLOOR(TO_CHAR(sysdate, 'hh24') / 2) * 2 + 2) / 24 GROUP BY TO_CHAR(study_dttm, 'yyyymmdd') || LPAD(FLOOR(TO_CHAR(study_dttm, 'hh24') / 2) * 2, 2, '0') ORDER BY tm ;