Oracle equaivalent of java System.currentTimeMilli

Question

I want to be able to store the current time in milliseconds in an Oracle number field. How do I do this via a query?

select systimestamp from dual; 

returns the actual timestamp. Is there anyway that I can convert this into milliseconds the same way Java's System.currentTimeMillis() does?

Answer 1

this link helps for all languages currentmillis.com for oracle:

SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL

Answer 2

The Java function returns the number of milliseconds which have elapsed since a fixed moment in time. That time is midnight on the first day of 1970 UTC, i.e. the start of Unix clock time.

The following function does the same for PL/SQL. It subtracts the current timestamp from the starting point (where ms=1). It extracts the various time components and turns them into seconds. Finally it multiplies everything by 1000 to get the value in milliseconds:

create or replace function current_millisecs 
    return number 
is
    base_point constant timestamp := to_timestamp('01-JAN-1970 00:00:00.000');
    now constant timestamp := systimestamp AT TIME ZONE 'UTC' ;
begin
    return (
                  ((extract(day    from (now-base_point)))*86400)
                + ((extract(hour   from (now-base_point)))*3600)
                + ((extract(minute from (now-base_point)))*60)
                + ((extract(second from (now-base_point))))
           ) * 1000;
end;
/

If you have Java enabled in the database you may find it simpler to create a Java Stored Procedure instead:

create or replace function currentTimeMillis return number as
language java name 'java.lang.System.currentTimeMillis() return java.lang.Integer';
/

Comparison of the two approaches:

SQL> select currentTimeMillis as JAVA
  2         , current_millisecs as PLSQL
  3         , currentTimeMillis - current_millisecs as DIFF
  4  from dual
  5  /

      JAVA      PLSQL       DIFF
---------- ---------- ----------
1.2738E+12 1.2738E+12          0

SQL>

(My thanks go to Simon Nickerson, who spotted the typo in the previous version of my PL/SQL function which produced an anomalous result.)

---

Incidentally, if you are only interested in time to the nearest centisecond, Oracle has a built-in for that: DBMS_UTILITY.GET_TIME().

Answer 3

• DB timezone agnostic
• with milliseconds
• works in XE

    function current_time_ms
        return number
    is
        out_result number;
    begin
        select extract(day from(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 
            + to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3'))
        into out_result
        from dual;
        return out_result;
    end current_time_ms;

Answer 4

Try this -

select extract(day from(sys_extract_utc(systimestamp AT TIME ZONE 'GMT') - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp AT TIME ZONE 'GMT'), 'SSSSSFF3')) from dual;