grouping dates in postgres using date_trunc

well recently I started to write some statistics functions for my miniX application and so I was woundering, how can you easily execute queries like:

give me the count of object for the last 24h grouped by hour

which should be rather simple, if you know about the functions 'date_trunc' in postgres.

So 5 minutes later some sql appeared...


"select max(object_count) as object,date_trunc('hour',created_at) as date from bbstatistic_snapshot where object_name = ? and created_at between ? and ? group by date_trunc('hour',created_at) order by date_trunc('hour',created_at)"


which works rather well and get's the job done, but the speed is a touch slow.

Results should look like:


object | date
--------+---------------------
1742 | 2011-10-13 15:00:00
1742 | 2011-10-13 16:00:00
1742 | 2011-10-13 17:00:00
1742 | 2011-10-13 18:00:00
2102 | 2011-10-13 19:00:00
2102 | 2011-10-13 20:00:00
2057 | 2011-10-13 21:00:00
1899 | 2011-10-13 22:00:00
1803 | 2011-10-13 23:00:00
1742 | 2011-10-14 00:00:00
1742 | 2011-10-14 01:00:00
1742 | 2011-10-14 02:00:00
1742 | 2011-10-14 03:00:00
1742 | 2011-10-14 04:00:00
1742 | 2011-10-14 05:00:00
1742 | 2011-10-14 06:00:00