Re: query to return hourly snapshot

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