首页 文章

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

提问于
浏览 次
0

我正在尝试编写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列：从时间戳开始，该值比使用DATE时减少1小时

  • 第2列：在America / Los_Angeles中创建此时间戳会产生与UTC相同的值

  • 第4列：将时间戳转换为America / New_York产生与America / Los_Angeles中相同的值

  • 第6列：提取的小时数是第3列的UTC小时

  • 第7列：无法将时区偏移全部提取，导致未知类型

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

oracle datetime timezone

2 回答

  • 0

    我想出了一个或多或少的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() AT TIME ZONE 按预期工作，但SQL工具（甚至基于JDBC的工具）将输出一个本地化时区的字符串;因此，也应该使用TO_CHAR（）转换为字符串输出 .

    回复于
  • 0

    • 我运行了你的代码，在我的机器上它显示在第1列的下午22:30，不知道为什么你得到23:30 PM（实际上我不相信你） . 实际上，您可能想知道为什么在第2栏中没有得到相同的答案（22:30 PM）;这是因为命名时区包括夏令时调整，这不是UTC约定的一部分（因此第一列不受影响） .

    • 和3.为时间戳添加秒数将为您提供新的时间戳 . 事实上，秒数是你脑海中的“UNIX时代”没有意义;如何知道你想知道你想从UTC转换/转换到你想要的任何时区？

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

    • 在我的机器上，时区偏移被完全精细地提取，在-4小时 .

    您使用的是哪个版本的Oracle？

    回复于

相关问题