to_tz function

From: <rthumsi_at_yahoo.com>
Date: 11 Jan 2007 18:02:08 -0800
Message-ID: <1168567328.779596.308300@s34g2000cwa.googlegroups.com>

Here is a function to return time in a different time zone. Though new_time function exists this one works on any time zone regions and corrects for daylight saving time.
To test this function try something like:

select to_tz(timestamp '2007-05-11 08:00:00 NZ-CHAT') from dual;

CREATE OR REPLACE FUNCTION to_tz(p_tstz TIMESTAMP WITH TIME ZONE, p_new_tzr VARCHAR2 DEFAULT 'PST')
RETURN TIMESTAMP WITH TIME ZONE
AS

v_utc TIMESTAMP;
v_curr_utc TIMESTAMP;
v_new_ts TIMESTAMP;
v_new_tstz TIMESTAMP WITH TIME ZONE;
v_tzoffset VARCHAR2(20);
v_ds_tzoffset INTERVAL DAY TO SECOND;
v_ds_30min INTERVAL DAY TO SECOND DEFAULT '0 00:30:00';
v_max_loop_cnt INTEGER DEFAULT 2 * 24;
v_curr_tstz TIMESTAMP WITH TIME ZONE;
v_found BOOLEAN DEFAULT FALSE;

BEGIN

    v_utc := SYS_EXTRACT_UTC(p_tstz);
    v_tzoffset := TZ_OFFSET(p_new_tzr);
    v_ds_tzoffset := TO_DSINTERVAL('0 ' || SUBSTR(v_tzoffset,2,5) ||

':00');

    IF SUBSTR(v_tzoffset,1,1) = '+' THEN

        v_new_ts := v_utc + v_ds_tzoffset;
    ELSE
        v_new_ts := v_utc - v_ds_tzoffset;
    END IF;
    v_new_tstz := FROM_TZ(v_new_ts, p_new_tzr);     v_curr_tstz := v_new_tstz;
-- Now, apply the correction for Daylight Saving Time if needed.

• 1 hr difference is most common between ST and DT
• there have been instances of 30 minute differences
• as well as 2 hr differences.
• So let us try 30 minute intervals
• to figure out the actual time. FOR i in 1 .. v_max_loop_cnt LOOP v_curr_utc := SYS_EXTRACT_UTC(v_curr_tstz); IF v_curr_utc = v_utc THEN v_found := TRUE; EXIT; ELSIF v_curr_utc > v_utc THEN v_curr_tstz := v_curr_tstz - v_ds_30min; ELSE v_curr_tstz := v_curr_tstz + v_ds_30min; END IF; END LOOP; IF v_found THEN v_new_tstz := v_curr_tstz; ELSE v_new_tstz := NULL; END IF; return v_new_tstz; END to_tz; /

Received on Thu Jan 11 2007 - 20:02:08 CST