On Tuesday, March 17, 2015, Israel Brewster <
israel@ravnalaska.net> wrote:
> On Mar 17, 2015, at 8:09 AM, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
>
>>> 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
Right, thanks. That makes sense. So next question: how do I get the "active" time per hour from this? To use the same example that came up with this result set:
Which is why you do not (only?) want to convert your data to hour-of-day but want to create timestamp end points. Then you simply do timestamp subtraction to get durations which you can then sum together.
David J.