Oracle 将 UNIX 时间戳转换为带时区的时间戳 [英] Oracle conversion of UNIX timestamp to timestamp with time zone

查看：99 发布时间：2021/9/7 20:42:11 oracle timestamp unix-timestamp timestamp-with-timezone

本文介绍了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');

这篇关于Oracle 将 UNIX 时间戳转换为带时区的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文