Oracle SQL中转换Unix时间戳为日期结果异常的求助

算力阿峰

2026-7-9

Oracle中Unix时间戳转日期的正确实现

问题背景

从RESTful接口获取的JSON数据里，Paid_at字段值为Unix时间戳1696484073，尝试以下两种Oracle SQL转换方式均得到错误结果：

执行TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(nvl(l_payment_date,0), 'SECOND')，得到错误日期10/05/23；
先转字符串再用TO_DATE转换，得到错误日期03/02/15。

需要将该时间戳正确转换为日期并赋值给变量l_payment_date_trans。

正确解决方案

Unix时间戳的定义是从1970-01-01 00:00:00 UTC开始累计的秒数，错误的核心原因是忽略了时区差异，或者基准时间的格式定义不完整。以下是两种可靠的实现方式：

方式1：基于UTC时区的标准转换（推荐）

利用Oracle的时区函数确保转换不受数据库本地时区影响，同时支持任意目标时区：

DECLARE
  l_payment_date NUMBER := 1696484073;
  l_payment_date_trans TIMESTAMP WITH TIME ZONE;
BEGIN
  SELECT FROM_TZ(TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'UTC')
         + NUMTODSINTERVAL(l_payment_date, 'SECOND')
         AT TIME ZONE 'Asia/Shanghai' -- 替换为你的业务所需时区
  INTO l_payment_date_trans
  FROM DUAL;
  
  -- 如果需要转换为DATE类型，可添加CAST：
  -- l_payment_date_trans := CAST(上述结果 AS DATE);
END;

说明：FROM_TZ将基准时间绑定到UTC时区，加上秒数间隔后转换为目标时区，避免了本地时区偏移导致的错误。时间戳1696484073对应的UTC时间是2023-10-05 08:14:33，东8区时间为2023-10-05 16:14:33。

方式2：简化的日期计算（适合同步时区场景）

如果数据库本地时区与目标时区一致，可直接通过天数换算实现：

DECLARE
  l_payment_date NUMBER := 1696484073;
  l_payment_date_trans DATE;
BEGIN
  SELECT TO_DATE('1970-01-01', 'YYYY-MM-DD') + l_payment_date/86400
  INTO l_payment_date_trans
  FROM DUAL;
END;