
DECLARE
	sourceoffset varchar;
	destoffset   varchar;
BEGIN
	sourceoffset := CASE sourcezone
		WHEN 'AST' THEN 'UTC+4'
		WHEN 'ADT' THEN 'UTC+3'
		WHEN 'BST' THEN 'UTC+11'
		WHEN 'BDT' THEN 'UTC+10'
		WHEN 'CST' THEN 'UTC+6'
		WHEN 'CDT' THEN 'UTC+5'
		WHEN 'EST' THEN 'UTC+5'
		WHEN 'EDT' THEN 'UTC+4'
		WHEN 'GMT' THEN 'UTC'
		WHEN 'HST' THEN 'UTC+10'
		WHEN 'HDT' THEN 'UTC+9'
		WHEN 'MST' THEN 'UTC+7'
		WHEN 'MDT' THEN 'UTC+6'
		WHEN 'NST' THEN 'UTC+3:30'
		WHEN 'PST' THEN 'UTC+8'
		WHEN 'PDT' THEN 'UTC+7'
		WHEN 'YST' THEN 'UTC+9'
		WHEN 'YDT' THEN 'UTC+8' END;
	IF sourceoffset IS NULL THEN
		RAISE EXCEPTION 'timezone % not recognized for new_time(), Use SQL standard AT TIME ZONE construct which supports all timezones', sourcezone;
	END IF;
	destoffset := CASE destzone
		WHEN 'AST' THEN 'UTC+4'
		WHEN 'ADT' THEN 'UTC+3'
		WHEN 'BST' THEN 'UTC+11'
		WHEN 'BDT' THEN 'UTC+10'
		WHEN 'CST' THEN 'UTC+6'
		WHEN 'CDT' THEN 'UTC+5'
		WHEN 'EST' THEN 'UTC+5'
		WHEN 'EDT' THEN 'UTC+4'
		WHEN 'GMT' THEN 'UTC'
		WHEN 'HST' THEN 'UTC+10'
		WHEN 'HDT' THEN 'UTC+9'
		WHEN 'MST' THEN 'UTC+7'
		WHEN 'MDT' THEN 'UTC+6'
		WHEN 'NST' THEN 'UTC+3:30'
		WHEN 'PST' THEN 'UTC+8'
		WHEN 'PDT' THEN 'UTC+7'
		WHEN 'YST' THEN 'UTC+9'
		WHEN 'YDT' THEN 'UTC+8' END;
	IF destoffset IS NULL THEN
		RAISE EXCEPTION 'timezone % not recognized for new_time(). Use SQL standard AT TIME ZONE construct which supports all timezones', destzone;
	END IF;
	RETURN ((datein AT TIME ZONE sourceoffset)  AT TIME ZONE destoffset);
END;
