sql - 如何在 mysql 中查找日期时间行的时间增量？

Question

我在数据库中有一列，它为一系列传感器读数提供日期时间戳。我想将那些读数分成不间断的、连续的传感器读数集。如果传感器读数损坏，则日期时间列中将不连续。因此，我想对日期时间列执行查询，然后计算连续读数之间的差异。

假设我的查询是:

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 中分配变量。它可能也不适用于其他版本的 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 |
+---------------------+-----------+-------+