0

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
Cyreaver
  • 17
  • 4

1 Answers1

2

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 );
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Thank you very much this worked perfectly. I'll go have a look at the to_char function. – Cyreaver Jan 24 '21 at 11:03
  • @Cyreaver - Since you updated your question to say it was actually a timestamp, I updated my answer to use the `extract` function instead. That's likely to be a cleaner approach. – Justin Cave Jan 24 '21 at 11:04
  • Thanks again, that is much cleaner to read. Is there any way to have the hour displayed as 00:00 / 01:00 instead of 0/1? – Cyreaver Jan 24 '21 at 11:19
  • @Cyreaver - If you want a string, you could use `to_char( trunc(request_time,'HH'), 'HH24:MI' )`. – Justin Cave Jan 24 '21 at 12:02