/*
** $Id: pkg_time.sql,v 1.3 2011/06/07 15:23:43 dieter Exp $
**
** Copyright (C) 2001 topIT Informationstechnologie GmbH
** Copyright (C) 2006 independIT Integrative Technologies GmbH
*/

CREATE OR REPLACE PACKAGE pkg_time
AS
	function gmt2local(p_gmt number)
		return date;
	function gmt2date (p_gmt number)
		return date;
	FUNCTION tz_convert_gmt_to_local (dt date)
		RETURN DATE;
	function tz_convert (dt date, from_tz varchar2, to_tz varchar2)
		return date;
END;
/
show errors

CREATE OR REPLACE PACKAGE BODY pkg_time
AS
	gv_gmt_offset NUMBER(1) := 1;

	-- memory cache for daylight start and end of a year
	TYPE t_tdate IS TABLE OF DATE INDEX BY BINARY_INTEGER;
	v_tdlstart t_tdate;
	v_tdlend   t_tdate;

	v_dlstart DATE;
	v_dlend   DATE;

	/*
		New version using oracles timestamp with timezone feature
	*/
	FUNCTION gmt2local(p_gmt NUMBER)
		RETURN DATE
	AS
	BEGIN
		RETURN tz_convert_gmt_to_local(gmt2date(p_gmt));
	END;

	/* 
		Old Style Version with home grown daylight saving calculation

	FUNCTION gmt2local(p_gmt NUMBER)
		RETURN DATE
	AS
		v_ts   NUMBER(38) := p_gmt;
		v_d    DATE;
		v_year BINARY_INTEGER;
	BEGIN
		IF p_gmt IS NULL
		THEN
			RETURN NULL;
		END IF;
		-- if unmask info bit in timestamp if present
		IF v_ts > 1125899906842624
		THEN
			v_ts := MOD (v_ts, 1125899906842624);
		END IF;
		-- date is calculated from milliseconds after 1.1.1970 with
		-- offset from gmt = 1 hour for MET
		v_d := to_date('01.01.1970','DD.MM.YYYY') +
			(v_ts / (60 * 60 * 24 * 1000) + gv_gmt_offset/24);
		-- now we calculate daylight saving
		-- get year from above date
		v_year := TO_NUMBER(TO_CHAR(TRUNC(v_d, 'YEAR'),'YYYY'));
		BEGIN
			-- get daylight start and end from cache
			v_dlstart := v_tdlstart(v_year);
			v_dlend   := v_tdlend(v_year);
		EXCEPTION
			WHEN NO_DATA_FOUND
			THEN
			DECLARE
				v_cyear CHAR(4);
				v_sunday VARCHAR2(7) := TRIM(TO_CHAR(TO_DATE('19630407','YYYYMMDD'), 'DAY'));
			BEGIN
				-- find start and end date for this year
				v_cyear   := TRIM(TO_CHAR(v_year));
				-- start on last sunday of march at 2:00
				v_dlstart := NEXT_DAY(
						TO_DATE(v_cyear||'0401','YYYYMMDD') - 8,
						v_sunday) + 2/24;
				-- end on last sunday of october at 2:00
				v_dlend   := NEXT_DAY(
						TO_DATE(v_cyear||'1101','YYYYMMDD') - 8,
						v_sunday) + 3/24;
				-- save start and end of daylight saving time in cache
				v_tdlstart(v_year) := v_dlstart;
				v_tdlend(v_year)   := v_dlend;
			END;
		END;
		-- if in daylight syving range, add 1 hour for daylight saving time
		IF v_d BETWEEN v_dlstart AND v_dlend
		THEN
			v_d := v_d + 1/24;
		END IF;
		RETURN v_d;
	END;
	*/

	FUNCTION gmt2date(p_gmt NUMBER)
		RETURN DATE
	AS
		v_ts   NUMBER(38) := p_gmt;
	BEGIN
		IF p_gmt IS NULL
		THEN
			RETURN NULL;
		END IF;
		-- if unmask info bit in timestamp if present
		IF v_ts > 1125899906842624
		THEN
			v_ts := MOD (v_ts, 1125899906842624);
		END IF;
		-- date is calculated from milliseconds after 1.1.1970
		RETURN to_date('01.01.1970','DD.MM.YYYY') + (v_ts / (60 * 60 * 24 * 1000));
	END;

	FUNCTION tz_convert_gmt_to_local (dt date)
		RETURN DATE
	AS
	BEGIN
		IF dt IS NULL 
		THEN 
			RETURN NULL; 
		END IF;
		RETURN TO_DATE ( 
			   TO_CHAR ( 
			     TO_TIMESTAMP_TZ ( 
			       TO_CHAR ( dt , 'YYYYMMDDHH24MISS' ) || 'GMT'
                           , 'YYYYMMDDHH24MISSTZR' 
                       ) AT LOCAL 
			   , 'YYYYMMDDHH24MISS'
			   )
		       , 'YYYYMMDDHH24MISS' 
		       );
	END; 

	FUNCTION tz_convert (dt date, from_tz varchar2, to_tz varchar2)
		RETURN DATE
	AS
	BEGIN
		RETURN TO_DATE ( 
			   TO_CHAR ( 
			     TO_TIMESTAMP_TZ ( 
			       TO_CHAR ( dt , 'YYYYMMDDHH24MISS' ) || from_tz
                         , 'YYYYMMDDHH24MISSTZR' 
                         ) AT TIME ZONE to_tz
			   , 'YYYYMMDDHH24MISS'
			   )
		       , 'YYYYMMDDHH24MISS' 
		       );
	END; 
END;
/
show errors
