test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) where
h between extract(hour from start_time) and extract(hour from end_time) group by h
order by h;
h | count
----+-------
8 | 2
9 | 3
10 | 2
11 | 2
Note if you always want all 24 rows with a count of 0 when appropriate
(which seems common in reports with tables or plots), you can just tweak
the above query to use a left join: FROM generate_series(0, 23) AS s(h)
LEFT OUTER JOIN start_end ON h BETWEEN ...
Paul
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
