Generating Total Time in Milliseconds in SQL Developer

Question

I need to replicate the linux command "date +%s%3N" in SQL Developer. I have tried the below code sample but it returns with a different value. I have also done extensive searching Google with no luck.

select to_char((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) * 1000) unix_time
from dual;

The date +%s%3N command returns something like:

1475615656692870653

Whereas the above code sample returns something like:

1475594089419116

The date command returns a longer and larger number than the code sample even though it was run before the code sample. The ultimate solution would be a direct utility in Oracle if possible. If not, possibly invoking the date command within Oracle would work.

That's a helluva lot of extra math: why do you need to extract each individual time component, only to multiply it out into seconds? http://stackoverflow.com/questions/12105691/convert-timestamp-datatype-into-unix-timestamp-oracle all of what you have could be done with one single bit of a date "math". — Marc B, Oct 04 '16 at 21:25
What is the timezone of your database server? Unix time is based on `1970-01-01 00:00:00 UTC`! — Wernfried Domscheit, Oct 04 '16 at 21:29
You make `extract(second from ...) * 1000) * 1000`, i.e. Microseconds. Is that what you want? — Wernfried Domscheit, Oct 04 '16 at 21:33
@MarcB You make a fair point about the extra math. I took a look at what you linked and it looked promising, however, the time that is returned is far different. Me and my boss believe that it is returning time in seconds not milliseconds. Simply multiplying it by 1000 is not accurate enough for the web app we are working on. — Keagan Lidwell, Oct 04 '16 at 21:42
@WernfriedDomscheit I added the second * 1000 to temporarily remove some decimals that were appearing in the answer. — Keagan Lidwell, Oct 04 '16 at 21:43
Your answer is of the order of 1.47 * 10 ^ 15, which is approximately the time elapsed from 1970-01-01 in MICROseconds. Your formula suggests the same. The other number you show is in NANOseconds, nine digits after the decimal point. With that in mind, the difference is almost exactly 6 hours, the difference between GMT and CST. — , Oct 04 '16 at 22:56

score 0 · Answer 1 · answered Oct 05 '16 at 05:57

Try this one:

CREATE OR REPLACE FUNCTION GetUnixTime RETURN INTEGER IS
    dsInt INTERVAL DAY(9) TO SECOND;
    res NUMBER;
BEGIN

    dsInt := CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC';
    res:= EXTRACT(DAY FROM dsInt)*24*60*60 
        + EXTRACT(HOUR FROM dsInt)*60*60 
        + EXTRACT(MINUTE FROM dsInt)*60 
        + EXTRACT(SECOND FROM dsInt);
    RETURN ROUND(1000*res);
END GetUnixTime;

ROUND(1000*res) will return Unix time in Milliseconds, according to your question it is not clear whether you like to get Milliseconds, Microseconds or even Nanoseconds. But it is quite obvious how to change the result to desired value.

This function considers your local time zone and time zone of Unix epoch (which is always UTC)

If you don't like a function, you can write it at a query of course:

SELECT 
    ROUND(EXTRACT(DAY FROM CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC')*24*60*60 
        + EXTRACT(HOUR FROM CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC')*60*60 
        + EXTRACT(MINUTE FROM CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC')*60 
        + EXTRACT(SECOND FROM CURRENT_TIMESTAMP - TIMESTAMP '1970-01-01 00:00:00 UTC') 
    * 1000) AS unix_time
FROM dual;

score 0 · Accepted Answer · answered Oct 05 '16 at 15:48

I ended up going with using oscommands through the method described in this link here http://www.orafaq.com/scripts/plsql/oscmd.txt. The solutions below were a step in the right direction, however, other parts of the script we were making were running into issues. Using oscommands solved all of our issues. With the method mentioned in the link, I was simply able to type

l_time := oscomm('/bin/date +%s%3N');

to get the correct number.

Generating Total Time in Milliseconds in SQL Developer

2 Answers2