Sum of time difference between rows

Question

I have a table that records every change in state of an object

id recordTime Status ID1 2014-03-01 11:33:00 Disconnected ID1 2014-03-01 12:13:00 Connected ID2 2014-03-01 12:21:00 Connected ID1 2014-03-01 12:24:00 Disconnected ID1 2014-03-01 12:29:00 Connected ID2 2014-03-01 12:40:00 Disconnected ID2 2014-03-01 13:03:00 Connected ID2 2014-03-01 13:13:00 Disconnected ID2 2014-03-01 13:29:00 Connected ID1 2014-03-01 13:30:00 Disconnected 

I need to calculate the total inactive time, the time between the "Connected" and the last "Disconnected" status on the identifier for the given time window.

For the table above and the time from 2014-03-01 11:00:00 to 2014-03-01 14:00:00 the output should be:

 ID InactiveTime ID1 01:15:00 ID2 02:00:00 

Answer 1

The particular difficulty is not to skip time intervals to an external time frame.
Assuming the next line for any given id always has the opposite status.
Using ts column name instead of recordTime :

 WITH span AS ( SELECT '2014-03-01 13:00'::timestamp AS s_from -- start of time range , '2014-03-01 14:00'::timestamp AS s_to -- end of time range ) , cte AS ( SELECT id, ts, status, s_to , lead(ts, 1, s_from) OVER w AS span_start , first_value(ts) OVER w AS last_ts FROM span s JOIN tbl t ON t.ts BETWEEN s.s_from AND s.s_to WINDOW w AS (PARTITION BY id ORDER BY ts DESC) ) SELECT id, sum(time_disconnected)::text AS total_disconnected FROM ( SELECT id, ts - span_start AS time_disconnected FROM cte WHERE status = 'Connected' UNION ALL SELECT id, s_to - ts FROM cte WHERE status = 'Disconnected' AND ts = last_ts ) sub GROUP BY 1 ORDER BY 1; 

Returns the intervals as requested.
Identifiers without entries in the selected time range are not displayed. You will have to request them additionally.

SQL Fiddle
Note. I entered total_disconnected into text in the script because the interval type is displayed in terrible format.

Add identifiers without recording to the selected time interval

^{On request in the comments.}
Add to the query above (before the final ORDER BY 1 ):

 ... UNION ALL SELECT id, total_disconnected FROM ( SELECT DISTINCT ON (id) t.id, t.status, (s.s_to - s.s_from)::text AS total_disconnected FROM span s JOIN tbl t ON t.ts < s.s_from -- only from before time range LEFT JOIN cte c USING (id) WHERE c.id IS NULL -- not represented in selected time frame ORDER BY t.id, t.ts DESC -- only the latest entry ) sub WHERE status = 'Disconnected' -- only if disconnected ORDER BY 1; 

SQL Fiddle

Now only identifiers without entries in or before the selected time range are not displayed.

Answer 2

This is how I understand your SQL Fiddle question

 select id, sum(diff) as inactive from ( select recordtime, recordTime - lag(recordTime, 1, recordTime) over( partition by id order by recordTime ) as diff, status, id from t ) s where status = 'Connected' group by id order by id ; id | inactive ----+---------- 1 | 00:45:00 2 | 00:39:00 

Could you explain your desired result?

Answer 3

 select id , sum(diff) inactif_time from ( SELECT id, "recordTime", "Status" ,LEAD("recordTime") OVER(PARTITION BY id order by "recordTime" ),LEAD("recordTime") OVER(PARTITION BY id order by "recordTime" ) - "recordTime" diff FROM my_table ) B where "Status" = 'Disconnected' group by id 

But he deduces:

 "ID1";"00:45:00" "ID2";"00:39:00"