如何在MySQL中找到数据时间行的时间增量? [英] How to find a time delta of a datatime row in mysql?

问题描述

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

I have a column in a database which provides datetime stamps for series of sensor readings. I'd like to segment those reading into unbroken, continuous sets of sensor readings. If a sensor reading is broken, then there will be a discontinuity in the date time column. And so I'd like to perform a query on the datetime column and then compute the difference between consecutive readings.

假设我的查询是:

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()应用于整个查询吗?

The times in this example suddenly jump from 07:10 to 07:41, which I'd like to detect. My question is how I could compute the 9 time differences for these 10 datetime stamps? Is there some way to apply timediff() to an entire query?

推荐答案

在MySQL中，这非常简单，因为您可以使用变量存储每一行​​的传感器时间，然后在计算时间差异.该技术在MS SQL中不起作用，因为它不允许在也返回数据的SELECT中分配变量.它也可能在其他版本的SQL中也不起作用.通常的方法是产生一个偏移联接，该联接从前一行返回值，但这可能会很慢.

In MySQL, this is fairly easy, as you can use a variable to store the sensor time for each row, then use that at the next row when calculating the time difference. This technique does not work in MS SQL as it doesn't allow variables to be assigned in a SELECT which is also returning data. It probably won't work in other versions of SQL either. The usual method would be to produce an offset join, whereby the join returns values from the previous row, but this can be quite slow.

也就是说，这是在MySQL中执行此操作的一种方法:

That said, here's one way to do it in 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 |
+---------------------+-----------+-------+

这篇关于如何在MySQL中找到数据时间行的时间增量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！