如何正确使用Oracle的to_char？解决时间差格式化多余前缀问题

问题出在两个TIMESTAMP相减后得到的是INTERVAL类型，Oracle中直接对INTERVAL调用TO_CHAR()时，默认格式会包含多余的年/月字段（就是你看到的+000000000），而且hh24:mi:SS.FF这类DATE/TIMESTAMP的格式语法对INTERVAL不生效，所以才会出现不符合预期的结果。

直接通过EXTRACT()拆分INTERVAL的各个时间部分再拼接，就能得到你需要的格式：

UPDATE MY_TABLE
SET ENDTIME = COALESCE(:ENDTIME, ENDTIME),
    ELAPSEDTIME = EXTRACT(DAY FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME)) || 'd ' ||
        LPAD(EXTRACT(HOUR FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME)), 2, '0') || ':' ||
        LPAD(EXTRACT(MINUTE FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME)), 2, '0') || ':' ||
        LPAD(FLOOR(EXTRACT(SECOND FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME))), 2, '0') || '.' ||
        LPAD(TRUNC((EXTRACT(SECOND FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME)) - FLOOR(EXTRACT(SECOND FROM (CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME)))) * 1000), 3, '0')
WHERE MISSIONID = :MISSIONID

关键说明 #

• EXTRACT()可以精准提取INTERVAL类型中的日、时、分、秒数值，其中秒需要拆分为整数部分和小数部分，通过乘以1000后取整得到三位毫秒值。
• LPAD()用来保证时、分、秒为两位数字，毫秒为三位数字，不足时自动补0，完全匹配你要的0d 12:08:16.132格式。

如果觉得重复写时间差表达式太啰嗦，也可以用WITH子句简化（Oracle 12c及以上版本支持）：

WITH mission_diff AS (
    SELECT 
        MISSIONID,
        CAST (:ENDTIME AS TIMESTAMP(6)) - STARTTIME AS diff
    FROM MY_TABLE
    WHERE MISSIONID = :MISSIONID
)
UPDATE MY_TABLE t
SET ENDTIME = COALESCE(:ENDTIME, t.ENDTIME),
    ELAPSEDTIME = EXTRACT(DAY FROM md.diff) || 'd ' ||
        LPAD(EXTRACT(HOUR FROM md.diff), 2, '0') || ':' ||
        LPAD(EXTRACT(MINUTE FROM md.diff), 2, '0') || ':' ||
        LPAD(FLOOR(EXTRACT(SECOND FROM md.diff)), 2, '0') || '.' ||
        LPAD(TRUNC((EXTRACT(SECOND FROM md.diff) - FLOOR(EXTRACT(SECOND FROM md.diff))) * 1000), 3, '0')
FROM mission_diff md
WHERE t.MISSIONID = md.MISSIONID

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