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
