Re: Group by range in hour of day

> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> wrote:
>
> So next question: how do I get the "active" time per hour from this?
>
> I think you just SUM() over the intersection between each hourly window and each event, right? This might be easiest using tsrange, something like this:

Sounds reasonable. I've never worked with range values before, but it does seem appropriate here.

>
> SUM(extract(minutes from (tsrange(start_time, end_time) && tsrange(h, h + interval '1 hour'))::interval))
>
> I think you'll have to implement ::interval yourself though, e.g. here:
>
> http://dba.stackexchange.com/questions/52153/postgresql-9-2-number-of-days-in-a-tstzrange

Gotcha

>
> Also as mentioned you'll have to convert h from an integer [0,23] to a timestamp, but that seems pretty easy. Assuming start_time and end_time are UTC that's just adding that many hours to UTC midnight of the same day.
>
> Some weird edge cases to be careful about: activities that cross midnight. Activities that last more than one full day, e.g. start 3/15 and end 3/17.

Right. And I will run into some of those (at least the crossing midnight), so I'll keep an eye out.

-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

>
> Paul
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general