Oracle count records by hour

Aggregating Oracle data by hour is surprisingly fiddly. You can’t just GROUP BY the date column directly because it includes the full timestamp. The EXTRACT(HOUR FROM CAST(...AS TIMESTAMP)) pattern is the reliable way to pull out just the hour component for grouping and filtering.

Let us assume that there is a table:

MESSAGE_ARCHIVE

In this table there are two columns:

WHENRECEIVED (Date)
TOPIC (VARCHAR(20))

• SQL to count the number of topics received at 9 PM on 15-07-2020

select topic,count(*) from MESSAGE_ARCHIVE 
WHERE 
trunc(WHENRECEIVED)=to_date('15-07-2020','DD-MM-YYYY')
AND EXTRACT (HOUR FROM CAST (WHENRECEIVED AS TIMESTAMP)) > 19
GROUP BY topic 
ORDER BY count(*) DESC
;