Re: Group by range in hour of day - Mailing list pgsql-general

From David G. Johnston
Subject Re: Group by range in hour of day
Date
Msg-id CAKFQuwbw==Sye=RdSLGTAwwL4ukTQHt345bGT5K0ZQ9XixLppQ@mail.gmail.com
Whole thread Raw
In response to Re: Group by range in hour of day  (Israel Brewster <israel@ravnalaska.net>)
Responses Re: Group by range in hour of day
List pgsql-general
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. 

pgsql-general by date:

Previous
From: Israel Brewster
Date:
Subject: Re: Group by range in hour of day
Next
From: Israel Brewster
Date:
Subject: Re: Group by range in hour of day