Stash intervals with consequent same value in PostgrerSQL

Question

I have a table:

Status	Date
ok	2023-05-01 00:00:00
ok	2023-05-01 00:01:00
ok	2023-05-01 00:02:00
no	2023-05-01 00:03:00
no	2023-05-01 00:04:00
ok	2023-05-01 00:04:00
ok	2023-05-01 00:05:00

I want to stash statuses coming having the same value and coming in a row and save status, start, end. So the above table would be transformed into:

Status	Start	End
ok	2023-05-01 00:00:00	2023-05-01 00:02:00
no	2023-05-01 00:03:00	2023-05-01 00:04:00
ok	2023-05-01 00:04:00	2023-05-01 00:05:00

How can I do this using PostgreSQL?

Answer 1

One way of dealing with this problem is:

• generating a flag that recognizes when the status has changed
• computing a running sum on the flag, to get the new partitions
• aggregating on minimum and maximum dates within the partitions

WITH cte AS (
    SELECT *, 
           CASE WHEN LAG("Date") OVER(PARTITION BY "Status" ORDER BY "Date") = "Date" - INTERVAL '1 minute' 
                THEN 0 ELSE 1 
           END AS "changed_part" 
    FROM tab
), cte2 AS (
    SELECT *, 
           SUM("changed_part") OVER(PARTITION BY "Status" ORDER BY "Date") AS "parts"
    FROM cte 
)
SELECT "Status",
       MIN("Date") AS "Start",
       MAX("Date")   AS "End"
FROM cte2 
GROUP BY "Status", "parts"
ORDER BY "Start"

Output:

Status	Start	End
ok	2023-05-01T00:00:00.000Z	2023-05-01T00:02:00.000Z
no	2023-05-01T00:03:00.000Z	2023-05-01T00:04:00.000Z
ok	2023-05-01T00:04:00.000Z	2023-05-01T00:05:00.000Z

Check the demo here.

Note: This solution works under the assumption that the time distance between consecutive status detection is fixed, as per sample data, to a specific time distance.