如何计算前序ID的endTime与后序ID的startTime的时间差？

原始数据表 #

booking表的初始数据：

id   startTime            endTime 
1    2022-12-3 13:00:00   2022-12-3 14:00:00
2    2022-12-3 14:00:00   2022-12-3 14:30:00
3    2022-12-3 15:00:00   2022-12-3 15:15:00 
4    2022-12-3 15:30:00   2022-12-3 16:30:00
5    2022-12-3 18:30:00   2022-12-3 19:00:00

已实现的单条记录时间差计算 #

你已经实现了计算单条记录内startTime与endTime的差值（判断是否为60分钟），对应的SQL语句：

SELECT startTime, endTime, 
(TIMESTAMPDIFF(MINUTE, startTime , endTime) = '60') AS MinuteDiff 
FROM booking

执行后的输出：

id   startTime            endTime             MinuteDiff
1    2022-12-3 13:00:00   2022-12-3 14:00:00  1
2    2022-12-3 14:00:00   2022-12-3 14:30:00  0
3    2022-12-3 15:00:00   2022-12-3 15:15:00  0
4    2022-12-3 15:30:00   2022-12-3 16:30:00  1
5    2022-12-3 18:30:00   2022-12-3 19:00:00  0

问题需求 #

已实现单条ID记录的startTime与endTime差值计算，现在需要计算相邻ID间，前一条记录的endTime与后一条记录的startTime的时间差（比如ID1的endTime和ID2的startTime的差值）。

解决方案 #

使用MySQL的**窗口函数LAG()**可以轻松实现这个需求，LAG()用于获取当前行的前一行指定字段的值，结合TIMESTAMPDIFF()计算时间差。

完整SQL语句 #

SELECT 
    id,
    startTime,
    endTime,
    -- 获取上一条记录的endTime
    LAG(endTime) OVER (ORDER BY id) AS prev_endTime,
    -- 计算前一条endTime到当前startTime的分钟差
    TIMESTAMPDIFF(MINUTE, LAG(endTime) OVER (ORDER BY id), startTime) AS adjacent_minute_diff
FROM booking
ORDER BY id;

执行结果 #

id   startTime            endTime             prev_endTime          adjacent_minute_diff
1    2022-12-3 13:00:00   2022-12-3 14:00:00  NULL                  NULL
2    2022-12-3 14:00:00   2022-12-3 14:30:00  2022-12-3 14:00:00    0
3    2022-12-3 15:00:00   2022-12-3 15:15:00  2022-12-3 14:30:00    30
4    2022-12-3 15:30:00   2022-12-3 16:30:00  2022-12-3 15:15:00    15
5    2022-12-3 18:30:00   2022-12-3 19:00:00  2022-12-3 16:30:00    120

说明 #

• LAG(endTime) OVER (ORDER BY id)：按照id排序，获取当前行的前一行的endTime，第一条记录没有前一行，所以该字段值为NULL
• TIMESTAMPDIFF(MINUTE, ...)：以分钟为单位计算时间差，第一条记录因无前一条数据，差值为NULL

可选：排除无前置记录的第一条数据 #

如果不需要展示第一条记录，可以添加WHERE条件过滤：

SELECT 
    id,
    startTime,
    endTime,
    LAG(endTime) OVER (ORDER BY id) AS prev_endTime,
    TIMESTAMPDIFF(MINUTE, LAG(endTime) OVER (ORDER BY id), startTime) AS adjacent_minute_diff
FROM booking
WHERE LAG(endTime) OVER (ORDER BY id) IS NOT NULL
ORDER BY id;

内容的提问来源于stack exchange，提问作者Cole