如何在PostgreSQL/EDB中复现Oracle日期差数值格式输出

差异原因 #

• Oracle中两个DATE类型直接做减法，返回浮点型数值：整数部分为两个日期间隔的完整自然日天数，小数部分为不足1天的时长按「1天=1单位」折算的数值，即1小时=1/24、1分钟=1/(2460)、1秒=1/(243600)。
• EDB/PostgreSQL中两个日期/时间类型直接做减法，默认返回interval类型的结构化时间间隔（格式为X days HH:MM:SS），不会自动转换为数值型天数，因此和Oracle输出存在差异。
• 之前尝试的写法错误原因是时间换算系数错误：1秒折算为天的数值是1/86400≈0.000011574，而非0.00001，系数偏差直接导致小数部分计算结果错误。

正确实现写法 #

通用PostgreSQL全版本兼容写法 #

两种写法逻辑完全等价，都可以输出和Oracle一致的浮点型天数差结果：

1. 按时间单位逐级折算

SELECT
  EXTRACT(DAY FROM diff)
  + EXTRACT(HOUR FROM diff)/24
  + EXTRACT(MINUTE FROM diff)/(24*60)
  + EXTRACT(SECOND FROM diff)/(24*3600) AS date_diff
FROM (
  SELECT SYSDATE - TO_DATE('30-12-1899','dd-mm-yyyy') AS diff
) t;

2. 基于秒级总时长折算（写法更简洁）

SELECT EXTRACT(EPOCH FROM (SYSDATE - TO_DATE('30-12-1899','dd-mm-yyyy')))/86400 AS date_diff;

逻辑说明：EXTRACT(EPOCH FROM 时间间隔)会返回间隔对应的总秒数，1天固定为86400秒，总秒数除以86400后得到的浮点数，和Oracle日期相减的计算逻辑、输出格式完全一致。

EDB环境简便写法 #

EDB作为Oracle兼容版PostgreSQL，直接开启Oracle兼容模式即可让日期减法行为和Oracle完全一致，不需要额外写换算逻辑：

-- 会话级开启兼容模式
SET compatible_mode TO oracle;
-- 直接执行和Oracle完全相同的查询即可得到一致结果
select SYSDATE - TO_DATE('30-12-1899','dd-mm-yyyy') from dual;

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