Re: query to return hourly snapshot

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
>