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?
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.
