-- $Id: time.sql,v 1.4 2004/03/04 02:26:25 jnasby Exp $

CREATE OR REPLACE PACKAGE 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 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 time;
/
show err
GRANT EXECUTE ON time TO public;

CREATE OR REPLACE PACKAGE BODY 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 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 time;
/
show err

-- vi: noexpandtab sw=4 ts=4