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
BYdate_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
