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.