Convert local datetime (with time zone) to a Unix timestamp in Oracle

Question

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

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;

which returns:

DATETIME
19-OCT-18 09.12.47.000000000 AM AMERICA/DENVER

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?

Answer 1

You can convert your timestamp with timezone to UTC, and then subtract the epoch from that:

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

which gives you an interval data type:

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

You can then extract the elements from that, and multiply each element by an appropriate factor to convert it to milliseconds (i.e. for days, 60*60*24*1000); and then add them together:

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

db<>fiddle

This preserves milliseconds too, if the starting timestamp has them (this converts from a 'Unix' time while preserving them):

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

then to convert back:

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

db<>fiddle

If your starting timestamp has greater precision than that then you'll need to truncate (or round/floor/ceil/cast) to avoid having a non-integer result; this version just truncates the extracted milliseconds part:

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

Without that truncation (or equivalent) you'd end up with 1539961967123.456789.

---

I'd forgotten about the leap seconds discrepancy; if you need/want to handle that, see this answer.

Answer 2

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.

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.

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 timestamp to Oracle timestamp with time zone (preserving milliseconds):

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 timestamp with time zone to Unix timestamp (preserving milliseconds):

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