pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcalculate time diffs across rows with single timestamp
3+ messages / 2 participants
[nested] [flat]
* calculate time diffs across rows with single timestamp
@ 2007-06-13 16:17 Bob Singleton <bsingleton@ibss.net>
0 siblings, 1 reply; 3+ messages in thread
From: Bob Singleton @ 2007-06-13 16:17 UTC (permalink / raw)
To: pgsql-sql
First post - please pardon if I'm posted to the wrong group.
I have a table 'statuslog'
type varchar NOT NULL
id varchar NOT NULL
status varchar
datetime timestamp NOT NULL
Example data
type id status datetime
ASSET 001 AAA 2007-06-08 13:42:00.00
ASSET 002 AAA 2007-06-08 13:42:00.00
ASSET 003 AAA 2007-06-08 13:42:00.00
ASSET 001 BBB 2007-06-08 14:42:00.00
ASSET 001 CCC 2007-06-08 14:52:00.00
ASSET 002 BBB 2007-06-08 13:45:00.00
ASSET 001 DDD 2007-06-08 15:00:00.00
Consider this a log of transitional status changes. I now need to
sumarize time-in-status with grouping on type, id, status.
I can't currently modify the schema to include a second timestamp...
I'm not (yet) well versed in temp tables and cursors, but from what I
have researched and the suggestions from helpful coworkers, this seems
the way to go...?
Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
(The time diff can be seconds since epoch, some int, or whatever... in
testing I set up the schema using a second timestamp (the 'in' stamp of
the latter record by type/id became the 'out' stamp of the previous
record) and I simply subtracted the in from the out time in a sum() with
grouping.)
Thanks,
Bob
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: calculate time diffs across rows with single timestamp
@ 2007-06-13 16:51 Rodrigo De León <rdeleonp@gmail.com>
parent: Bob Singleton <bsingleton@ibss.net>
0 siblings, 1 reply; 3+ messages in thread
From: Rodrigo De León @ 2007-06-13 16:51 UTC (permalink / raw)
To: pgsql-sql
On Jun 13, 11:17 am, bsingle...@ibss.net (Bob Singleton) wrote:
> Any suggestions on how I can build a result set that would return
>
> ASSET 001 AAA 1:00 (1 hour)
> ASSET 001 BBB 0:10 (10 minutes)
> ASSET 001 CCC 0:08 (8 minutes)
> ASSET 001 DDD {difference between timestamp and now()}
> ASSET 002 AAA 0:03 (3 minutes)
> ASSET 002 BBB {difference detween timestamp and now()}
> ASSET 003 AAA{diff between timestamp and now()}
SELECT
TYPE, ID, STATUS
, (COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: calculate time diffs across rows with single timestamp
@ 2007-06-13 18:48 Bob Singleton <bsingleton@ibss.net>
parent: Rodrigo De León <rdeleonp@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Bob Singleton @ 2007-06-13 18:48 UTC (permalink / raw)
To: Rodrigo De León <rdeleonp@gmail.com>; +Cc: 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">
Rodrigo De León wrote:
<blockquote
cite="mid1181753515.419178.63440@j4g2000prf.googlegroups.com"
type="cite">
<pre wrap="">On Jun 13, 11:17 am, <a class="moz-txt-link-abbreviated" href="mailto:bsingle...@ibss.net">bsingle...@ibss.net</a> (Bob Singleton) wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Any suggestions on how I can build a result set that would return
ASSET 001 AAA 1:00 (1 hour)
ASSET 001 BBB 0:10 (10 minutes)
ASSET 001 CCC 0:08 (8 minutes)
ASSET 001 DDD {difference between timestamp and now()}
ASSET 002 AAA 0:03 (3 minutes)
ASSET 002 BBB {difference detween timestamp and now()}
ASSET 003 AAA{diff between timestamp and now()}
</pre>
</blockquote>
<pre wrap=""><!---->
SELECT
TYPE, ID, STATUS
, (COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME) AS DURATION
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
</pre>
</blockquote>
Awesome - thank you very much! Slightly modified to collapse by TYPE /
ID / STATUS<br>
<br>
<pre wrap="">SELECT
TYPE, ID, STATUS, SUM(
(COALESCE(
(SELECT MIN(DATETIME) FROM STATUSLOG
WHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME)
, NOW()::TIMESTAMP
) - DATETIME))
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS
Thanks for the lesson!
Bob Singleton
</pre>
</body>
</html>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-06-13 18:48 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-13 16:17 calculate time diffs across rows with single timestamp Bob Singleton <bsingleton@ibss.net>
2007-06-13 16:51 ` Rodrigo De León <rdeleonp@gmail.com>
2007-06-13 18:48 ` Bob Singleton <bsingleton@ibss.net>
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