Oracle SQL - hour slabs

Question

I have the following data:

CUSTOMER_ORDERS:

CUST_ORDER_NO CREATE_DATETIME
WEB904204965    17-SEP-21 03.12.45.719000000 PM
WEB904204966    17-SEP-21 03.16.45.719000000 PM

I want to check the number of orders in every hour, like:

COUNT SLAB
2     1-2 PM
4     2-3 PM

How to write the query for this?

Answer 1

You can trunc the date/time or extract just the hour component, then GROUP BY that expression.

Here's a test case with sample SQL:

Working Test Case with a little data

SELECT COUNT(*) AS n
     , to_char(dt, 'HH') AS slab
  FROM test
 GROUP BY to_char(dt, 'HH')
;

Result:

We could have used 'HH24' to show the 24 hour value or 'HH AM' to show the 'hours plus AM/PM' indicator.

SELECT to_char(current_date, 'HH24') AS slab FROM dual;

SELECT to_char(current_date, 'HH AM') AS slab FROM dual;