Oracle转PostgreSQL查询求助：时间转换SQL执行结果不一致

嘿，我来帮你搞定这个Oracle转PostgreSQL的问题！你的原查询是把epoch秒数（4304052）转换成可读日期格式，但在PostgreSQL里结果不一致，核心原因是两种数据库的日期运算逻辑和精度处理有差异，下面给你一步步拆解解决方案：

原Oracle查询的逻辑 #

先理清你这条Oracle语句的思路：

SELECT to_char(to_date('01011970','ddmmyyyy') + 1/24/60/60 * 4304052,'dd-mon-yyyy hh24:mi:ss') from dual;

1. 把字符串01011970转成起始日期（1970年1月1日）
2. 通过1/24/60/60计算出每秒对应的天数，再乘以总秒数得到要累加的天数
3. 最后把结果格式化为dd-mon-yyyy hh24:mi:ss的字符串

PostgreSQL的最优转换方案 #

PostgreSQL支持更直观且精度更高的时间间隔运算，完全可以规避浮点计算带来的误差，推荐用这种方式：

SELECT to_char('1970-01-01'::timestamp + 4304052 * interval '1 second', 'dd-mon-yyyy hh24:mi:ss');

为什么这个方案更可靠？ #

• 消除精度误差：原Oracle用浮点值计算天数，容易出现微小精度偏差；PostgreSQL直接用interval '1 second'累加秒数，完全基于时间间隔的精确计算，这是你之前结果不一致的主要原因。
• 简化语法：PostgreSQL不需要from dual（虽然写了也兼容，但省略更简洁），用::timestamp把字符串转成时间戳类型，比to_date更直观。
• 格式兼容与灵活调整：to_char的格式符dd-mon-yyyy hh24:mi:ss和Oracle基本一致，但如果你的数据库locale不是英文，mon会显示本地化的月份缩写（比如中文环境显示“二月”）。如果需要强制英文缩写，可以添加第三个参数指定语言：

  SELECT to_char('1970-01-01'::timestamp + 4304052 * interval '1 second', 'dd-mon-yyyy hh24:mi:ss', 'NLS_DATE_LANGUAGE=English');
  

贴近Oracle写法的备选方案 #

如果一定要保留“日期加天数”的逻辑，也可以这样写，但还是不推荐（毕竟存在浮点精度风险）：

SELECT to_char(to_date('01011970','ddmmyyyy') + 4304052 / (24 * 60 * 60), 'dd-mon-yyyy hh24:mi:ss');

用第一种interval的方式，你得到的结果会和Oracle完全一致，英文环境下为19-feb-1970 19:34:12。

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