Oracle-TIMESTAMP&DATE互转

-- SWTICH UNIX TIMESTAMP TO DATE

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 ;