> On Mar 17, 2015, at 1:41 PM, Marc Mamin <M.Mamin@xxxxxxxxxxxx> wrote: > > >>> 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 > > I missed the case when the start and end points are in the same hour: > > SELECT ser, SUM( > case when e - ser < interval '1 hour' then e - greatest(ser,s) --end interval or s&e in same hour > 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 > > Marc That you all for the suggestions. I think I have it working now, using CASE statements similar to these. I'll have to spend some time playing around with the tsrange suggestions as well, since I think it could end up being cleaner and safer (especially, as mentioned, for any cases where there may be date changes involved), but at least I now have a functioning query I can tweak. Thanks again! ----------------------------------------------- Israel Brewster Systems Analyst II Ravn Alaska 5245 Airport Industrial Rd Fairbanks, AK 99709 (907) 450-7293 ----------------------------------------------- > > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
