Seems like a classic Islands and Gaps problem with edges.
Try this:
with changes as (
select status - lag(status,1,0) over (order by timestamp) change
,timestamp
from Table1
)
,up as (
select timestamp
,row_number() over (order by timestamp) rn
from changes
where change = 1
)
,down as (
select timestamp
,row_number() over (order by timestamp) rn
from changes
where change = -1
)
select u.timestamp up,d.timestamp down,u.rn rn
from up u
join down d on u.rn = d.rn
order by rn
Here's[
^] the fiddle.
This works on SQLServer 2012 and Oracle 10G, but earlier versions lack the
Lag
function. It can be implemented on those easy enough using rownumber and a self join.
So if you have an earlier version or another database you'll need to update your question with database and version.
Here's an updated solution for MySQL:
SELECT up.TIMESTAMP up,down.TIMESTAMP down,up.rn
FROM (
SELECT TIMESTAMP
,@r1 := @r1 + 1 AS rn
FROM (
SELECT t1.timestamp
FROM Table1 t1
left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
WHERE t1.status - ifnull(t2.status,0) = 1
ORDER BY t1.timestamp
) t1
,(SELECT @r1 := 0) r
) up
,(
SELECT TIMESTAMP
,@r2 := @r2 + 1 AS rn
FROM (
SELECT t1.timestamp
FROM Table1 t1
left OUTER JOIN Table1 t2 ON t1.timestamp = t2.timestamp + 1
WHERE t1.status - ifnull(t2.status,0) = -1
ORDER BY t1.timestamp
) t1
,(SELECT @r2 := 0) r
) down
WHERE up.rn = down.rn
ORDER BY up.rn
Here's[
^] the fiddle.
When you need to create a contraption like this it's a fitting time to say how much I despise MySQL.
Note: This code assumes that there are no gaps in the timestamp sequence.
If there are, you need to add a couple more IfNull