[SQL] 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] calculate time diffs across rows with single timestamp
Rodrigo De León wrote:
On Jun 13, 11:17 am, [EMAIL PROTECTED] (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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Awesome - thank you very much! Slightly modified to collapse by TYPE /
ID / STATUS
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
[SQL] Iterate and write a previous row to a temp table?
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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Iterate and write a previous row to a temp table?
chester c young wrote: --- Bob Singleton <[EMAIL PROTECTED]> 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 This works very well unless I have no records where timestamp <= minTime. If I try select max(timestamp) from sometable where timestamp <= minTime; I get max - (1 row) And the blank row/line/value/? confuses the between call - I get 0 rows returned. (if minTime is later than at least one row's timestamp I will see something like) max 2007-06-08 17:42:00.18 (1 row) Any help greatly appreciated! bs
