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
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
