Oracle PL/SQL get current timestamp number 19

Question

Is there a way in Oralce PL/SQL to get the current timestamp as a NUMBER ?

Something like '1582185277302'

Answer 1

You can convert the current date/time to an epoch timestamp as follows:

 (sysdate - date '1970-01-01') * 60 * 60 * 24

This gives you the number of seconds since January 1st, 1970.

If you want the results in milliseconds, then:

(cast(systimestamp as date) - date '1970-01-01') * 24 * 60 * 60 * 1000 
+ mod( extract(second from systimestamp), 1) * 1000 

Answer 2

Unix timestamps are in the UTC time zone. If your timestamp is in the UTC time zone then you can use:

( TRUNC( your_timestamp, 'MI' ) - DATE '1970-01-01' ) * 24 * 60 * 60 * 1000
+ EXTRACT( SECOND FROM your_timestamp ) * 1000

If your timestamps have a time zone then:

( TRUNC( your_timestamp AT TIME ZONE 'UTC', 'MI' ) - DATE '1970-01-01' ) * 24 * 60 * 60 * 1000
+ EXTRACT( SECOND FROM your_timestamp ) * 1000

If you need to handle leap seconds then you can use this answer.

Answer 3

If you subtract a fixed timestamp from systimestamp you get an interval:

select systimestamp - timestamp '1970-01-01 00:00:00 UTC' as diff
from dual;

DIFF                  
----------------------
+18312 10:05:29.674905

You can then extract and manipulate the elements of that to get the epoch time, using an inline view or CTE to avoid repeating the interval generation:

with t (diff) as (
  select systimestamp - timestamp '1970-01-01 00:00:00 UTC'
  from dual
)
select trunc(1000 * (
    extract (day from diff) * 24 * 60 *60
      + extract (hour from diff) * 60 * 60
      + extract (minute from diff) * 60
      + extract (second from diff)
  )) as epoch
from t;

              EPOCH
-------------------
      1582193129829

Adding all the extracts together gives you the total number of seconds, including fractions; multiplying by 1000 gives you milliseconds but will still have fractions (depending on the precision your platform supports); truncating that gives you just milliseconds.

This takes the time zone into account (epoch times are supposed to count from UTC); but doesn't allow for leap seconds.

As a further demo, using a fixed time in an arbitrary time zone to get the result in your question:

with t (diff) as (
  select timestamp '2020-02-20 02:54:37.302789 America/New_York' - timestamp '1970-01-01 00:00:00 UTC'
  from dual
)
select trunc(1000 * (
    extract (day from diff) * 24 * 60 *60
      + extract (hour from diff) * 60 * 60
      + extract (minute from diff) * 60
      + extract (second from diff)
  )) as epoch
from t;

              EPOCH
-------------------
      1582185277302