Oracle中如何计算两个日期差值并输出为HH:MI:SS格式？

你遇到的问题很典型——Oracle里直接对日期（或日期字符串）做减法，得到的是以天为单位的数值，直接用to_char格式化这个数值，会因为它不是日期类型而输出完全不符合预期的格式。你看到的+000000001 01:01:000，其实是Oracle把这个天数数值当成儒略日解析后的结果，和我们要的时长格式完全不搭。

下面给你两种可靠的实现方式，都能正确输出超过24小时的时长（比如你示例里的25:01:00）：

方法一：通过总秒数拆分时分秒 #

这种方法先计算两个日期的总秒数差，再拆分出小时、分钟、秒，适合需要精确控制格式的场景：

SELECT 
    -- 计算总小时数并补前导零
    LPAD(FLOOR((date_end - date_start) * 24), 2, '0') || ':' ||
    -- 计算剩余分钟数并补零
    LPAD(FLOOR(MOD((date_end - date_start) * 24 * 60, 60)), 2, '0') || ':' ||
    -- 计算剩余秒数并补零（ROUND处理小数秒）
    LPAD(ROUND(MOD((date_end - date_start) * 24 * 60 * 60, 60)), 2, '0') AS duration
FROM (
    -- 先将字符串转为DATE类型，这一步必须有，避免隐式转换出错
    SELECT 
        TO_DATE('15/11/2015 11:19:58', 'DD/MM/YYYY HH24:MI:SS') AS date_end,
        TO_DATE('14/11/2015 10:20:58', 'DD/MM/YYYY HH24:MI:SS') AS date_start
    FROM dual
);

方法二：使用INTERVAL类型提取时长 #

利用Oracle的INTERVAL DAY TO SECOND类型处理时间差，逻辑更直观：

SELECT 
    -- 提取天数转成小时 + 提取小时数，补零
    LPAD(EXTRACT(DAY FROM time_diff) * 24 + EXTRACT(HOUR FROM time_diff), 2, '0') || ':' ||
    -- 提取分钟数补零
    LPAD(EXTRACT(MINUTE FROM time_diff), 2, '0') || ':' ||
    -- 提取秒数补零
    LPAD(EXTRACT(SECOND FROM time_diff), 2, '0') AS duration
FROM (
    SELECT 
        NUMTODSINTERVAL(
            TO_DATE('15/11/2015 11:19:58', 'DD/MM/YYYY HH24:MI:SS') - 
            TO_DATE('14/11/2015 10:20:58', 'DD/MM/YYYY HH24:MI:SS'), 
            'DAY'
        ) AS time_diff
    FROM dual
);

关键注意点 #

• 一定要先把字符串用TO_DATE转成DATE类型，不要直接对字符串做减法——Oracle的隐式转换可能会导致错误或者性能问题。
• 两种方法都用LPAD补前导零，确保输出是标准的两位格式（比如01而不是1）。

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