在Oracle SQL中将Unix纪元转换为带时区的日期/时间戳

xe55xuns 于 2022-11-03 发布在 Oracle

关注(0)|答案(3)|浏览(197)

我正在尝试编写需要一个unix时期的SQL表达式（自1970年1月1日以来的秒数）转换为特定时区的本地时间，并从中提取小时值。在研究了此时间的解决方案后，我仍然不确定如何处理时区。请注意，我尽量避免使用NEW_TIME（）函数，因为它只接受时区缩写词的有限子集，而不是完整的时区名称。

select 
(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + numtodsinterval(1464820200,'second')) as ts_utc,
(TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'America/Los_Angeles' + numtodsinterval(1464820200,'second')) as ts_la,
(DATE '1970-01-01' + numtodsinterval(1464820200,'second')) as date_utc,
FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York' as date_ny,
FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/Los_Angeles' as date_la, -- this value is correct
EXTRACT(hour from FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York') as hour,
EXTRACT(TIMEZONE_OFFSET from FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York') as tz_offset,
NEW_TIME((DATE '1970-01-01' + numtodsinterval(1464820200,'second')), 'GMT', 'EDT') as date_edt -- this value is correct
from dual;

结果是

TS_UTC  TS_LA   DATE_UTC    DATE_NY DATE_LA HOUR    TZ_OFFSET   DATE_EDT
2016-06-01 23:30:00.0   2016-06-01 23:30:00.0   2016-06-01 22:30:00.0   2016-06-01 15:30:00.0   2016-06-01 15:30:00.0   22  <UnknownType (-104)>    2016-06-01 18:30:00.0

结果有几个问题
1.第1列：从时间戳开始，该值比使用DATE时相差1小时
1.第2列：在美洲/洛杉矶创建此时间戳将产生与UTC中相同的值
1.第4列：将时间戳转换为America/New_约克会产生与America/Los_Angeles中相同的值
1.第6列：提取的小时是第3列的UTC小时
1.第7列：时区偏移量无法全部提取，导致类型未知
要做到这一点，正确的Oracle SQL是什么？

oracle

来源：https://stackoverflow.com/questions/38087705/converting-unix-epoch-to-date-timestamp-with-time-zone-in-oracle-sql

3条答案

按热度按时间

p4rjhz4m1#

我想出了一个多少有些古怪的解决方案--先使用TO_CHAR（）函数将其转换为字符串，然后再将字符串转换为数字。

TO_NUMBER(TO_CHAR(FROM_TZ(CAST(DATE '1970-01-01' + numtodsinterval(1464820200,'second') as TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'HH24')) as date_ny_int

FROM_TZ（）AT TIME ZONE也按预期工作，但SQL工具（甚至是基于JDBC的工具）将输出一个本地化为本地时区的字符串;因此也应该使用TO_CHAR（）转换为字符串输出。

赞(0）回复(0）举报 2022-11-03

yb3bgrhw2#

1.我运行了你的代码，在我的机器上，它在第一列显示22：30 PM，不知道为什么你会得到23：30 PM（实际上我不相信你）。这是因为命名时区包括夏令时的调整，而夏令时不是UTC约定的一部分（因此第一列不受影响）。

3.在时间戳中添加秒数将给予一个新的时间戳。在您的脑海中，秒数是一个“UNIX纪元”的事实没有任何意义; SQL如何知道您要将UTC转换为您想要的时区？
1.请参阅我对1的回答。提取的小时是22，这是正确的。
1.在我的机器上，时区偏移被提取得非常好，在-4小时。
您使用的Oracle是什么版本？

赞(0）回复(0）举报 2022-11-03

c86crjj03#

我已经发布了here一些方法来将纳秒转换为时间戳，并将时间戳转换为纳秒。这些方法不受时区的影响，并且具有纳秒精度。
您需要将时区设置为显示“-06：00”的位置。

SELECT (TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263 --Replace line with desired milliseconds
/ 1000, 'SECOND')) AT TIME ZONE '-06:00' AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263000000 UTC

赞(0）回复(0）举报 2022-11-03

我来回答

在Oracle SQL中将Unix纪元转换为带时区的日期/时间戳

3条答案

相关问题

热门标签

最新问答