Oracle中两个日期时间相减获取秒数结果有误如何解决

结果少1秒由两个原因共同导致：

• DATE类型CAST为TIMESTAMP后，相减得到的是INTERVAL DAY TO SECOND类型，秒字段默认保留小数精度，10秒的差值可能因精度误差实际被计算为+000000000 00:00:09.999999999
• 正则\d{2}:\d{2}:\d{2}仅匹配整数秒部分，会直接截断小数，9.999秒就会被识别为9秒

推荐直接用DATE类型计算总秒数后转时分秒格式，避免精度问题：

NVL(TO_CHAR( table.time1,'YYYY/MM/DD HH24:MI:SS'),' ') "Time1",
NVL(TO_CHAR( table.time2,'YYYY/MM/DD HH24:MI:SS'),' ') "Time2",
NVL(dbms_lob.substr(oprm.message,4000),' ') AS "Messages",
-- 先计算总秒数并四舍五入消除精度误差，再拼接为时分秒格式
NVL(
  TO_CHAR(TRUNC(ROUND((table.time2 - table.time1)*86400)/3600), 'FM00') || ':' ||
  TO_CHAR(TRUNC(MOD(ROUND((table.time2 - table.time1)*86400), 3600)/60), 'FM00') || ':' ||
  TO_CHAR(MOD(ROUND((table.time2 - table.time1)*86400), 60), 'FM00'),
' ') AS duration

如果需要保留TIMESTAMP计算逻辑，可在提取秒字段时做四舍五入处理：

WITH time_diff AS (
  SELECT 
    table.time1,
    table.time2,
    oprm.message,
    CAST(table.time2 AS TIMESTAMP) - CAST(table.time1 AS TIMESTAMP) AS diff
  FROM your_table table
  -- 保留原有关联条件
)
SELECT
  NVL(TO_CHAR(time1,'YYYY/MM/DD HH24:MI:SS'),' ') "Time1",
  NVL(TO_CHAR(time2,'YYYY/MM/DD HH24:MI:SS'),' ') "Time2",
  NVL(dbms_lob.substr(message,4000),' ') AS "Messages",
  NVL(
    LPAD(EXTRACT(HOUR FROM diff), 2, '0') || ':' ||
    LPAD(EXTRACT(MINUTE FROM diff), 2, '0') || ':' ||
    LPAD(ROUND(EXTRACT(SECOND FROM diff)), 2, '0'),
  ' ') AS duration
FROM time_diff

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