在 Oracle 中将本地日期时间(带时区)转换为 Unix 时间戳 [英] Convert local datetime (with time zone) to a Unix timestamp in Oracle

问题描述

我目前有一个 SQL 查询，它从我们数据库中的 Unix TIMESTAMP 列返回正确的本地 DATETIME.

I currently have a SQL query that returns the correct local DATETIME from a Unix TIMESTAMP column in our DB.

以下是使用 1539961967000 的特定 TIMESTAMP 的示例:

Here is an example using a specific 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

我很难反转此查询以返回以本地 DATETIME 开头的 Unix TIMESTAMP.

I am having a hard time reversing this query to return a Unix TIMESTAMP starting with a local DATETIME.

有没有人遇到过这种情况?

Has anyone ever encountered this before?

推荐答案

主要问题是 Oracle 有两种方法(至少)将秒数转换为每天到秒的间隔 - 使用函数或对区间文字进行简单的算术运算 - 但没有直接的方法来做相反的事情.

The main issue is that Oracle has two ways (at least) to convert a number of seconds to an interval day-to-second - either with a function or with a simple arithmetic operation on an interval literal - but no direct way to do the reverse.

在下面的两个查询中，首先我展示了如何将 UNIX 时间戳(自 Epoch 以来的毫秒数)转换为 Oracle 时间戳，而不会丢失毫秒.(请参阅我在您的问题下的评论，我指出您的方法将丢失毫秒.)然后我将展示如何反转该过程.

In the two queries below, first I show how to convert a UNIX timestamp (in milliseconds since the Epoch) to an Oracle timestamp, without losing milliseconds. (See my comment under your Question, where I point out that your method will lose milliseconds.) Then I show how to reverse the process.

和你一样，我忽略了Unix 时间戳"忽略闰秒导致的UTC 时间戳"和Unix 时间戳"之间的区别.您的企业必须确定这是否重要.

Like you, I ignore the difference between "timestamp at UTC" and "Unix timestamp" caused by "Unix timestamp" ignoring leap seconds. Your business must determine whether that is important.

Unix 时间戳到带时区的 Oracle 时间戳(保留毫秒):

with
  inputs (unix_timestamp) as (
    select 1539961967186 from dual
  )
select from_tz(timestamp '1970-01-01 00:00:00' 
               + interval '1' second * (unix_timestamp/1000), 'UTC')
                   at time zone 'America/Denver' as oracle_ts_with_timezone
from   inputs
;

ORACLE_TS_WITH_TIMEZONE               
--------------------------------------
2018-10-19 09:12:47.186 America/Denver

带时区的 Oracle 时间戳到 Unix 时间戳(保留毫秒):

with
  sample_data (oracle_ts_with_timezone) as (
    select to_timestamp_tz('2018-10-19 09:12:47.186 America/Denver', 
                           'yyyy-mm-dd hh24:mi:ss.ff tzr')           from dual
  )
select ( extract(second from ts) 
         + (trunc(ts, 'mi') - date '1970-01-01') * (24 * 60 * 60)
       ) * 1000 as unix_timestamp
from   ( select cast(oracle_ts_with_timezone at time zone 'UTC' 
                     as timestamp) as ts
         from   sample_data
       )
;

  UNIX_TIMESTAMP
----------------
   1539961967186

这篇关于在 Oracle 中将本地日期时间(带时区)转换为 Unix 时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！