如何在mysql中找到数据行的时间增量？

Question

我在一个数据库中有一个列，它提供了一系列传感器读数的日期时间戳。我想把这些读数分割成不间断的、连续的传感器读数。如果传感器读数损坏，则date time列中将出现中断。因此，我想对日期时间列执行查询，然后计算连续读数之间的差值。

假设我的查询是：

select sensor_time from sensor_table limit 10;

我会得到：

+---------------------+
| sensor_time         |
+---------------------+
| 2009-09-28 07:08:12 |
| 2009-09-28 07:08:40 |
| 2009-09-28 07:09:10 |
| 2009-09-28 07:09:40 |
| 2009-09-28 07:10:10 |
| 2009-09-28 07:10:40 |
| 2009-09-28 07:41:10 |
| 2009-09-28 07:41:40 |
| 2009-09-28 07:42:10 |
| 2009-09-28 07:42:40 |
+---------------------+

这个例子中的时间突然从07:10跳到07:41，这是我想要检测的。我的问题是，我如何计算这10个日期时间戳的9个时间差？有什么方法可以将timediff()应用于整个查询吗？

Answer 1

在MySQL中，这相当简单，因为您可以使用一个变量来存储每行的传感器时间，然后在计算时间差时在下一行使用该变量。这种技术在MS SQL中不起作用，因为它不允许在SELECT中赋值变量，而SELECT也返回数据。它可能在其他版本的SQL中也不起作用。通常的方法是产生一个偏移量连接，由此连接返回前一行的值，但这可能相当慢。

也就是说，在MySQL中有一种方法可以做到：

SELECT 
    sensor_time,
    time_diff,
    TIME_TO_SEC(time_diff) > 30 AS alarm
FROM (
    SELECT
        sensor_time,
        TIMEDIFF(sensor_time, @prev_sensor_time) AS time_diff,
        @prev_sensor_time := sensor_time AS prev_sensor_time
    FROM sensor_table,
    (SELECT @prev_sensor_time := NULL) AS vars
    ORDER BY sensor_time ASC
) AS tmp;

+---------------------+-----------+-------+
| sensor_time         | time_diff | alarm |
+---------------------+-----------+-------+
| 2009-09-28 07:08:12 | NULL      |  NULL |
| 2009-09-28 07:08:40 | 00:00:28  |     0 |
| 2009-09-28 07:09:10 | 00:00:30  |     0 |
| 2009-09-28 07:09:40 | 00:00:30  |     0 |
| 2009-09-28 07:10:10 | 00:00:30  |     0 |
| 2009-09-28 07:10:40 | 00:00:30  |     0 |
| 2009-09-28 07:41:10 | 00:30:30  |     1 |
| 2009-09-28 07:41:40 | 00:00:30  |     0 |
| 2009-09-28 07:42:10 | 00:00:30  |     0 |
| 2009-09-28 07:42:40 | 00:00:30  |     0 |
+---------------------+-----------+-------+