Oracle SQL时间转换遇ORA-01873/ORA-01760错误求解决

问题原因分析 #

• ORA-01873错误：NUMTODSINTERVAL函数的处理范围有限，它能支持的最大秒数对应时间仅从1970年往后延伸约292年（到2262年左右）。当你的VALUE_KEY.VALUE对应2999年的时间戳时，秒数超出了该函数的精度范围，因此触发错误。
• ORA-01760错误：NUMTODSINTERVAL的第二个参数仅支持'DAY'、'HOUR'、'MINUTE'、'SECOND'这几个选项，不能传入'YEAR'，所以修改参数后会触发参数非法的错误。

修改后的代码 #

TO_CHAR(
   FROM_TZ(
      TIMESTAMP '1970-01-01 00:00:00.000' + (VALUE_KEY.VALUE / 1000) * INTERVAL '1' SECOND,
      'GMT'
   ) AT TIME ZONE 'Europe/Warsaw',
   'YYYY-MM-DD"T"HH24:MI:SS:FF3TZHTZM'
) AS "New Date Format",

代码说明 #

• 替换NUMTODSINTERVAL逻辑：用(VALUE_KEY.VALUE / 1000) * INTERVAL '1' SECOND直接计算时间偏移，这种方式支持的时间范围远大于NUMTODSINTERVAL，能轻松覆盖2999年的时间戳。
• 移除冗余转换：原代码中的两次CAST操作都是多余的，直接通过TIMESTAMP加时间间隔的方式，就能被FROM_TZ识别为合法的时间类型，后续时区转换也能正常执行。

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