How to convert sysdate to milliseconds in Oracle?

Question

How to convert sysdate for milisegundos directly on Oracle?

I need to give the same result as the code below in java, but directly in query:

String s=df.format(Calendar.getInstance());
java.util.Date parsedUtilDate = df.parse(s);  
java.sql.Timestamp timestamp = new java.sql.Timestamp(parsedUtilDate.getTime());
long timeInMilliSeconds = timestamp.getTime();

Answer 1

Based in that reply in Soen, first you must create this function:

create or replace function date_to_unix_ts( PDate in date ) return number is

   l_unix_ts number;

begin

   l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
   return l_unix_ts;

end;

Then you use it like this:

select date_to_unix_ts(systimestamp) from dual;

If you want to try it without needing the function, maybe it works:

select ((systimestamp - date '1970-01-01') * 60 * 60 * 24) as t from dual;

Meanwhile, such as explained in this other answer, this does not take into account the time zone and daylight saving time.