SELECT A.Device, TIMESTAMPDIFF(SECOND,B.t2,A.t1) as differenceintime
FROM
( SELECT device, MIN(timestamp) as t1
  FROM tableName
  WHERE state = 5
  GROUP BY device
) as A
INNER JOIN
( SELECT device, MIN(timestamp) as t2
  FROM tableName
  WHERE state = 4
  GROUP BY device
) as B

ON A.Device = B.Device

如果您使用的是更高版本的mysql，那么可以使用 LEAD 函数获取每行的下一个状态

SELECT  *,
        LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
        LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
FROM    t;

对于一个设备，它将提供：

id      device          state   timestamp               NextState       NextTimeStamp
---------------------------------------------------------------------------------------------
250795  3091-5-007208   7       2019-02-01 10:39:00         5           2019-02-01 10:42:00
250819  3091-5-007208   5       2019-02-01 10:42:00         4           2019-02-01 10:58:00   <---
250832  3091-5-007208   4       2019-02-01 10:58:00         11          2019-02-01 11:02:00
250837  3091-5-007208   11      2019-02-01 11:02:00         4           2019-02-01 11:13:00
250846  3091-5-007208   4       2019-02-01 11:13:00        NULL                 NULL

这将允许您识别状态何时从5更改为4。只需将上述查询放入子查询中，并应用where子句：

SELECT  t.device,
        t.TimeStamp AS ChangedToFive,
        t.NextTimeStamp AS ChangedFromFiveToFour
FROM    (   SELECT  *,
                    LEAD(State) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextState,
                    LEAD(TimeStamp) OVER(PARTITION BY device ORDER BY TimeStamp) AS NextTimeStamp
            FROM    t
        ) AS t
WHERE   t.State = 5
AND     t.NextState = 4;

如果您使用的旧版本不支持 LEAD ，则需要使用共同相关的子查询来复制功能：

SELECT  t.device,
        t.TimeStamp AS ChangedToFive,
        t.NextTimeStamp AS ChangedFromFiveToFour
FROM    (   SELECT  *,
                    (   SELECT  TimeStamp
                        FROM    t AS t2
                        WHERE   t2.Device = t.Device
                        AND     t2.TimeStamp > t.TimeStamp
                        ORDER BY TimeStamp
                        LIMIT 1
                    ) AS NextTimeStamp
            FROM    t
            WHERE   t.State = 5
            AND     (   SELECT  State
                        FROM    t AS t2
                        WHERE   t2.Device = t.Device
                        AND     t2.TimeStamp > t.TimeStamp
                        ORDER BY TimeStamp
                        LIMIT 1
                    ) = 4 -- next state = 4 to satisfy changing from 5 to 4
        ) AS t;

db<>fiddle示例

我认为可以使用条件聚合：

select device,
       timestampdiff(second,
                     min(case when status = 4 then timestamp end),
                     max(case when status = 5 then timestamp end)
                    ) as diff_in_seconds
from t
group by device;

你可以使用join。以下假设正好有一个5->4转换，因此不必检查它是否是第一个转换：

SELECT t1.id, t2.id, TIMESTAMPDIFF(SECOND, t1.timestamp,t2.timestamp) AS diff
FROM       t AS t1
INNER JOIN t AS t2 ON t2.device = t1.device AND t2.state = 4 AND t2.timestamp > t1.timestamp
LEFT  JOIN t AS tx ON t1.device = tx.device AND tx.timestamp > t1.timestamp AND tx.timestamp < t2.timestamp
WHERE t1.state = 5 AND tx.id IS NULL

上例中的左连接确保只匹配连续的转换（例如250774连接到250778和250808，但后者被丢弃）。
在db上演示