How to find a time delta of a datatime row in mysq

Question

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.

Supposing my query is:

select sensor_time from sensor_table limit 10;

I'd get:

+---------------------+
| 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 |
+---------------------+

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?

Answer 1

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.

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 |
+---------------------+-----------+-------+