From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Israel Brewster <israel(at)ravnalaska(dot)net>, Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Group by range in hour of day |
Date: | 2015-03-17 21:02:53 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D8828B95B55@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
>--
> 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
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Mamin | 2015-03-17 21:41:14 | Re: Group by range in hour of day |
Previous Message | Steve Boyle | 2015-03-17 19:49:54 | Re: bdr replication latency monitoring |