Skip to content
Advertisement

Generating Total Time in Milliseconds in SQL Developer

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.

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement