Group By Date with specific hours

Question

I would like to do a group by on this bunch of data, based on the date, but with 23:00 to 22:59 as begin hour and end hour.

For example, for these data :

2014-11-05 23:04:58.0
2014-11-05 23:23:03.0
2014-11-05 23:51:54.0
2014-11-05 23:54:29.0
2014-11-05 23:58:14.0
2014-11-06 06:13:23.0
2014-11-06 06:29:58.0
2014-11-06 06:58:27.0
2014-11-06 08:21:56.0
2014-11-06 08:24:01.0
2014-11-06 23:13:47.0
2014-11-06 23:27:55.0
2014-11-06 23:30:34.0
2014-11-06 23:40:52.0
2014-11-06 23:58:53.0
2014-11-07 06:00:37.0
2014-11-07 06:23:47.0
2014-11-07 06:28:27.0
2014-11-07 06:44:13.0

I'm expecting something like :

Count | Date
-------------------
10    | 2014-11-06
9     | 2014-11-07

Answer 1

You can get this aggregation by adding one hour to the date:

select date(timestamp + interval '1' hour) as thedate, count(*)
from table t
group by date(timestamp + interval '1' hour);

Answer 2

You can use the EXTRACT function to return the hour and group by that. For example:

SELECT COUNT(date), date
from tableName
group by EXTRACT(hour from Date)

For more info, see here