MySQL 怎么计算相邻两条记录的时间差

如果一张日志表里保存了设备编号和发生时间，想知道每条记录距离上一条记录过了多久，最稳妥的写法是先用 LAG() 取出上一行，再用 TIMESTAMPDIFF() 做日期时间相减。不要直接依赖物理存储顺序，上一条必须由明确的 ORDER BY 定义。

MySQL 8.0 及以上可以用 LAG(recorded_at) OVER (PARTITION BY device_id ORDER BY recorded_at, id) 找到同一设备的上一条时间，再用 TIMESTAMPDIFF(SECOND, prev_recorded_at, recorded_at) 计算间隔。每个分组的第一行没有上一条记录，所以差值应为 NULL。

先用 LAG() 排出上一条时间

假设表名为 device_event，字段如下：

先把“上一条”单独算出来，后面的差值表达式会更容易读：

WITH ordered_event AS (
    SELECT
        id,
        device_id,
        recorded_at,
        LAG(recorded_at) OVER (
            PARTITION BY device_id
            ORDER BY recorded_at, id
        ) AS prev_recorded_at
    FROM device_event
)
SELECT *
FROM ordered_event
ORDER BY device_id, recorded_at, id;

PARTITION BY device_id 表示每台设备重新开始计算；如果整张表只有一个连续序列，可以去掉它。ORDER BY recorded_at, id 中的 id 是稳定的并列时间兜底，否则两条时间完全相同的记录没有唯一先后。

用 TIMESTAMPDIFF() 计算秒数或分钟

在上一层结果上计算差值：

WITH ordered_event AS (
    SELECT
        id,
        device_id,
        recorded_at,
        LAG(recorded_at) OVER (
            PARTITION BY device_id
            ORDER BY recorded_at, id
        ) AS prev_recorded_at
    FROM device_event
)
SELECT
    id,
    device_id,
    recorded_at,
    prev_recorded_at,
    TIMESTAMPDIFF(SECOND, prev_recorded_at, recorded_at) AS gap_seconds,
    TIMESTAMPDIFF(MINUTE, prev_recorded_at, recorded_at) AS gap_minutes
FROM ordered_event
WHERE prev_recorded_at IS NOT NULL
ORDER BY device_id, recorded_at, id;

TIMESTAMPDIFF() 的参数顺序是“开始时间、结束时间”，返回结束时间减开始时间的整数结果。这里把首行过滤掉，是因为首行没有上一条时间；如果你希望保留首行做完整序列展示，可以删掉 WHERE，它的差值会自然保持 NULL。

不能在同一层的 WHERE 中直接引用窗口函数结果，所以用 CTE 或子查询先得到 prev_recorded_at。这也是把窗口计算和业务筛选分开的关键。

处理分组、并列时间和 NULL

这类 SQL 最容易错的不是函数拼写，而是“相邻”的定义。先对照下面的判断：

如果设备事件可能乱序到达，应按事件发生时间 recorded_at 排序，而不是按自增 id 排序。若业务要回答“写入间隔”，才把排序字段换成写入时间。两者含义不同，不能只因为 id 有索引就替换。

选择展示格式并做查询检查

统计通常选整数单位，展示则可以换成时间格式：

-- 需要小时、分钟、秒的显示值
TIMEDIFF(recorded_at, prev_recorded_at) AS gap_time

-- 需要微秒级整数差值
TIMESTAMPDIFF(MICROSECOND, prev_recorded_at, recorded_at) AS gap_microseconds

TIMEDIFF() 返回时间值，适合直接展示；TIMESTAMPDIFF() 返回指定单位的整数，适合阈值判断和聚合。使用 MINUTE 或 HOUR 时要注意它会舍去不足一个单位的部分，精细统计应先取秒或微秒。

上线前至少检查三件事：窗口排序是否覆盖业务真实顺序；分组字段是否与统计口径一致；筛选条件是否放在窗口计算之后。官方文档对 LAG() 的相邻行语义和 TIMESTAMPDIFF() 的单位与参数顺序都有明确说明。

常见问题

为什么第一条记录的时间差是 NULL？

因为它在自己的窗口分组里没有上一条记录。保留它可以表达完整序列，统计平均间隔时通常在外层排除。

能不能用当前行时间减上一行时间？

可以，但上一行必须由 LAG() 和确定的排序得到。不要依赖表的物理顺序或自增键推断事件先后。

时间差需要保留小数怎么办？

使用 MICROSECOND 得到整数微秒，或根据业务精度换算；不要直接用分钟结果再乘回秒数，因为单位截断会丢失精度。