From: Shawn Tayler <stayler@washoecounty.us>
To: Steve Crawford <scrawford@pinpointresearch.com>
To: pqsql list <pgsql-sql@postgresql.org>
Subject: Re: Cumulative result with increment
Date: Tue, 08 Feb 2011 10:09:50 -0800
Message-ID: <1297188590.32163.1.camel@shop.telecom.co.washoe.nv.us> (raw)
In-Reply-To: <4D506E8A.30107@pinpointresearch.com>
References: <1297113095.21916.76.camel@shop.telecom.co.washoe.nv.us>
<4D506E8A.30107@pinpointresearch.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stayler@washoecounty.us, scrawford@pinpointresearch.com
Subject: Re: Cumulative result with increment
In-Reply-To: <1297188590.32163.1.camel@shop.telecom.co.washoe.nv.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox