SQL动态计算日期差：适配任意行数的时间戳分钟间隔计算

完全可以实现，核心思路是用**窗口函数LAG()**获取当前行的上一行时间戳，再结合时间差计算函数（比如DATEDIFF或对应数据库的时间差函数）得到分钟间隔，这个方案适配任意行数的数据集。

以下是主流数据库的实现示例：

MySQL #

SELECT
    time_stamp,
    CASE
        WHEN LAG(time_stamp) OVER (ORDER BY time_stamp) IS NULL THEN '--'
        ELSE TIMESTAMPDIFF(MINUTE, LAG(time_stamp) OVER (ORDER BY time_stamp), time_stamp)
    END AS diff_in_minutes
FROM your_table;

• LAG(time_stamp) OVER (ORDER BY time_stamp)：按时间戳排序后，获取当前行的上一行时间戳，第一行无前置行时返回NULL
• TIMESTAMPDIFF(MINUTE, 起始时间, 结束时间)：计算两个时间戳的分钟差

SQL Server #

SELECT
    time_stamp,
    CASE
        WHEN LAG(time_stamp) OVER (ORDER BY time_stamp) IS NULL THEN '--'
        ELSE DATEDIFF(MINUTE, LAG(time_stamp) OVER (ORDER BY time_stamp), time_stamp)
    END AS diff_in_minutes
FROM your_table;

• SQL Server的DATEDIFF参数顺序为DATEDIFF(单位, 起始时间, 结束时间)，和MySQL的TIMESTAMPDIFF逻辑一致

PostgreSQL #

PostgreSQL没有内置DATEDIFF函数，可通过时间差提取各时间单位再转换为分钟：

SELECT
    time_stamp,
    CASE
        WHEN LAG(time_stamp) OVER (ORDER BY time_stamp) IS NULL THEN '--'
        ELSE DATE_PART('minute', AGE(time_stamp, LAG(time_stamp) OVER (ORDER BY time_stamp)))
             + DATE_PART('hour', AGE(time_stamp, LAG(time_stamp) OVER (ORDER BY time_stamp))) * 60
             + DATE_PART('day', AGE(time_stamp, LAG(time_stamp) OVER (ORDER BY time_stamp))) * 1440
    END AS diff_in_minutes
FROM your_table;

• AGE(结束时间, 起始时间)：返回两个时间的间隔，再提取分钟、小时、天单位，转换为总分钟数

以上查询会自动处理任意行数的数据集，第一行无前置数据时显示--，其余行准确计算与上一行的分钟间隔。

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