I couldn't find exactly this question among the great date/time posts on the forum.
I have a date field (row_added_ts) with the format: "12/29/2014 6:34:42 PM"
My end goal is to group 'cases' by the hour, but with the date included (so a simple HOUR EXTRACT seemingly won't work).
SELECT row_added_ts original_time,
count(distinct case_id) cases
GROUP BY 1
But I would like the grouping to be by hour (not rounded).
So 1:00:00 PM - 1:59:59 PM would be grouped 1:00 PM, 2:00:00 PM - 2:59:59 PM would be grouped 2:00 PM, so forth.
Any help is appreciated.
The easiest way is to group by two columns:
CAST(row_added_ts AS DATE), EXTRACT(HOUR FROM row_added_ts)
You can also cast to a string, but your format will not allow correct sorting:
to_char(row_added_ts , 'mm/dd/yyyy HH12 PM')
Internationla standard format sorts ok:
to_char(row_added_ts , 'yyyy-mm-dd HH24')
Or you cast to a string & back:
cast(to_char(row_added_ts , 'yyyy-mm-dd HH24') as timestamp(0) format 'yyyy-mm-ddBhh')
- (extract(minute from row_added_ts) * interval '1' minute)
- (extract(second from row_added_ts) * interval '1' second)