Thread: Cumulative result with increment

Hello,

I am struggling with what is probably a simple problem but I am coming
up blank.  In the interest of full disclosure I am not a very savy
programmer by any stretch.

I have a table of data from an external source which contains numbers of
events per operating group per hour per day, kind of like this:

Group |    events  | duration  | timestamp

The Group, events, and duration (milliseconds) are all integers in char
format.  Timestamp is as stated.  The records are at 1 hour increments.
I need to sum the events and duration fields in 8 hour (shifts, mid to
8AM, 8AM to 4PM, etc).

Id like the output to be in table format ala:

Group | events  |  duration  |   date    | shift
------+---------+------------+-----------+-------
100   |  26     |00:00:25.00 |2011-01-01 | Day

I have a function to convert the duration to an interval already and can
make one to do the shift labeling.  Its the rest that has me stumped.

Any suggestions or direction?

As always, your help is greatly appreciated.

--
Sincerely,

Shawn Tayler

On 02/07/2011 01:11 PM, Shawn Tayler wrote:
> Hello,
>
> I am struggling with what is probably a simple problem but I am coming
> up blank.  In the interest of full disclosure I am not a very savy
> programmer by any stretch.
>
> I have a table of data from an external source which contains numbers of
> events per operating group per hour per day, kind of like this:
>
> Group |    events  | duration  | timestamp
>
> The Group, events, and duration (milliseconds) are all integers in char
> format.  Timestamp is as stated.  The records are at 1 hour increments.
> I need to sum the events and duration fields in 8 hour (shifts, mid to
> 8AM, 8AM to 4PM, etc).
>
> Id like the output to be in table format ala:
>
> Group | events  |  duration  |   date    | shift
> ------+---------+------------+-----------+-------
> 100   |  26     |00:00:25.00 |2011-01-01 | Day    
>
> I have a function to convert the duration to an interval already and can
> make one to do the shift labeling.  Its the rest that has me stumped.
>
> Any suggestions or direction?
>
> As always, your help is greatly appreciated.
>

I'm not sure exactly what you want but it sounds like you just want an 
output column that has the shift instead of timestamp. You can then sum 
on that column. Don't know what your shifts are called, but this will 
give you dog-, day- and night-shifts based on your times:

case
when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog'
when extract(hour from now())< 16 then 'day'
else 'night'
end as shiftname

This can be used for grouping as well as display.

Cheers,
Steve

Thanks Steve,

That did the trick!

I appreciate the help....

Shawn


On Mon, 2011-02-07 at 14:13 -0800, Steve Crawford wrote:
> On 02/07/2011 01:11 PM, Shawn Tayler wrote:
> > Hello,
> >
> > I am struggling with what is probably a simple problem but I am coming
> > up blank.  In the interest of full disclosure I am not a very savy
> > programmer by any stretch.
> >
> > I have a table of data from an external source which contains numbers of
> > events per operating group per hour per day, kind of like this:
> >
> > Group |    events  | duration  | timestamp
> >
> > The Group, events, and duration (milliseconds) are all integers in char
> > format.  Timestamp is as stated.  The records are at 1 hour increments.
> > I need to sum the events and duration fields in 8 hour (shifts, mid to
> > 8AM, 8AM to 4PM, etc).
> >
> > Id like the output to be in table format ala:
> >
> > Group | events  |  duration  |   date    | shift
> > ------+---------+------------+-----------+-------
> > 100   |  26     |00:00:25.00 |2011-01-01 | Day
> >
> > I have a function to convert the duration to an interval already and can
> > make one to do the shift labeling.  Its the rest that has me stumped.
> >
> > Any suggestions or direction?
> >
> > As always, your help is greatly appreciated.
> >
>
> I'm not sure exactly what you want but it sounds like you just want an
> output column that has the shift instead of timestamp. You can then sum
> on that column. Don't know what your shifts are called, but this will
> give you dog-, day- and night-shifts based on your times:
>
> case
> when extract(hour from '2011-01-12 1600'::timestamptz)< 8 then 'dog'
> when extract(hour from now())< 16 then 'day'
> else 'night'
> end as shiftname
>
> This can be used for grouping as well as display.
>
> Cheers,
> Steve
>