Message-ID: <468A9C10.8070005@archonet.com>
Date: Tue, 03 Jul 2007 19:57:20 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Bob Singleton <bsingleton@ibss.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Iterate and write a previous row to a temp table?
References: <468A98D3.7030206@ibss.net>
In-Reply-To: <468A98D3.7030206@ibss.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Bob Singleton wrote:
> Revisiting a Time In Status query I received help on - I'm trying to 
> narrow down a subset of data I return for analysis.
> 
> Given a statusLog as entityId, statusId, timestamp that might look 
> something like
> 
> entityId | statusId | timestamp
> --------------------------------------------
> 001      | HLD      | 2007-06-14 11:07:35.93
> 001      | RDY      | 2007-06-15 11:07:35.93
> 001      | USE      | 2007-06-16 11:07:35.93
> 001      | RDY      | 2007-06-17 11:07:35.93
> 001      | MNT      | 2007-06-18 11:07:35.93
> 
> I need to pull for a given span of time - say 2007-06-16 00:00:00.01 
> (let me call it startTime)  to 2007-06-17 23:59:59.99 (call it endTime) 
> in such a way that rows with a timestamp between startTime and endTime 
> AND the latest record prior to or equal to startTime are returned. In 
> the above simplified example, only the second and third rows would be 
> returned.

Can't be done, because you don't have a primary key, so no way to 
distinguish between duplicate rows. However, if you just eliminate 
duplicates you could just use a function like (not tested):

CREATE FUNCTION ranged(
	startTime timestamp with time zone,
	endTime timestamp with time zone,
) RETURNS SETOF statusLog
  AS $$
     SELECT entityid,statusid,timestamp
     FROM statusLog
     WHERE timestamp BETWEEN startTime AND endTime

     UNION

     SELECT entityid,statusid,timestamp
     FROM statusLog
     WHERE timestamp <= startTime ORDER BY timestamp DESC LIMIT 1

     ORDER BY <final result ordering>
$$ LANGUAGE SQL;

Note that UNION eliminates duplicates, if you want to keep them use 
"UNION ALL"

HTH

-- 
   Richard Huxton
   Archonet Ltd