将Unix Epoch转换为日期/时间戳，并在Oracle SQL中使用时区

Question

我正在尝试编写SQL表达式，该表达式将使Unix时期（自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列：从时间戳开始，该值关闭1小时，而不是使用日期
2. 第2列：在美国创建此时间戳/LOS_ANGELES的值与UTC中相同的值
3. 第4列：将时间戳转换为美国/new_york的价值与美国/los_angeles
4. 第6列：提取的小时是第3列的UTC小时
5. 第7列：时区偏移无法作为全部提取，导致未知类型

这样做的正确的Oracle SQL是什么？

Answer 1

我提出了一个或多或少的kackish解决方案 - 使用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（）工作，但是SQL工具（甚至基于JDBC的工具）将输出位于本地时区域的字符串；因此，也应该使用to_char（）转换为字符串输出。

Answer 2

1. 我运行了您的代码，在我的计算机上显示了第1列的22:30 pm，不确定您为什么得到23:30 pm（实际上我不相信您）。确实，您可能想知道为什么第2列中没有得到相同的答案（22:30 PM）；这是因为命名时区包括用于节省日光时间的调整，这不是UTC约定的一部分（因此第一列不受影响）。

2. 和3.将秒添加到时间戳将为您提供新的时间戳。秒数是您脑海中" Unix时代"的事实没有意义。SQL应该如何知道您想将UTC转换为您想要的任何时区？

3. 请参阅我对1的答案。提取的小时为22，这是正确的。

4. 在我的机器上，时区偏移量在-4小时时被提取得很好。

您使用哪种版本的Oracle？

Answer 3

我在这里发布了一些方法，可以将纳米秒转换为时间戳和时间戳为纳秒。这些方法不受时区影响，并且具有纳秒精度。

您需要将时区放置在看到'-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