Postgres Pro
Downloads
Home   >   mailing lists

Thread: query to return hourly snapshot

query to return hourly snapshot

From
Richard Broersma Jr
Date:
I orginally sent this email to the [novice] list but did not get any response.  
I am look for help in developing a query that will return the nearest process record that waslogged at or after each
hourin a day (i.e. hourly snapshot).Below are typical sample data.  Also, I've included a query used to return the
averageof theprocess value for each hour to give an example for the format that I am trying to achieve.Thanks for the
help.Regards,Richardselect* from process limit 10;       tstamp        | process---------------------+---------
2005-10-2521:10:41 | 3.56513 2005-10-25 21:10:42 | 3.56503 2005-10-25 21:10:43 | 3.56494 2005-10-25 21:10:44 | 3.56484
2005-10-2521:10:45 | 3.56475 2005-10-25 21:10:46 | 3.56465 2005-10-25 21:10:47 | 3.56455 2005-10-25 21:10:48 | 3.56446
2005-10-2521:10:49 | 3.56436 2005-10-25 21:10:50 | 3.56427(10 rows)select   avg(process),
date_trunc('hour',tstamp)as date from     process where    date_trunc('day', tstamp) = '2005-10-26' group by
date_trunc('hour',tstamp) order by date_trunc('hour', tstamp);       avg        |
date------------------+---------------------9.79195118032606 | 2005-10-26 05:00:00 10.0249767947376 | 2005-10-26
06:00:008.88596018049452 | 2005-10-26 07:00:00 7.95090951088542 | 2005-10-26 08:00:00 8.10741349776586 | 2005-10-26
09:00:007.30079822791947 | 2005-10-26 10:00:00 7.10586501293712 | 2005-10-26 11:00:00 8.15196838166979 | 2005-10-26
12:00:008.26183129151662 | 2005-10-26 13:00:00 8.95141531440947 | 2005-10-26 14:00:00  10.562882253329 | 2005-10-26
15:00:0010.8634908244445 | 2005-10-26 16:00:00 11.4077104069976 | 2005-10-26 17:00:00 12.4702264580744 | 2005-10-26
18:00:0011.9155618293134 | 2005-10-26 19:00:00 11.5622152555012 | 2005-10-26 20:00:00 11.6527367563489 | 2005-10-26
21:00:0010.3170960432442 | 2005-10-26 22:00:00 9.56747980806563 | 2005-10-26 23:00:00(19 rows)

Re: query to return hourly snapshot

From
Michael Fuhr
Date:
On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> I am look for help in developing a query that will return the nearest
> process record that was logged at or after each hour in a day (i.e.
> hourly snapshot).

Are you looking for something like this?

SELECT p.process, date_trunc('hour', p.tstamp) AS hour
FROM process AS p
JOIN ( SELECT date_trunc('hour', tstamp), min(tstamp) FROM process WHERE date_trunc('day', tstamp) = '2005-10-26' GROUP
BYdate_trunc('hour', tstamp)
 
) AS s ON s.min = p.tstamp
ORDER BY hour;    

Or, using PostgreSQL's non-standard DISTINCT ON clause:

SELECT DISTINCT ON (date_trunc('hour', tstamp))      process, date_trunc('hour', tstamp) AS hour
FROM process
WHERE date_trunc('day', tstamp) = '2005-10-26'
ORDER BY date_trunc('hour', tstamp), tstamp;

-- 
Michael Fuhr

Re: query to return hourly snapshot

From
Richard Broersma Jr
Date:
Yes!  Thanks you very much!

--- Michael Fuhr <mike@fuhr.org> wrote:

> On Tue, Apr 04, 2006 at 05:58:26PM -0700, Richard Broersma Jr wrote:
> > I am look for help in developing a query that will return the nearest
> > process record that was logged at or after each hour in a day (i.e.
> > hourly snapshot).
> 
> Are you looking for something like this?
> 
> SELECT p.process, date_trunc('hour', p.tstamp) AS hour
> FROM process AS p
> JOIN (
>   SELECT date_trunc('hour', tstamp), min(tstamp)
>   FROM process
>   WHERE date_trunc('day', tstamp) = '2005-10-26'
>   GROUP BY date_trunc('hour', tstamp)
> ) AS s ON s.min = p.tstamp
> ORDER BY hour;    
> 
> Or, using PostgreSQL's non-standard DISTINCT ON clause:
> 
> SELECT DISTINCT ON (date_trunc('hour', tstamp))
>        process, date_trunc('hour', tstamp) AS hour
> FROM process
> WHERE date_trunc('day', tstamp) = '2005-10-26'
> ORDER BY date_trunc('hour', tstamp), tstamp;
> 
> -- 
> Michael Fuhr
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>