Message-ID: <467018B0.30108@ibss.net>
Date: Wed, 13 Jun 2007 12:17:52 -0400
From: Bob Singleton <bsingleton@ibss.net>
User-Agent: Thunderbird 1.5.0.10 (X11/20070302)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: calculate time diffs across rows with single timestamp
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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