Oracle SQL中TO_CHAR转换Interval时不生效？如何输出HH24:MI:SS格式？

问题根因 #

Oracle的TO_CHAR函数不支持对INTERVAL类型使用日期时间类的格式掩码，你传入的'HH24:MI:SS'参数对INTERVAL类型无效，因此最终输出的是INTERVAL的默认字符串格式化结果，也就是你看到的带天数前缀、小数秒后缀的完整格式。

解决方案 #

根据你的业务场景选对应写法即可：

场景1：秒数不会超过86400（时长小于1天） #

直接把秒数换算为天数加到任意日期的零点上，再用TO_CHAR格式化即可，写法最简单：

CASE 
  WHEN TR.RUN_LENGTH > 0 THEN TO_CHAR(TRUNC(SYSDATE) + TR.RUN_LENGTH / 86400, 'HH24:MI:SS')
  ELSE '0' 
END AS RUN_LENGTH

注意：如果秒数超过86400，这个写法会自动对24小时取模，比如90000秒会显示为01:00:00而非25:00:00，适合时长不会超过1天的业务场景。

场景2：秒数可能超过86400（需保留超出24小时的部分） #

用EXTRACT函数从INTERVAL中分别提取天、时、分、秒，自行拼接为需要的格式：

CASE 
  WHEN TR.RUN_LENGTH > 0 THEN 
    LPAD(EXTRACT(DAY FROM NUMTODSINTERVAL(TR.RUN_LENGTH, 'second')) * 24 + EXTRACT(HOUR FROM NUMTODSINTERVAL(TR.RUN_LENGTH, 'second')), 2, '0')
    || ':' || LPAD(EXTRACT(MINUTE FROM NUMTODSINTERVAL(TR.RUN_LENGTH, 'second')), 2, '0')
    || ':' || LPAD(TRUNC(EXTRACT(SECOND FROM NUMTODSINTERVAL(TR.RUN_LENGTH, 'second'))), 2, '0')
  ELSE '0' 
END AS RUN_LENGTH

这个写法可以正确处理超过24小时的时长，比如90000秒会输出25:00:00，加TRUNC是为了去掉秒的小数部分，如果你需要保留小数可以去掉该函数。

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