Re: to_tz function

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 11 Jan 2007 22:27:23 -0800
Message-ID: <1168583243.815448.119520@s34g2000cwa.googlegroups.com>

rthu..._at_yahoo.com wrote:
> 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;
> /

How about using built-in AT TIME ZONE clause?

SQL> select timestamp '2007-05-11 08:00:00 NZ-CHAT' AT TIME ZONE 'Europe/Moscow' ts_in_my_tz from dual;

TS_IN_MY_TZ

---

10.05.07 23:15:00,000000000 EUROPE/MOSCOW SQL> select timestamp '2007-05-11 08:00:00 NZ-CHAT' AT TIME ZONE 'Australia/Sydney' ts_in_my_tz from dual;

TS_IN_MY_TZ

---

11.05.07 05:15:00,000000000 AUSTRALIA/SYDNEY Automatically takes daylight savings into account if you use zone names rather than offsets, using built-in time zones dictionary. To convert a timestamp to the session time zone (set with ALTER SESSION SET TIME_ZONE,) use AT LOCAL instead of AT TIME ZONE 'Zone/Name'.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Fri Jan 12 2007 - 00:27:23 CST