How do I get the current number of milliseconds since 1970-01-01.
I still have no access to oracle.com so I created a new account to answer this question.
Either you use the difference between timestamp ‘1970-01-01 00:00:00 +00:00’ and current_timestamp, or you can use java, which is more portable.
YMMV
create function epoch return number as language java name
'java.lang.System.currentTimeMillis() return int';
/
select epoch from dual;
EPOCH
----------------
1214562599878
I needed to do this recently so i did,
select ((to_date(to_char(sys_extract_utc(systimestamp),'yyyymmddhh24miss'),'yyyymmddhh24miss')
- to_date('01-JAN-1970','dd-mon-yyyy'))*86400 * 1000)
from dual
/
it was a plsql block and milliseconds from epoch at UTC were needed to construct a JSON message to be sent via utl_tcp.
R
select
extract(day from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone ‘UTC’ -timestamp ‘1970-01-01 00:00:00 +00:00’))*86400+
extract(hour from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone ‘UTC’ -timestamp ‘1970-01-01 00:00:00 +00:00’))*3600+
extract(minute from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone ‘UTC’ -timestamp ‘1970-01-01 00:00:00 +00:00’))*60+
extract(second from (from_tz(cast(current_timestamp as timestamp), sessiontimezone) at time zone ‘UTC’ -timestamp ‘1970-01-01 00:00:00 +00:00’)) n
from dual