CREATE OR REPLACE FUNCTION TO_UNIXTIME
( oratime IN timestamp DEFAULT SYSTIMESTAMP)
RETURN number IS
temp_days interval day(6) to second(6);
num_val number;
BEGIN
temp_days := (oratime - to_timestamp('01-JAN-1970','DD-MON-YYYY'));
num_val := to_number(extract(second from temp_days))*1000 +
to_number(extract(minute from temp_days)) * 60 *1000 +
to_number(extract(hour from temp_days)) * 60 * 60 *1000+
to_number(extract(day from temp_days)) * 60 * 60* 24 *1000;
RETURN num_val;
END;
/
SQL> alter session set nls_timestamp_format='mm/dd/yyyy hh:mi:ssxff am';
SQL> column ts form 999999999999999
SQL> select to_unixtime('7/28/2011 5:19:15.833000 PM') ts from dual;
TS
----------------
1311873555833
CREATE OR REPLACE FUNCTION FROM_UNIXTIME
( unixtime IN number DEFAULT 0)
RETURN varchar2 IS
oracle_date date;
BEGIN
oracle_date := to_date(trunc((unixtime/1000)/86400 + 2440588), 'J')+(((unixtime/1000)/86400+2440588)-(trunc((unixtime/1000)/86400 + 2440588)));
RETURN oracle_date;
END;
/
ALTER SESSION SET NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';
select from_unixtime(1316184485675) from dual;
SQL>
FROM_UNIXTIME(1316184485675)
--------------------------------------------------------------------------------
16-sep-2011 14:48:06
CREATE OR REPLACE FUNCTION FROM_UNIXTIME_ts
( unixtime IN number DEFAULT 0)
RETURN varchar2 IS
oracle_date timestamp(6);
BEGIN
oracle_date := to_timestamp('01-JAN-1970','DD-MON-YYYY')+numtodsinterval(unixtime/1000,'SECOND');
RETURN oracle_date;
END;
/
select from_unixtime_ts(1316184485675) from dual;
FROM_UNIXTIME_TS(1316184485675)
--------------------------------------------------------------------------------
16-SEP-11 02.48.05.675000 PM
