From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: query to return hourly snapshot
Date: Tue, 4 Apr 2006 21:37:32 -0700 (PDT)
Message-ID: <20060405043732.10040.qmail@web31803.mail.mud.yahoo.com> (raw)
In-Reply-To: <20060405024916.GA75532@winnie.fuhr.org>
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
>
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: rabroersma@yahoo.com, mike@fuhr.org
Subject: Re: query to return hourly snapshot
In-Reply-To: <20060405043732.10040.qmail@web31803.mail.mud.yahoo.com>
* 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