Date: Tue, 4 Apr 2006 20:49:16 -0600
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
Message-ID: <20060405024916.GA75532@winnie.fuhr.org>
References: <20060405005826.45346.qmail@web31814.mail.mud.yahoo.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <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