Re: Group by range in hour of day - Mailing list pgsql-general

> On Mar 17, 2015, at 1:41 PM, Marc Mamin <M.Mamin@intershop.de> 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@illuminatedcomputing.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
easiestusing 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
spendsome 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
functioningquery 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general