Oracle中日期差值转秒数与整数比较结果不符问题咨询

问题原因 #

在Oracle中，两个DATE类型值相减得到的是以天为单位的浮点数。你计算的(out_time-in_time)*24*60*60虽然显示为30，但实际上由于浮点数的精度限制，它是一个非常接近30的近似值（比如29.9999999999或30.0000000001）。当直接与整数30比较时，精度误差导致等式不成立；而delta_seconds列显示时，Oracle会对该近似值进行四舍五入，所以看起来是整数30。

解决方法 #

方法1：用ROUND函数取整后比较

通过ROUND函数对计算出的秒数进行取整，消除浮点数精度误差：

WITH m AS (
 SELECT to_date('2023-03-01 12:00:00','yyyy-mm-dd hh24:mi:ss') in_time,
        to_date('2023-03-01 12:00:30','yyyy-mm-dd hh24:mi:ss') out_time
 FROM dual
)
SELECT
ROUND((out_time-in_time)*24*60*60) delta_seconds,
(CASE WHEN ROUND((out_time-in_time)*24*60*60)=30 THEN 'equal' else 'not equal' end) flag
FROM m

方法2：用EXTRACT直接提取秒数（推荐）

利用EXTRACT函数从日期差的时间间隔中直接提取秒数，结果为精确整数：

WITH m AS (
 SELECT to_date('2023-03-01 12:00:00','yyyy-mm-dd hh24:mi:ss') in_time,
        to_date('2023-03-01 12:00:30','yyyy-mm-dd hh24:mi:ss') out_time
 FROM dual
)
SELECT
EXTRACT(SECOND FROM (out_time - in_time) DAY TO SECOND) delta_seconds,
(CASE WHEN EXTRACT(SECOND FROM (out_time - in_time) DAY TO SECOND)=30 THEN 'equal' else 'not equal' end) flag
FROM m

方法3：通过NUMTODSINTERVAL转换为时间间隔

先将日期差转换为INTERVAL DAY TO SECOND类型，再提取秒数：

WITH m AS (
 SELECT to_date('2023-03-01 12:00:00','yyyy-mm-dd hh24:mi:ss') in_time,
        to_date('2023-03-01 12:00:30','yyyy-mm-dd hh24:mi:ss') out_time
 FROM dual
)
SELECT
EXTRACT(SECOND FROM NUMTODSINTERVAL(out_time - in_time, 'DAY')) delta_seconds,
(CASE WHEN EXTRACT(SECOND FROM NUMTODSINTERVAL(out_time - in_time, 'DAY'))=30 THEN 'equal' else 'not equal' end) flag
FROM m

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