MySQL / MariaDB：如何查找基于时间的数据的差距？

记录器系统每 5 秒保存一行数据（秒==0,5,10,15,...,55；时间类似于23:00:07不可能）。

有时，记录器由于通信错误而无法保存，并且表中只是缺少行。

我需要检测这些间隙：我想读取间隙之前的最后一行和间隙之后的第一行。

这些是演示数据：

create table #time (
    DateTime datetime not null,
    Value int not null
);

insert into #time (DateTime, Value)
    values
        ('2018-08-23 00:00:00', 123),
        ('2018-08-23 00:00:05', 152),
        ('2018-08-23 00:00:10',  37),
        ('2018-08-23 00:00:15', 141),
        ('2018-08-23 00:00:20',  41),
        ('2018-08-23 00:00:25',  35),
        ('2018-08-23 00:00:30', 143),
        ('2018-08-23 00:00:35',  67),
        ('2018-08-23 00:00:40', 111),
                                        /* gap of one minute here */
        ('2018-08-23 00:01:45', 123),
        ('2018-08-23 00:01:50', 145),
        ('2018-08-23 00:01:55', 141),
        ('2018-08-23 00:02:00',  87),
                                        /* gap of 10 seconds here */
        ('2018-08-23 00:02:15', 190),
        ('2018-08-23 00:02:20', 122),
        ('2018-08-23 00:02:25', 123);

select * from #time;

他们也在雷克斯测试仪 http://rextester.com/AMEC43472

我想回读一下：

    GapStart              GapEnd                Size
0   2018-08-23 00:00:40   2018-08-23 00:01:45   0000-00-00 00:01:45
1   2018-08-23 00:02:00   2018-08-23 00:02:15   0000-00-00 00:00:15

当然不应列出 5 秒的间隙

我发现的是同一行中的开始/结束日期时间（查找时间表数据中特定时间之间的差距 https://stackoverflow.com/questions/45444166/find-gaps-in-timesheet-data-between-certain-hours），或者太复杂for me适应我的情况（查找连续日期中的空白 https://stackoverflow.com/questions/35200652/find-gaps-in-sequential-dates).

我正在使用 MariaDB 10.0.28（无法升级）：这意味着LAG()不可用。

先感谢您

一种方法可能是首先进行子查询，并将每个记录与具有最近的较大时间戳的记录配对。然后，查询并返回具有足够大小间隙的所有记录。

SELECT
    DateTime AS GapStart,
    NextDateTime AS GapEnd,
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) AS SizeInSecond
FROM
(
    SELECT DateTime, Value,
        (SELECT MIN(DateTime) FROM #time t2
         WHERE t2.DateTime > t1.DateTime) AS NextDateTime
    FROM #time t1
) t
WHERE
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) > 5;   -- or whatever threshhold you want

Demo http://rextester.com/KLFSA79205

添加到原始答案

If DateTime总是在增长，可以通过改变内部SELECT来获得速度的提升：

SELECT
    DateTime AS GapStart,
    NextDateTime AS GapEnd,
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) AS SizeInSecond
FROM
(
    SELECT DateTime, Value,
        (SELECT DateTime FROM #time t2
         WHERE t2.DateTime > t1.DateTime LIMIT 1) AS NextDateTime
    FROM #time t1
) t
WHERE
    TIMESTAMPDIFF(SECOND, DateTime, NextDateTime) > 5;