From: Bob Singleton <bsingleton@ibss.net>
To: pgsql-sql@postgresql.org
Subject: Iterate and write a previous row to a temp table?
Date: Tue, 03 Jul 2007 14:43:31 -0400
Message-ID: <468A98D3.7030206@ibss.net> (raw)
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.
A colleague suggested a temp table, but I'm unsure how to iterate until
I pass the startTime and then write the _previous_ and all subsequent
rows to a temp table, stopping when I pass the endTime parameter.
Any hints?
Thanks!
Bob Singleton
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: bsingleton@ibss.net
Subject: Re: Iterate and write a previous row to a temp table?
In-Reply-To: <468A98D3.7030206@ibss.net>
* 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