Re: Group by range in hour of day

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 easiest
usingtsrange, 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

>
>>
>> Also as mentioned you'll have to convert h from an integer [0,23] to a timestamp, but that seems pretty easy.
Assumingstart_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@aklaver.com