From: Bob Singleton <bsingleton@ibss.net>
To: pgsql-sql@postgresql.org
Subject: calculate time diffs across rows with single timestamp
Date: Wed, 13 Jun 2007 12:17:52 -0400
Message-ID: <467018B0.30108@ibss.net> (raw)
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
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: calculate time diffs across rows with single timestamp
In-Reply-To: <467018B0.30108@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