All Forums Database
mrjkirkpatrick 2 posts Joined 06/15
02 Sep 2016
Grouping while combining Date and Hour

Hello,
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).
e.g:
--

SELECT row_added_ts original_time,
count(distinct case_id) cases
FROM X.data
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.

dnoeth 4628 posts Joined 11/04
03 Sep 2016

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

 

Or you subtract minutes & seconds:
row_added_ts
  - (extract(minute from row_added_ts) * interval '1' minute)
  - (extract(second from row_added_ts) * interval '1' second)

Dieter

mrjkirkpatrick 2 posts Joined 06/15
04 Sep 2016

Thank you, very helpful. Grouping by the two columns does seem easiest, though I'll try the other options as well.
 

You must sign in to leave a comment.