-- SWTICH UNIX TIMESTAMP TO DATE
-- SWITCH DATE TO UNIX TIMESTAMP
CREATE
OR REPLACE FUNCTION unix_to_oracle(in_number NUMBER)RETURN DATE IS
BEGIN
RETURN(
TO_DATE('19700101', 'yyyymmdd')+ in_number / 86400 + TO_NUMBER(
SUBSTR(
TZ_OFFSET(SESSIONTIMEZONE),
1,
3
)
)/ 24
);
END unix_to_oracle ;
-- SWITCH DATE TO UNIX TIMESTAMP
CREATE
OR REPLACE FUNCTION oracle_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 oracle_to_unix ;