From: Richard Huxton <dev@archonet.com>
To: Bob Singleton <bsingleton@ibss.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Iterate and write a previous row to a temp table?
Date: Tue, 03 Jul 2007 19:57:20 +0100
Message-ID: <468A9C10.8070005@archonet.com> (raw)
In-Reply-To: <468A98D3.7030206@ibss.net>
References: <468A98D3.7030206@ibss.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, bsingleton@ibss.net
Subject: Re: Iterate and write a previous row to a temp table?
In-Reply-To: <468A9C10.8070005@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox