在Oracle中将本地日期时间（带时区）转换为Unix时间戳

我目前有一个返回正确本地的 SQL 查询DATETIME从 UnixTIMESTAMP我们数据库中的列。

这是一个使用特定的示例TIMESTAMP of 1539961967000:

SELECT FROM_TZ(CAST(DATE '1970-01-01' + 1539961967000 * (1/24/60/60/1000) AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/Denver' DATETIME
FROM dual;

返回：

DATETIME
19-OCT-18 09.12.47.000000000 AM AMERICA/DENVER

我很难逆转这个查询以返回 UnixTIMESTAMP从当地人开始DATETIME.

以前有人遇到过这种情况吗？

您可以将带有时区的时间戳转换为 UTC，然后从中减去纪元：

select timestamp '2018-10-19 09:12:47.0 AMERICA/DENVER'
  - timestamp '1970-01-01 00:00:00.0 UTC' as diff
from dual;

这给你一个区间数据类型：

DIFF                  
----------------------
+17823 15:12:47.000000

然后，您可以从中提取元素，并将每个元素乘以适当的因子，将其转换为毫秒（即天数，60*60*24*1000）；然后将它们加在一起：

select extract(day from diff) * 86400000
  + extract(hour from diff) * 3600000
  + extract(minute from diff) * 60000
  + extract(second from diff) * 1000 as unixtime
from (
  select timestamp '2018-10-19 09:12:47.0 AMERICA/DENVER'
    - timestamp '1970-01-01 00:00:00.0 UTC' as diff
  from dual
);

            UNIXTIME
--------------------
       1539961967000

数据库小提琴

如果起始时间戳有毫秒，这也会保留毫秒（这会在保留它们的同时从“Unix”时间转换）：

select (timestamp '1970-01-01 00:00:00.0 UTC' + (1539961967567 * interval '0.001' second))
  at time zone 'America/Denver' as denver_time
from dual;

DENVER_TIME                                 
--------------------------------------------
2018-10-19 09:12:47.567000000 AMERICA/DENVER

然后转换回来：

select extract(day from diff) * 86400000
  + extract(hour from diff) * 3600000
  + extract(minute from diff) * 60000
  + extract(second from diff) * 1000 as unixtime
from (
  select timestamp '2018-10-19 09:12:47.567 AMERICA/DENVER'
    - timestamp '1970-01-01 00:00:00.0 UTC' as diff
  from dual
);

            UNIXTIME
--------------------
       1539961967567

数据库小提琴

如果您的起始时间戳比该精度更高，那么您需要截断（或舍入/下限/上限/转换）以避免出现非整数结果；这个版本只是截断提取的毫秒部分：

select diff,
  extract(day from diff) * 86400000
  + extract(hour from diff) * 3600000
  + extract(minute from diff) * 60000
  + trunc(extract(second from diff) * 1000) as unixtime
from (
  select timestamp '2018-10-19 09:12:47.123456789 AMERICA/DENVER'
    - timestamp '1970-01-01 00:00:00.0 UTC' as diff
  from dual
);

DIFF                                  UNIXTIME
------------------------- --------------------
+17823 15:12:47.123456789        1539961967123

如果没有那个截断（或等效的），你最终会得到1539961967123.456789.

我忘记了闰秒的差异；如果你需要/想要处理这个问题，看到这个答案.