Oracle SQL中如何计算系统日期与任务创建日期的天时分差值？

错误原因 #

你之前的代码问题在于：TO_CHAR中的dd格式符代表的是具体日期的日份，而非两个日期之间的间隔天数。当你把sysdate - CREATE_DATE_PL的天数差值加到1970-01-01上时，得到的是一个具体日期，dd会取该日期的日部分，而非实际间隔天数。比如间隔31天的话，得到的日期是1970-02-01，dd会显示01，而非正确的31，这就导致结果错误。

正确解决方法 #

方法一：数值拆分拼接

利用Oracle日期相减返回天数（含小数，比如1.25天=1天6小时）的特性，拆分出天、小时、分钟后拼接成指定格式：

SELECT
    -- 提取整数天数
    TRUNC(sysdate - a.CREATE_DATE_PL) || ':' ||
    -- 提取小时数，补零为两位格式
    LPAD(TRUNC((sysdate - a.CREATE_DATE_PL) * 24) - TRUNC(sysdate - a.CREATE_DATE_PL) * 24, 2, '0') || ':' ||
    -- 提取分钟数，补零为两位格式
    LPAD(TRUNC((sysdate - a.CREATE_DATE_PL) * 24 * 60) - TRUNC((sysdate - a.CREATE_DATE_PL) * 24) * 60, 2, '0') AS TT_LIFETIME
FROM your_table a;

方法二：使用INTERVAL类型（Oracle 11g及以上版本支持）

将天数差值转换为INTERVAL DAY TO SECOND类型，再提取各时间分量：

SELECT
    EXTRACT(DAY FROM diff) || ':' ||
    LPAD(EXTRACT(HOUR FROM diff), 2, '0') || ':' ||
    LPAD(EXTRACT(MINUTE FROM diff), 2, '0') AS TT_LIFETIME
FROM (
    -- 将天数差值转换为日-秒间隔类型
    SELECT NUMTODSINTERVAL(sysdate - a.CREATE_DATE_PL, 'DAY') AS diff
    FROM your_table a
);

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