From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | Israel Brewster <israel(at)ravnalaska(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Group by range in hour of day |
Date: | 2015-03-17 17:30:08 |
Message-ID: | 550864A0.3050908@illuminatedcomputing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
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
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.
Paul
From | Date | Subject | |
---|---|---|---|
Next Message | Israel Brewster | 2015-03-17 17:57:40 | Re: Group by range in hour of day |
Previous Message | Israel Brewster | 2015-03-17 17:22:50 | Re: Group by range in hour of day |