how do I group by by hour in postgresql with a "time" field?
Solution 1:
use date_trunc:
group by cdate, date_trunc('hour', ctime)
Solution 2:
I think date_part('hour', ctime) is a better choice if you just want to group based on the hour.
date_part('hour', timestamp '2001-02-16 20:38:40') ---> 20
date_trunc('hour', interval '2 days 3 hours 40 minutes') ---> 2 days 03:00:00