Path: news.f.de.plusline.net!news-fra1.dfn.de!news-lei1.dfn.de!newsfeed.freenet.de!newspeer1.nwr.nac.net!border2.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!s34g2000cwa.googlegroups.com!not-for-mail
From: rthumsi@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: to_tz function
Date: 11 Jan 2007 18:02:08 -0800
Organization: http://groups.google.com
Lines: 60
Message-ID: <1168567328.779596.308300@s34g2000cwa.googlegroups.com>
NNTP-Posting-Host: 143.127.3.10
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1168567338 14859 127.0.0.1 (12 Jan 2007 02:02:18 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 12 Jan 2007 02:02:18 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: s34g2000cwa.googlegroups.com; posting-host=143.127.3.10;
   posting-account=YEq5Zg0AAADO1Ots0_4NVcoFNxN0-RG0
Xref: news.f.de.plusline.net comp.databases.oracle.server:192230

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;
/