-- Adrian Klaver adrian.klaver@xxxxxxxxxxx On Tue, Mar 17, 2015, at 11:30 AM, Adrian Klaver wrote: > On 03/17/2015 10:57 AM, Israel Brewster wrote: > > > > > >> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth <pj@xxxxxxxxxxxxxxxxxxxxxxxx> 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 > > > My take on this is using CASE. > > Rough sketch: > > > WHEN > date_trunc('hour', end_time) < h > THEN > end_time - start_time > ELSE > (date_trunc('hour', start_time) + interval '1 hr') - start_time > as > active_time Aah, should be WHEN date_trunc('hour', end_time) < h + 1 THEN end_time - start_time ELSE (date_trunc('hour', start_time) + interval '1 hr') - start_time as active_time > > > > >> > >> 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 > > ----------------------------------------------- > > > > > > > > -- > Adrian Klaver > adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
