Oracle不同时区时间戳相减的结果时区及Epoch秒问题

问题根源 #

你碰到的2小时偏差，本质是1970年1月1日当天，Asia/Jerusalem时区的UTC偏移是+2小时。如果你直接拿耶路撒冷时区的1970-01-01 00:00:00去减UTC的1970-01-01 00:00:00，实际上这两个时间点根本不是同一个物理时刻——耶路撒冷的0点对应UTC的1969-12-31 22:00:00，所以相减得到的是-2小时，转成秒就是-7200，自然会比你预期的Epoch值晚2小时。

正确转换步骤 #

要把Asia/Jerusalem时区的TIMESTAMP转成Epoch秒，关键是先对齐到UTC时间再计算：

方法1：用SYS_EXTRACT_UTC提取UTC时间计算 #

假设你的目标列是jerusalem_ts（类型为TIMESTAMP WITH TIME ZONE），SQL如下：

SELECT 
  (CAST(SYS_EXTRACT_UTC(jerusalem_ts) AS DATE) - DATE '1970-01-01') * 86400 
  + EXTRACT(SECOND FROM SYS_EXTRACT_UTC(jerusalem_ts))
AS epoch_seconds
FROM your_table;

• SYS_EXTRACT_UTC会直接把带时区的时间转成UTC时区的TIMESTAMP
• 转成DATE类型后和1970-01-01的DATE相减，得到天数差，乘以86400（一天的秒数）
• 加上秒部分的小数，得到精确到秒的Epoch值

方法2：直接计算跨时区时间差 #

Oracle会自动将不同时区的TIMESTAMP转换为同一物理时刻再计算间隔，所以也可以直接写：

SELECT 
  EXTRACT(DAY FROM time_diff) * 86400 
  + EXTRACT(HOUR FROM time_diff) * 3600 
  + EXTRACT(MINUTE FROM time_diff) * 60 
  + EXTRACT(SECOND FROM time_diff)
AS epoch_seconds
FROM (
  SELECT jerusalem_ts - TIMESTAMP '1970-01-01 00:00:00 UTC' AS time_diff
  FROM your_table
);

这里的time_diff是两个物理时刻的实际间隔，没有时区属性，提取各时间单位转成秒即可。

关于相减结果的时区归属 #

Oracle中两个TIMESTAMP WITH TIME ZONE相减的结果是INTERVAL DAY TO SECOND类型，这个间隔没有时区属性，它代表的是两个时间点之间经过的实际物理时长，和时区无关。你之前的偏差不是间隔的时区问题，而是没搞清楚耶路撒冷时区和UTC在1970年的时间对应关系。

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