CODEWITHSUNDEEP
sqloracle-databasedatetime
Cyreaver
Cyreaver

Reputation: 17

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

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

Upvotes: 0

Views: 1343

Answers (1)

Justin Cave
Justin Cave

Reputation: 231881

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 );

Upvotes: 2

Related Questions