> > >-- > 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 Here another approach while building an hourly serie for each start/end pair, truncated to the hours: create temp table t (s timestamptz, e timestamptz); insert into t select '2015-03-16 08:15:00','2015-03-16 09:35:00'; insert into t select '2015-03-16 09:15:00','2015-03-16 11:05:00'; insert into t select '2015-03-16 08:00:00','2015-03-16 11:45:00'; insert into t select '2015-03-17 15:15:00','2015-03-18 11:45:00'; insert into t select '2015-03-17 20:15:00','2015-03-18 11:45:00'; insert into t select '2015-03-17 21:15:00','2015-03-18 10:10:00'; insert into t select '2015-03-18 23:30:00','2015-03-19 01:30:00'; SELECT ser, SUM( case when e - ser < interval '1 hour' then e-ser --end interval when s >= ser then interval '1 hour' - (s - ser) --start interval else interval '1 hour' end ) as time_tot FROM (select e,s, generate_series(date_trunc('hour',s), date_trunc('hour',e), '1 hour') ser from t )foo group by ser order by 1 regards, Marc Mamin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
