Oracle 将 UNIX 时间戳转换为带时区的时间戳

问题描述：

尝试将 UNIX 时间戳转换为带时区的 Oracle 时间戳.期望看到不同的输出，但日期时间部分是相同的.

Trying to convert UNIX timestamp to Oracle timestamp with timezone. Expecting to see different output, however datetime part is the same.

什么是拧?

select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('EST') from dual;

输出:27-FEB-08 09.21.56.656000000 AM -05:00

Output: 27-FEB-08 09.21.56.656000000 AM -05:00

select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('PST') from dual;

输出:27-FEB-08 09.21.56.656000000 AM -07:00

Output: 27-FEB-08 09.21.56.656000000 AM -07:00

为什么日期/时间部分相同?Oracle 不做调整吗?

How come date / time part is the same? Does not Oracle do adjustment ?

答

Unix 时间戳来自 1970-01-01 00:00:00 UTC.当您只执行 timestamp '1970-01-01 00:00:00' 时，Oracle 会采用您的本地时区！

Unix timestamp is from 1970-01-01 00:00:00 UTC. When you just do timestamp '1970-01-01 00:00:00' Oracle takes your local time zone!

你必须这样做:

(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' +     
    numtodsinterval(1204104116656/1000,'second')) AT time zone tz_offset('PST');