Thread: calculate time diffs across rows with single timestamp

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

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 STATUSLOGWHERE TYPE = S.TYPE AND ID = S.ID AND DATETIME > S.DATETIME),
NOW()::TIMESTAMP)- DATETIME) AS DURATION
 
FROM STATUSLOG S
ORDER BY TYPE, ID, STATUS

Rodrigo De León wrote: <blockquote cite="mid1181753515.419178.63440@j4g2000prf.googlegroups.com" type="cite"><pre
wrap="">OnJun 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="">Anysuggestions 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 STATUSLOGWHERE 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 STATUSLOGWHERE 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>