oracle – convert date to unix timestamp and vice versa

Convert date to unix timestamp (milliseconds):

(what a pain!!! incredible there is no native oracle function in 11g)

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;

Warning: this function does not take into account timezone delay

Convert unix timestamp (milliseconds)  to date:

select TO_DATE('1970-01-01', 'YYYY-MM-DD') + <unix_timestamp> / 86400000 from dual;

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...