Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 1:41 PM, Marc Mamin 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 >> wrote: >> >> So next question: how do I get the

Re: [GENERAL] Group by range in hour of day

>>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 >>> >> wrote: >>> >> >>> >> So next question: how do I get the "active" time per hour from this? >>> >> >>> >> I think you j

Re: [GENERAL] Group by range in hour of day

> > >-- > Adrian Klaver > adrian.kla...@aklaver.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 >> >> wrote: >> >> >> >> So next question: how do I get the "active" tim

Re: [GENERAL] Group by range in hour of day

-- Adrian Klaver adrian.kla...@aklaver.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 > >> wrote: > >> > >> So next question: how do I get the "active" time per hour from

Re: [GENERAL] Group by range in hour of 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. If you are running the report on mor

Re: [GENERAL] 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 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

Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 9:30 AM, Paul Jungwirth > 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: Soun

Re: [GENERAL] Group by range in hour of day

On Tuesday, March 17, 2015, Israel Brewster wrote: > > > > On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > wrote: > > > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as > s(h) where h between extract(hour from start_time) and extract(hour from > end_time) group by h order by

Re: [GENERAL] Group by range in hour of day

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: SUM(extract(minutes from (tsrange(start_time, end_time) && tsrange(

Re: [GENERAL] Group by range in hour of day

On Mar 17, 2015, at 9:05 AM, David G. Johnston wrote: > > On Tuesday, March 17, 2015, Israel Brewster > wrote: > > > > On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > > wrote: > > > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) > >>

Re: [GENERAL] Group by range in hour of day

> On Mar 17, 2015, at 8:09 AM, Paul Jungwirth > wrote: > >>> test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) >>> where h between extract(hour from start_time) and extract(hour from >>> end_time) group by h order by h; >>> >>> h | count >>> +--- >>> 8 |

Re: [GENERAL] Group by range in hour of day

test=> select h, count(*) from start_end, generate_series(0, 23) as s(h) where h between extract(hour from start_time) and extract(hour from end_time) group by h order by h; h | count +--- 8 | 2 9 | 3 10 | 2 11 | 2 Note if you always want all 24 rows with a count

Re: [GENERAL] Group by range in hour of day

BEGIN:VCARD VERSION:3.0 N:Brewster;Israel;;; FN:Israel Brewster ORG:Frontier Flying Service;MIS TITLE:PC Support Tech II EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com TEL;type=WORK;type=pref:907-450-7293 item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;9

Re: [GENERAL] Group by range in hour of day

On Mon, Mar 16, 2015 at 4:16 PM, Israel Brewster wrote: > On Mar 16, 2015, at 2:22 PM, David G. Johnston > wrote: > > > On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver > wrote: > >> On 03/16/2015 02:57 PM, Israel Brewster wrote: >> >>> I have a table with two timestamp columns for the start time

Re: [GENERAL] Group by range in hour of day

On 03/16/2015 04:16 PM, Israel Brewster wrote: On Mar 16, 2015, at 2:22 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>>wrote: On 03/16/2015 02:57 PM, Israel Brewster wrote: I have a

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:22 PM, David G. Johnston wrote: > > On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver > wrote: > On 03/16/2015 02:57 PM, Israel Brewster wrote: > I have a table with two timestamp columns for the start time and end > time of each record (call

Re: [GENERAL] Group by range in hour of day

Assuming 3 things Table name - test Column names - start_time, end_time Added an id column (int) to distinguish each record in the table You can go with this. (my apologies for formatting issues) with slots as ( select * fromgenerate_series(0,1439) as s(slot) ), slots_hours as (

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:16 PM, Paul Jungwirth wrote: > >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by "hour of day", > > I think you can do this by sel

Re: [GENERAL] Group by range in hour of day

On Mar 16, 2015, at 2:13 PM, Adrian Klaver wrote: > > On 03/16/2015 02:57 PM, Israel Brewster wrote: >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by "ho

Re: [GENERAL] Group by range in hour of day

On Mon, Mar 16, 2015 at 3:13 PM, Adrian Klaver wrote: > On 03/16/2015 02:57 PM, Israel Brewster wrote: > >> I have a table with two timestamp columns for the start time and end >> time of each record (call them start and end).I'm trying to figure out >> if there is a way to group these records by

Re: [GENERAL] Group by range in hour of day

I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", I think you can do this by selecting `FROM generate_series(0, 23) s(h)` and then joining to your

Re: [GENERAL] Group by range in hour of day

On 03/16/2015 02:57 PM, Israel Brewster wrote: I have a table with two timestamp columns for the start time and end time of each record (call them start and end).I'm trying to figure out if there is a way to group these records by "hour of day", that is the record should be included in the group