Home > Blogroll, java, sql > Epoch

Epoch

June 27th, 2008
Goto comments Leave a comment

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

Blogroll, java, sql

  1. Raj Jamadagni
    June 27th, 2008 at 19:37 | #1
    Reply | Quote

    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

  2. Sokrates
    June 30th, 2008 at 16:40 | #2
    Reply | Quote

    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

  1. No trackbacks yet.