Grouping while combining Date and Hour

Database

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.

2 REPLIES
Junior Contributor

Re: Grouping while combining Date and Hour

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)

Re: Grouping while combining Date and Hour

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