Re: Group by range in hour of day

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

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(at)illuminatedcomputing(dot)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 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(dot)klaver(at)aklaver(dot)com