Oracle中如何计算两个varchar类型日期的差值并转天时分秒格式

你当前的写法是将两个字符串格式的时间值直接做数值减法，得到的是数字本身的差值而非真实时间间隔，不符合需求的核心原因是没有先把varchar类型的时间字段转为DATE类型再计算差值。

核心逻辑 #

• 先用TO_DATE函数将start_time从YYYYMMDDHH24MISS格式的varchar转为DATE类型
• 两个DATE类型直接相减得到的结果以「天」为单位，整数部分为完整天数，小数部分为不足一天的时长
• 对差值做数学运算拆分出天、小时、分钟、秒后，拼接为需要的格式

完整查询语句 #

SELECT 
    TRUNC(time_diff) || '天' || 
    TRUNC(MOD(time_diff * 24, 24)) || '小时' || 
    ROUND(MOD(time_diff * 24 * 60 * 60, 60)) || '秒' AS formatted_diff
FROM (
    SELECT 
        SYSDATE - TO_DATE(start_time, 'YYYYMMDDHH24MISS') AS time_diff
    FROM TABLEA 
    WHERE job_name = 'jOB_AA_BB'
) t;

可选字段拆分写法 #

如果需要单独返回天、小时、秒的数值结果，可使用以下语句：

SELECT 
    TRUNC(time_diff) AS diff_days,
    TRUNC(MOD(time_diff * 24, 24)) AS diff_hours,
    ROUND(MOD(time_diff * 24 * 60 * 60, 60)) AS diff_seconds
FROM (
    SELECT 
        SYSDATE - TO_DATE(start_time, 'YYYYMMDDHH24MISS') AS time_diff
    FROM TABLEA 
    WHERE job_name = 'jOB_AA_BB'
) t;

注意事项 #

• 若start_time字段存在不符合格式的脏数据，Oracle 12c及以上版本可使用TO_DATE(start_time DEFAULT NULL ON CONVERSION ERROR, 'YYYYMMDDHH24MISS')避免查询报错
• 计算结果为正表示start_time早于当前系统时间，为负表示start_time晚于当前系统时间

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