Question 169660 in PostgreSQL Question posted in
The official documentation can be found here.

How can I store intervals with the same value in PostgreSQL?

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You can use something like:

    
--afterwards select the earliest start time for that end time
select min( start_time), mstatus, end_time
from (
   --inside get the latest end times for each start time; this will have duplicates that only vary depending on start time
   select s.mdate as start_time, s.mstatus 
   , max(f.mdate) as end_time

   from statuses s
   join statuses f on f.mstatus = s.mstatus
    and s.mdate < f.mdate
    and not exists (select 1 from statuses m where m.mstatus <> s.mstatus and m.mdate > s.mdate and m.mdate < f.mdate)
   group by s.mdate, s.mstatus ) t
group by t.mstatus, t.end_time

    Fiddle: https://www.db-fiddle.com/f/o5v9CtBaQZuXsC4XaSB1Cv/0

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search