Converting Oracle Dates to UNIX Epoch Dates

Cleaning off my whiteboard, and I want to write these down somewhere before I erase them...

To convert from a UNIX date to Oracle date

TO_DATE('1970-01-01', 'YYYY-MM-DD') + UNIX_date_in_millis / 86400000 = Oracle_date

And to convert the other way

Oracle_date - TO_DATE('1970-01-01', 'YYYY-MM-DD') \* 86400000 = UNIX_date_in_millis

I don't remember why I needed this so long ago.

Comments:

I think that will get you the day. The second and msec entries 10/13 digit can be checked with the following package.

CREATE PACKAGE OUR_TIME AS
c_epoch_tz CONSTANT timestamp with time zone := to_timestamp_tz('1970-01-01 0:00', 'YYYY-MM-DD TZH:TZM');
c_epoch CONSTANT timestamp := to_timestamp('1970-01-01', 'YYYY-MM-DD');
--
FUNCTION t_s(time_in timestamp with time zone) RETURN int DETERMINISTIC PARALLEL_ENABLE;
FUNCTION t_ms(time_in timestamp with time zone) RETURN number DETERMINISTIC PARALLEL_ENABLE;
FUNCTION t_snz(time_in timestamp) RETURN int DETERMINISTIC PARALLEL_ENABLE;
FUNCTION t_msnz(time_in timestamp) RETURN number DETERMINISTIC PARALLEL_ENABLE;
--
FUNCTION s_t(time_in int) RETURN timestamp DETERMINISTIC PARALLEL_ENABLE;
FUNCTION ms_t(time_in number) RETURN timestamp DETERMINISTIC PARALLEL_ENABLE;
--
FUNCTION s_tz(time_in int) RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE;
FUNCTION ms_tz(time_in number) RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE;
--
FUNCTION epoch RETURN timestamp DETERMINISTIC PARALLEL_ENABLE;
FUNCTION epoch_tz RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE;
--
END our_time;
/

CREATE PACKAGE BODY OUR_TIME AS
FUNCTION t_s(time_in timestamp with time zone) RETURN int DETERMINISTIC PARALLEL_ENABLE AS
diff interval day(9) to second(9) := time_in at time zone 'GMT' - c_epoch_tz;
BEGIN
RETURN extract(day from diff) \* 86400
+ extract(hour from diff) \* 3600
+ extract(minute from diff) \* 60
+ extract(second from diff)
;
END;
FUNCTION t_ms(time_in timestamp with time zone) RETURN number DETERMINISTIC PARALLEL_ENABLE AS
diff interval day(9) to second(9) := time_in at time zone 'GMT' - c_epoch_tz;
BEGIN
RETURN extract(day from diff) \* 86400000
+ extract(hour from diff) \* 3600000
+ extract(minute from diff) \* 60000
+ extract(second from diff) \* 1000
;
END;
FUNCTION t_snz(time_in timestamp) RETURN int DETERMINISTIC PARALLEL_ENABLE AS
diff interval day(9) to second(9) := time_in - c_epoch;
BEGIN
RETURN extract(day from diff) \* 86400
+ extract(hour from diff) \* 3600
+ extract(minute from diff) \* 60
+ extract(second from diff)
;
END;
FUNCTION t_msnz(time_in timestamp) RETURN number DETERMINISTIC PARALLEL_ENABLE AS
diff interval day(9) to second(9) := time_in - c_epoch;
BEGIN
RETURN extract(day from diff) \* 86400000
+ extract(hour from diff) \* 3600000
+ extract(minute from diff) \* 60000
+ extract(second from diff) \* 1000
;
END;
FUNCTION s_t(time_in int) RETURN timestamp DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch + numtodsinterval(time_in, 'second');
END;
FUNCTION ms_t(time_in number) RETURN timestamp DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch + numtodsinterval(time_in/1000, 'second');
END;
FUNCTION s_tz(time_in int) RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch_tz + numtodsinterval(time_in, 'second');
END;
FUNCTION ms_tz(time_in number) RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch_tz + numtodsinterval(time_in/1000, 'second');
END;
FUNCTION epoch RETURN timestamp DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch;
END;
FUNCTION epoch_tz RETURN timestamp with time zone DETERMINISTIC PARALLEL_ENABLE AS
BEGIN
RETURN c_epoch_tz;
END;
END our_time;
/

create or replace public synonym OUR_TIME for SYS.OUR_TIME;
grant execute on OUR_TIME to PUBLIC;

col unix_t for 99999999999999999
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'Dy Mon dd HH24:MI:SS yyyy TZH:TZM';

select dbtimezone from dual;
select sessiontimezone from dual;
select localtimestamp from dual;
select systimestamp from dual;
select current_timestamp from dual;
select our_time.t_ms(systimestamp) unix_t from dual;
select our_time.t_msnz(systimestamp) unix_t from dual;
select our_time.t_s(systimestamp) unix_t from dual;
select our_time.ms_tz(1239986117826) from dual;
select our_time.s_tz(1239986117) from dual;

Posted by Jim on April 17, 2009 at 02:47 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

mock

Search

Top Tags
Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Bookmarks
Blogroll

No bookmarks in folder