How do I convert sysdate to milisegundos directly into Oracle?
I need to give the same result of 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();
Based on this answer in SOen , you must first 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 doing without needing the function, maybe this will work:
select ((systimestamp - date '1970-01-01') * 60 * 60 * 24) as t from dual;
However, such as explained in this other answer , this does not take into account the time zone and daylight saving time.