CODEWITHSUNDEEP
sqlpostgresqlgaps-and-islands
J. Bijvoets
J. Bijvoets

Reputation: 13

Postgres finding transitions

I'm trying to find all periods of time a system is in a specific state. The state is logged into a table. An example of the data is as follows: 

Time - State  
12:00 - 1  
12:01 - 1  
12:02 - 1  
12:03 - 1  
12:04 - 0  
12:05 - 0  
12:06 - 0  
12:07 - 1  
12:08 - 1

The result I would like to see is:

Start - End - State  
12:00 - 12:03 - 1  
12:04 - 12:06 - 0  
12:07 - 12:08 - 1

Is there any predefined function to find the transition records?

Upvotes: 1

Views: 371

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

Something like this should work:

SELECT "State",
       MIN("Time") AS Start,
       MAX("Time") AS End     
FROM (
  SELECT "Time", "State",
         ROW_NUMBER() OVER (ORDER BY "Time") - 
         ROW_NUMBER() OVER (PARTITION BY "State" ORDER BY "Time") AS grp
  FROM mytable) AS t
GROUP BY "State", grp

The inner query uses a standard technique in order to identify islands of consecutive records having the same State value. The outer query uses computed column grp to GROUP BY and get Start, End values of each island.

Upvotes: 3

Related Questions