DB2如何计算时间列相邻行的秒级时间差（末行时长为0）

要实现相邻行时间差计算，核心使用LEAD()窗口函数获取下一行时间值，再做秒级差值计算即可，不需要复杂自连接。

实现SQL（MySQL 8.0+ 支持窗口函数版本） #

假设你的业务表名为biz_table，SQL写法如下：

SELECT
    `date`,
    title,
    `time`,
    COALESCE(
        TIME_TO_SEC(LEAD(`time`) OVER (PARTITION BY `date` ORDER BY `time`, title)) 
        - TIME_TO_SEC(`time`),
        0
    ) AS `timeduration(seconds)`
FROM biz_table;

逻辑说明 #

• LEAD(time) OVER (PARTITION BY dateORDER BYtime, title)：
  • 按date分区，避免不同日期的时间跨天计算差值
  • 分区内先按time升序、再按title升序排列，和样例顺序完全匹配
  • 直接获取排序后当前行的下一行time值，最后一行没有下一行时该函数返回NULL
• TIME_TO_SEC()：将time类型值直接转换为当天0点到该时间的总秒数，两个秒数直接相减就是秒级差值，比TIMESTAMPDIFF更直接，不会出现隐式类型转换导致的结果错误
• COALESCE(..., 0)：将最后一行的NULL差值转换为要求的0值

结果验证 #

执行上述SQL后返回的结果和预期完全一致：

之前用TIMESTAMPDIFF未得到预期结果的常见原因 #

• 没有指定正确的分区和排序规则，导致取到的“下一行”不是业务逻辑上的下一行
• 函数参数顺序写反（TIMESTAMPDIFF第一个参数是单位，第二个是较小时间，第三个是较大时间，写反会得到负数）
• time类型隐式转换为datetime时拼接的默认日期不一致，导致差值计算错误

低版本MySQL（5.x）兼容写法 #

如果你的数据库不支持窗口函数，可以通过用户变量生成行号后自连接实现：

SELECT
    t1.`date`,
    t1.title,
    t1.`time`,
    COALESCE(TIME_TO_SEC(t2.`time`) - TIME_TO_SEC(t1.`time`), 0) AS `timeduration(seconds)`
FROM (
    SELECT
        *,
        @rownum := @rownum + 1 AS rn
    FROM biz_table, (SELECT @rownum := 0) r
    ORDER BY `date`, `time`, title
) t1
LEFT JOIN (
    SELECT
        *,
        @rownum2 := @rownum2 + 1 AS rn
    FROM biz_table, (SELECT @rownum2 := 0) r
    ORDER BY `date`, `time`, title
) t2
ON t1.`date` = t2.`date` AND t1.rn + 1 = t2.rn;

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