Re: query to return hourly snapshot

Yes! Thanks you very much!

--- Michael Fuhr <mike(at)fuhr(dot)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
>