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.
