Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> to_tz function

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.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US