>> 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