将Unix纪元转换为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列：在America/los_Angeles中创建此时间戳产生的值与UTC中的值相同
3. 第4列：将时间戳转换为America/New_York生成的值与America/Los_Angeles中的值相同
4. 第6列：提取的小时是第3列的UTC小时
5. 第7列：不能将时区偏移量提取为全部，导致类型未知

Answer 1

我想出了一个或多或少有点古怪的解决方案--使用TO_CHAR()函数先将其转换为string，然后将string转换为Number。

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()转换为字符串输出。