如何在SQL中跨两张表计算日期时间差值（天、时、分）

嘿，这个需求我经常碰到，不同数据库的实现方式略有不同，我给你整理几个主流SQL方言的解决方案，你根据自己用的数据库来选就行～

首先要注意的前提 #

你得先确定两张表之间的关联条件（比如某个业务ID匹配），不然直接关联会得到笛卡尔积，结果完全不对；另外要保证Collect和Disc列是合法的日期时间类型（比如datetime/timestamp），如果是字符串类型的话，得先转成时间类型再计算。

1. MySQL/MariaDB 版本 #

用TIMESTAMPDIFF函数直接计算差值，再通过取模拆分出天、小时、分钟：

SELECT 
    a.Disc,
    p.Collect,
    -- 计算总天数
    TIMESTAMPDIFF(DAY, p.Collect, a.Disc) AS days_diff,
    -- 剩余小时数（总小时数减去天数×24）
    TIMESTAMPDIFF(HOUR, p.Collect, a.Disc) % 24 AS hours_diff,
    -- 剩余分钟数（总分钟数减去总小时数×60）
    TIMESTAMPDIFF(MINUTE, p.Collect, a.Disc) % 60 AS minutes_diff,
    -- 拼接成友好的字符串格式
    CONCAT(
        TIMESTAMPDIFF(DAY, p.Collect, a.Disc), '天 ',
        TIMESTAMPDIFF(HOUR, p.Collect, a.Disc) % 24, '小时 ',
        TIMESTAMPDIFF(MINUTE, p.Collect, a.Disc) % 60, '分钟'
    ) AS time_diff_str
FROM Adm a
JOIN plc p 
    -- 替换成你的实际关联条件，比如 a.device_id = p.device_id
    ON a.some_id = p.some_id;

如果担心Disc早于Collect出现负数，可以给每个差值套个ABS()函数。

2. SQL Server 版本 #

SQL Server用DATEDIFF计算总差值，再通过DATEADD剔除已计算的大单位，得到剩余小单位：

SELECT 
    a.Disc,
    p.Collect,
    -- 总天数
    DATEDIFF(DAY, p.Collect, a.Disc) AS days_diff,
    -- 剩余小时：先把起始时间加上已算的天数，再算小时差
    DATEDIFF(HOUR, DATEADD(DAY, DATEDIFF(DAY, p.Collect, a.Disc), p.Collect), a.Disc) AS hours_diff,
    -- 剩余分钟：同理，剔除天数和小时后算分钟差
    DATEDIFF(MINUTE, DATEADD(HOUR, DATEDIFF(HOUR, p.Collect, a.Disc), p.Collect), a.Disc) AS minutes_diff,
    -- 拼接字符串
    CONCAT(
        DATEDIFF(DAY, p.Collect, a.Disc), '天 ',
        DATEDIFF(HOUR, DATEADD(DAY, DATEDIFF(DAY, p.Collect, a.Disc), p.Collect), a.Disc), '小时 ',
        DATEDIFF(MINUTE, DATEADD(HOUR, DATEDIFF(HOUR, p.Collect, a.Disc), p.Collect), a.Disc), '分钟'
    ) AS time_diff_str
FROM Adm a
JOIN plc p 
    -- 替换成你的实际关联条件
    ON a.some_id = p.some_id;

3. PostgreSQL 版本 #

PostgreSQL对时间处理更友好，直接用时间减法得到interval类型，再提取各单位即可：

SELECT 
    a.Disc,
    p.Collect,
    -- 提取天数
    EXTRACT(DAY FROM (a.Disc - p.Collect)) AS days_diff,
    -- 提取小时数
    EXTRACT(HOUR FROM (a.Disc - p.Collect)) AS hours_diff,
    -- 提取分钟数
    EXTRACT(MINUTE FROM (a.Disc - p.Collect)) AS minutes_diff,
    -- 直接格式化输出成字符串，省得自己拼接
    TO_CHAR(a.Disc - p.Collect, 'DD"天" HH24"小时" MI"分钟"') AS time_diff_str
FROM Adm a
JOIN plc p 
    -- 替换成你的实际关联条件
    ON a.some_id = p.some_id;

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