From: Michael Fuhr <mike@fuhr.org>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: query to return hourly snapshot
Date: Tue, 4 Apr 2006 20:49:16 -0600
Message-ID: <20060405024916.GA75532@winnie.fuhr.org> (raw)
In-Reply-To: <20060405005826.45346.qmail@web31814.mail.mud.yahoo.com>
References: <20060405005826.45346.qmail@web31814.mail.mud.yahoo.com>
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
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: mike@fuhr.org, rabroersma@yahoo.com
Subject: Re: query to return hourly snapshot
In-Reply-To: <20060405024916.GA75532@winnie.fuhr.org>
* 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