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
>
