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 > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
