15

Could you provide implementation of stored function to get current systimestamp as milliseconds.
Something I can use like

select current_time_ms from dual;

and get the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Thanks.

Mike
  • 20,010
  • 25
  • 97
  • 140
  • 1
    Look at http://stackoverflow.com/questions/5881235/oracle-current-timestamp-to-seconds-conversion. There is shorter and faster answer. – Vadzim Dec 21 '11 at 08:50

7 Answers7

21
  • 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;
Mike
  • 20,010
  • 25
  • 97
  • 140
  • 1
    If session timezone happens to be different from system's, this can return value off by 24hours when called around midnight (before of after depending how the two timezones correlate). You can easily test this with `ALTER SESSION SET TIME_ZONE = '+ 08:00'` or similar. Fix seems easy: replace `from(systimestamp...` with `from(sys_extract_utc(systimestamp)...` in your expression. – Piotr Findeisen Jan 23 '15 at 17:28
  • I wrote "pure Java" version (see my answer). To my astonishment, in very simple testing, the Java version was much faster than the PL/SQL one. – Piotr Findeisen Jan 23 '15 at 23:27
  • I wonder if Oracle gets the system clock twice here? (by calling `systimestamp` twice). In this case you may get inconsistencies between the first and the second part, in particular around midnight. – peterh Aug 25 '22 at 06:54
9

The best thing I know of is:

select extract(day    from (systimestamp - timestamp '1970-01-01 00:00:00')) * 86400000
     + extract(hour   from (systimestamp - timestamp '1970-01-01 00:00:00')) * 3600000
     + extract(minute from (systimestamp - timestamp '1970-01-01 00:00:00')) * 60000
     + extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) * 1000 unix_time
from dual;

I'm not quite sure what requirements you have regarding time zone. You might need to make minor adjustments for that.

Codo
  • 75,595
  • 17
  • 168
  • 206
5

Adding to @Mykhaylo Adamovych answer (which looks correct!) here goes a more straightforward approach using oracle Java support (i.e. not in XE and not in AWS RDS). Less portable (in case you care), but seemed faster in my testing.

CREATE or replace FUNCTION current_java_timestamp RETURN number
AS LANGUAGE JAVA NAME 'java.lang.System.currentTimeMillis() return java.lang.Long';
/
Piotr Findeisen
  • 19,480
  • 2
  • 52
  • 82
5
SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual
Floern
  • 33,559
  • 24
  • 104
  • 119
L0uL0u
  • 61
  • 1
  • 1
1

Below code gives the difference in milliseconds:

with t as (select systimestamp - to_timestamp(sysdate ) diff from dual)
select extract(day from diff) * 24 * 3600000+
   extract(hour from diff) * 3600000+
   extract(minute from diff) * 60000 +
   extract(second from diff) * 1000  
    dif
from t

For conversion of milliseconds to Hours, Minutes, seconds, modify and use below query as appropriate:

with t as (select systimestamp - to_timestamp(sysdate ) diff from dual)
select extract(day from diff) * 24 * 3600000+
   extract(hour from diff) * 3600000+
   extract(minute from diff) * 60000 +
   extract(second from diff) * 1000  
    dif,
    (to_char (to_date(round(( extract(day from diff) * 24 * 3600000+
   extract(hour from diff) * 3600000+
   extract(minute from diff) * 60000 +
   extract(second from diff) * 1000)/1000), 'SSSSS' ), 'HH24"Hrs" MI"Min" SS"Sec"'))  timeval
from t
ngrashia
  • 9,869
  • 5
  • 43
  • 58
1

AFAIK, there is no direct way for achieving this (other than manually writing a long-winded SQL function).

Why do you need this specifically?

You could use a stored Java function and then use the System.getCurrentMillis() that Java provides to return you a value in Milliseconds from 1.1.1970 to now.

Ollie
  • 17,058
  • 7
  • 48
  • 59
  • -1, @Codo's answer is direct, and less long-winded that writing a java function and pl/sql wrapper for it. – DCookie Aug 22 '11 at 14:58
  • Agreed, if you can do it in SQL then you should. – Ollie Aug 22 '11 at 16:38
  • 1
    This is the right way to do it. The other answers have subtle bugs, because the don't take into account things like leap years or leap seconds. – Pablo Dec 17 '14 at 09:18
  • You don't have to write Java code just to call `System.getCurrentMillis()` since you can call the latter directly. See my answer. – Piotr Findeisen Jan 23 '15 at 23:25
0

I've posted here some methods to convert timestamp to nanoseconds and nanoseconds to timestamp. These methods are not affected by time zones and have a nanosecond precision.

You just need to adjust it to get milliseconds instead of nanoseconds.

SELECT (EXTRACT(DAY FROM (
    SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    SYSTIMESTAMP --Replace line with desired timestamp
)) *  1000 AS MILLIS FROM DUAL;

MILLIS
1598434427263.027
Danton Estevam Pinto
  • 47
  • 2