如何在PLSQL中计算两个日期的时间差并按x天y小时z秒t分输出

在Oracle PLSQL中，两个DATE类型字段直接相减会得到相差的总天数，通过对这个数值做维度拆分即可得到你需要的格式的结果，常用两种实现方式如下：

方式1：自定义函数（复用性最高） #

适合需要频繁计算日期差的场景，创建一次即可全局调用：

CREATE OR REPLACE FUNCTION calc_date_diff(p_start_dt DATE, p_end_dt DATE)
RETURN VARCHAR2
IS
    v_total_diff NUMBER;
    v_days NUMBER;
    v_hours NUMBER;
    v_mins NUMBER;
    v_secs NUMBER;
BEGIN
    -- 取绝对值，需要保留正负标识时间先后的话可以去掉ABS
    v_total_diff := ABS(p_end_dt - p_start_dt);
    
    -- 拆分天
    v_days := TRUNC(v_total_diff);
    -- 拆分小时
    v_hours := TRUNC((v_total_diff - v_days) * 24);
    -- 拆分分钟
    v_mins := TRUNC(((v_total_diff - v_days) * 24 - v_hours) * 60);
    -- 拆分秒，需要截断小数秒就把ROUND换成TRUNC
    v_secs := ROUND((((v_total_diff - v_days) * 24 - v_hours) * 60 - v_mins) * 60);

    -- 处理进位边界
    IF v_secs >= 60 THEN
        v_mins := v_mins + TRUNC(v_secs/60);
        v_secs := MOD(v_secs, 60);
    END IF;
    IF v_mins >= 60 THEN
        v_hours := v_hours + TRUNC(v_mins/60);
        v_mins := MOD(v_mins, 60);
    END IF;
    IF v_hours >= 24 THEN
        v_days := v_days + TRUNC(v_hours/24);
        v_hours := MOD(v_hours, 24);
    END IF;

    -- 按要求拼接为x天y小时z秒t分钟格式，需要调整顺序直接改字段位置即可
    RETURN v_days || '天' || v_hours || '小时' || v_secs || '秒' || v_mins || '分钟';
END;
/

调用示例：
SELECT calc_date_diff(TO_DATE('2024-05-01 08:20:15', 'yyyy-mm-dd hh24:mi:ss'), TO_DATE('2024-05-03 10:25:30', 'yyyy-mm-dd hh24:mi:ss')) AS date_diff FROM dual;
返回结果示例：2天2小时30秒5分钟

方式2：单SQL直接计算（无需创建函数） #

适合单次查询临时使用的场景：

SELECT 
    v_days || '天' || v_hours || '小时' || v_secs || '秒' || v_mins || '分钟' AS date_diff
FROM (
    SELECT 
        TRUNC(total_diff) AS v_days,
        TRUNC((total_diff - TRUNC(total_diff)) * 24) AS v_hours,
        TRUNC(((total_diff - TRUNC(total_diff)) * 24 - TRUNC((total_diff - TRUNC(total_diff)) * 24)) * 60) AS v_mins,
        ROUND((((total_diff - TRUNC(total_diff)) * 24 - TRUNC((total_diff - TRUNC(total_diff)) * 24)) * 60 - TRUNC(((total_diff - TRUNC(total_diff)) * 24 - TRUNC((total_diff - TRUNC(total_diff)) * 24)) * 60)) * 60) AS v_secs
    FROM (
        -- 替换这里的两个日期为你需要计算的字段即可
        SELECT ABS(TO_DATE('2024-05-01 08:20:15', 'yyyy-mm-dd hh24:mi:ss') - TO_DATE('2024-05-03 10:25:30', 'yyyy-mm-dd hh24:mi:ss')) AS total_diff FROM dual
    )
);

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