pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIterate and write a previous row to a temp table?
4+ messages / 3 participants
[nested] [flat]
* Iterate and write a previous row to a temp table?
@ 2007-07-03 18:43 Bob Singleton <bsingleton@ibss.net>
2007-07-03 18:49 ` Re: Iterate and write a previous row to a temp table? chester c young <chestercyoung@yahoo.com>
2007-07-03 18:57 ` Re: Iterate and write a previous row to a temp table? Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: Bob Singleton @ 2007-07-03 18:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Iterate and write a previous row to a temp table?
2007-07-03 18:43 Iterate and write a previous row to a temp table? Bob Singleton <bsingleton@ibss.net>
@ 2007-07-03 18:49 ` chester c young <chestercyoung@yahoo.com>
2007-07-06 16:25 ` Re: Iterate and write a previous row to a temp table? Bob Singleton <bsingleton@ibss.net>
1 sibling, 1 reply; 4+ messages in thread
From: chester c young @ 2007-07-03 18:49 UTC (permalink / raw)
To: Bob Singleton <bsingleton@ibss.net>; +Cc: pgsql-sql
--- 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Iterate and write a previous row to a temp table?
2007-07-03 18:43 Iterate and write a previous row to a temp table? Bob Singleton <bsingleton@ibss.net>
2007-07-03 18:49 ` Re: Iterate and write a previous row to a temp table? chester c young <chestercyoung@yahoo.com>
@ 2007-07-06 16:25 ` Bob Singleton <bsingleton@ibss.net>
0 siblings, 0 replies; 4+ messages in thread
From: Bob Singleton @ 2007-07-06 16:25 UTC (permalink / raw)
To: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
chester c young wrote:
<blockquote cite="mid25291.42349.qm@web54305.mail.re2.yahoo.com"
type="cite">
<pre wrap="">--- Bob Singleton <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>
select max(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 is later 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>
</body>
</html>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Iterate and write a previous row to a temp table?
2007-07-03 18:43 Iterate and write a previous row to a temp table? Bob Singleton <bsingleton@ibss.net>
@ 2007-07-03 18:57 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2007-07-03 18:57 UTC (permalink / raw)
To: Bob Singleton <bsingleton@ibss.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-07-06 16:25 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-03 18:43 Iterate and write a previous row to a temp table? Bob Singleton <bsingleton@ibss.net>
2007-07-03 18:49 ` chester c young <chestercyoung@yahoo.com>
2007-07-06 16:25 ` Bob Singleton <bsingleton@ibss.net>
2007-07-03 18:57 ` Richard Huxton <dev@archonet.com>
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