PostgreSQL finding durations

Question

I'm trying to find the duration of an event in high (1) state. The states are logged into a table as the following

time    |state|
------- +-----+
01:00:00|    0|
01:01:00|    1|
01:02:35|    1|
01:03:20|    0|
01:04:00|    0|
01:05:00|    0|
01:06:00|    1|
01:07:00|    1|
01:08:00|    1|
01:09:10|    0|
01:10:00|    0|
01:11:00|    1|
01:12:45|    0|

The result I'm looking for is this:

starttime    | endtime    | duration    | 
-------------+------------+-------------+
01:01:00     | 01:03:20   |   00:02:20  |
01:06:00     | 01:09:10   |   00:03:10  |
01:11:00     | 01:12:45   |   00:01:45  |

Almost the same as this post, but not quite the same

not quite sure how to acomplish this.. any help would be appreciated

Edouard · Accepted Answer · 2022-03-11 14:46:32Z

1

try this :

WITH list AS
( SELECT time AS starttime
       , state
       , CASE
           WHEN state = 0 
           THEN min(time) FILTER (WHERE state = 1) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
           ELSE min(time) FILTER (WHERE state = 0) OVER (ORDER BY time ASC ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
         END AS endtime
    FROM test
)
SELECT min(starttime)
     , endtime
     , endtime - min(starttime) AS duration
  FROM list
 WHERE state = 1
 GROUP BY endtime

test result in dbfiddle

answered Mar 11, 2022 at 14:46

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

smolesen Over a year ago

Wow, thanks for helping out on this.... not sure which one of the 3 versions is best one, so I'm just going to ✓ the first one... but thanks for all solutions

Philippe · Accepted Answer · 2022-03-11 14:11:17Z

1

You have to use window functions (result here).

with y as (
select x.time, lead(time,1) over (), lead(time,1) over () - x.time,state,previous_state
from (select *,lag(state,1) over (order by time) previous_state from t1) x
where (x.state = 1 and x.previous_state = 0) or (x.state = 0 and x.previous_state = 1) 
order by time
)
select * from y where state = 1 and previous_state = 0

answered Mar 11, 2022 at 14:11

Philippe

1,8376 silver badges19 bronze badges

1 Comment

smolesen Over a year ago

Wow, thanks for helping out on this.... not sure which one of the 3 versions is best one, so I'm just going to ✓ the first one... but thanks for all solutions

D-Shih · Accepted Answer · 2022-03-11 14:20:39Z

1

It might be a gap and islands problem, we need to use LAG window function & CASE WHEN to determine a group on your logic condition column (event in high (1) state.)

Then use ROW_NUMBER to make a group continuous number.

WITH CTE AS (
    SELECT *,
          ROW_NUMBER() OVER (PARTITION BY condition ORDER BY time) - 
            ROW_NUMBER() OVER ( ORDER BY time) grp
    FROM (
     SELECT *,
           (CASE WHEN state = 1 OR  LAG(state,1,state) OVER(ORDER BY TIME)  = 1 THEN 1 END) condition
     FROM T
    ) t1
)
SELECT MIN(time) starttime ,
       MAX(time) endtime ,
       MAX(time) - MIN(time) duration    
FROM CTE 
WHERE condition IS NOT NULL
GROUP BY grp
ORDER BY 1

sqlfiddle

answered Mar 11, 2022 at 14:20

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

1 Comment

smolesen Over a year ago

Wow, thanks for helping out on this.... not sure which one of the 3 versions is best one, so I'm just going to ✓ the first one... but thanks for all solutions

Collectives™ on Stack Overflow

PostgreSQL finding durations

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related