sql - How can I extract just the time component from an Oracle timestamp value?

Question

I'm attempting to create a breakdown of requests per hour. As such the date part of the TimeStamp object needs to be ignored. This is what I've tried to far:

select 
    trunc(request_time, 'HH'),
    count(*)
    
from 
    service_request

group by
    trunc(request_time, 'HH');

This seems to group all data into one hour sections but also groups by the day. How do I go about removing the date part so I end up with results like:

Time     Count
--------------
00:00        1
01:00        4
02:00        2

question from:https://stackoverflow.com/questions/65869691/how-can-i-extract-just-the-time-component-from-an-oracle-timestamp-value

Answer 1

You should be able to do

select to_number( to_char( request_time, 'HH24' ) ),
       count(*)
  from service_request
 group by to_number( to_char( request_time, 'HH24' ) );

The to_number is probably not strictly necessary but it makes more sense to return a numeric hour than a string hour for things like sorting.

If the data type is actually a timestamp, then you could improve this

select extract( hour from request_time ),
       count(*)
  from service_request
 group by extract( hour from request_time );