Thread: Iterate and write a previous row to a temp table?
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
--- Bob Singleton <bsingleton@ibss.net> 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. > > 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 > couldn't you use the simple query: select * from sometable where timestamp between (select max(timestamp) from sometable where timestamp <= minTime) and maxTime ____________________________________________________________________________________ Finding fabulous fares is fun. Let Yahoo! FareChase search your favorite travel sites to find flight and hotel bargains. http://farechase.yahoo.com/promo-generic-14795097
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
startTimeAND 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
chester c young wrote: <blockquote cite="mid25291.42349.qm@web54305.mail.re2.yahoo.com" type="cite"><pre wrap="">--- BobSingleton <a class="moz-txt-link-rfc2396E" href="mailto:bsingleton@ibss.net"><bsingleton@ibss.net></a> wrote: </pre><blockquote type="cite"><pre wrap="">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 </pre></blockquote><pre wrap=""> couldn't you use the simple query: select * from sometable where timestamp between (select max(timestamp) from sometable where timestamp <= minTime) and maxTime </pre></blockquote> This works very well unless I have no records where timestamp <= minTime. If I try <br /> selectmax(timestamp) from sometable where timestamp <= minTime;<br /> I get<br /> max<br /> -----<br /><br /> (1 row)<br/> And the blank row/line/value/? confuses the between call - I get 0 rows returned.<br /> <br /> (if minTime islater than at least one row's timestamp I will see something like)<br /> max<br /> ------------------------<br/> 2007-06-08 17:42:00.18<br /> (1 row)<br /><br /><br /> Any help greatly appreciated!<br />bs<br />