Skip to content
February 1, 2011 / doganay

DATE_TO_UNIX_TIME and UNIX_TIME_TO_DATE CONVERSION FUNCTIONS

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

 

Hi,

 

You can use these functions to convert date to unix time and reverse..

 

create or replace FUNCTION date_to_unix
(
in_date IN DATE
)
RETURN NUMBER
IS
BEGIN
RETURN (in_date -TO_DATE(‘19700101′,’yyyymmdd’))*86400 – TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600;
END;

———–

CREATE OR REPLACE
FUNCTION unix_to_date(unixts IN PLS_INTEGER) RETURN DATE IS
unix_epoch DATE := TO_DATE(‘19700101000000′,’YYYYMMDDHH24MISS’);
max_ts PLS_INTEGER := 2145916799; — 2938-12-31 23:59:59
min_ts PLS_INTEGER := -2114380800; — 1903-01-01 00:00:00
oracle_date DATE;

BEGIN
IF unixts> max_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
‘UNIX timestamp too large for 32 bit limit’
);
ELSIF unixts <min_ts THEN
RAISE_APPLICATION_ERROR(
-20901,
'UNIX timestamp too small for 32 bit limit' );
ELSE
oracle_date := unix_epoch + NUMTODSINTERVAL(unixts, 'SECOND');
END IF;
RETURN (oracle_date);
END;
/

try and pray 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: