ORACLE timezone summary

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 26 Sep 2006 09:44:49 -0700
Message-ID: <1159289089.051544.23260@b28g2000cwb.googlegroups.com>

Hi all.
Since I deem all available info and documentation on this matter rather lacking, I have started a short ORACLE+timezone tutorial for myself and my fellow developers.
Fell free to comment on my ramblings.

cheers,
Martin

(sorry, the linebreaks will be f'ed up I fear)

---

/*

---

Understanding ORACLE time zone information

---

*/

--

• Current time information of client and server
  --
  select sysdate, -- Server system time as <DATE> datatype systimestamp, -- Server system time as <TIMESTAMP WITH TIME ZONE> datatype dbtimezone, -- Database timezone ... this will *only* affect the implicit conversion to <TIMESTAMP WITH TIME LOCAL ZONE> sessiontimezone, -- Timezone of the client/session current_timestamp, -- Server system time converted to the clients timezone; datatype is <TIMESTAMP WITH TIME ZONE> localtimestamp lts -- current_timestamp without the timezone information; datatype is <TIMESTAMP> from dual;

--

• Change client timezone:
  --
  /* This will affect the values of sessiontimezone, current_timestamp and localtimestamp. */ ALTER SESSION SET TIME_ZONE='+01:00' -- The session timezone will be set to timezone UTC+1 hour ALTER SESSION SET TIME_ZONE='CET' -- The session will be set to Central European Time, which corresponds to UTC+1 in winter and UTC+2 in summer

/*
Note: The session timezone hopefully is correctly set by the client environment, but it probably won't hurt to check that your oracle client layer does this correctly on your OS. */

--

• Note on the systimestamp function:
  --
  /* The SysTimeStamp and Current_TimeStamp will always returns the timezone information in the '+/-HH:MM' format, not in the named format! This means, that by examining SysTimeStamp you can determine what UTC offset the database server has *at the moment* but you *cannot* determine which timezone the database server is in (which TZ the server OS is set to). */

--

• Timenzones - named vs. timed
  --
  
• Result = 2006-07-03 12:00:00 +02:00 SELECT TO_CHAR( TO_TIMESTAMP_TZ('2006-07-03 12:00:00 CET', 'YYYY-MM-DD HH24:MI:SS TZR') , 'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual;
• Result = 2006-07-03 12:00:00 CET CEST SELECT TO_CHAR( TO_TIMESTAMP_TZ('2006-07-03 12:00:00 CET', 'YYYY-MM-DD HH24:MI:SS TZR') , 'YYYY-MM-DD HH24:MI:SS TZR TZD') from dual;
• Result = 2006-01-01 12:00:00 CET CET SELECT TO_CHAR( TO_TIMESTAMP_TZ('2006-01-01 12:00:00 CET', 'YYYY-MM-DD HH24:MI:SS TZR') , 'YYYY-MM-DD HH24:MI:SS TZR TZD') from dual;
• Result = 2006-01-01 12:00:00 +02:00 SELECT TO_CHAR( TO_TIMESTAMP_TZ('2006-01-01 12:00:00 +02:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') , 'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual;
• (!) Result = 2006-01-01 12:00:00 +02:00 SELECT TO_CHAR( TO_TIMESTAMP_TZ('2006-01-01 12:00:00 +02:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') , 'YYYY-MM-DD HH24:MI:SS TZR TZD') from dual;

/*
Note how in the last example the result does *not* contain timezone information. It cannot
since no such information is present in the input data. If the SysTimeStamp is converted to a string with 'TZR TZD' it *still* will only display
the timezone offset as '+/-HH:MM'
*/

SELECT TO_CHAR(

       SysTimeStamp
	   , 'YYYY-MM-DD HH24:MI:SS TZR TZD') from dual;

--

• The v$timezone_names view
  --
  /* This view stores a number of timezones with their summertime sub-timezones How this values relate to a +/-hours offset is dependent on the actual time for which a timezone is queried. This also means, that the 'TZR' format will always yield a valid time, while the 'TZR TZD' format is *invalid* for certain wall-clock (or UTC) times. E.g.: Taking the <CET CEST> timezone, which is Central European Summertime the time <2006-Jul-01 12:00> is a valid time, *but* <2006-Dez-01 12:00> is *not* a valid time in <CET CEST> since at this time the correct timezone info would be <CET CET> The following Function and view should help highlight these dependencies.

[ Oracle9i Database Reference Release 2 (9.2) ] >>> TZNAME VARCHAR2(64) Time zone region (for example, US/Pacific) >>> TZABBREV VARCHAR2(64) Corresponding daylight abbreviation (for example, PDT) */

CREATE OR REPLACE FUNCTION TIMEZONE_DISPLAY(DATEPART IN VARCHAR2, TZ_MAIN_NAME IN VARCHAR2, TZ_SUMMER_ABBREV IN VARCHAR2) RETURN VARCHAR2
IS

	invalid_timezone EXCEPTION;
	PRAGMA EXCEPTION_INIT (invalid_timezone, -1857);
BEGIN
	RETURN TO_CHAR(
	               TO_TIMESTAMP_TZ(DATEPART||' '||TZ_MAIN_NAME||'
'||TZ_SUMMER_ABBREV, 'YYYY-MM-DD HH24:MI:SS TZR TZD')
				  , 'TZH:TZM');

EXCEPTION
WHEN invalid_timezone THEN

        RETURN NULL;
END; SELECT * FROM (
SELECT tzname TZ_MAIN_NAME, tzabbrev TZ_SUMMER_ABBREV, TO_CHAR(TO_TIMESTAMP_TZ('2006-01-01 12:00:00 '||tzname, 'YYYY-MM-DD HH24:MI:SS TZR'), 'TZH:TZM') AS TZ_OFFSET_REGION_WINTER, TO_CHAR(TO_TIMESTAMP_TZ('2006-07-01 12:00:00 '||tzname, 'YYYY-MM-DD HH24:MI:SS TZR'), 'TZH:TZM') AS TZ_OFFSET_REGION_SUMMER, TIMEZONE_DISPLAY('2006-01-01 12:00:00', tzname, tzabbrev) AS TZ_OFFSET_COMBINED_WINTER,
TIMEZONE_DISPLAY('2006-07-01 12:00:00', tzname, tzabbrev) AS TZ_OFFSET_COMBINED_SUMMER
FROM v$timezone_names )
/* where TZ_OFFSET_COMBINED_WINTER IS NOT NULL

   OR TZ_OFFSET_COMBINED_SUMMER IS NOT NULL */ order by TZ_MAIN_NAME

/*
Note in the SELECT, how some entries TZNAME+TZABBREV in v$timezone_names do not yield a valid time for summer AND winter I really have no clue what this could mean. :)

Note also that some entries in v$timezone_names yield valid offsets (always the same) for summer and winter. */

--

• Invalid Timezones
  --
  /* The first example will work, the CET CEST timezone is valid for 206-07-01. The second example will give us a "ORA-01857 not a valid timezone" exception. */

select TO_TIMESTAMP_TZ('2006-07-01 12:00:00 CET CEST', 'YYYY-MM-DD HH24:MI:SS TZR TZD')
from dual; -- OK

select TO_TIMESTAMP_TZ('2006-01-01 12:00:00 CET CEST', 'YYYY-MMM-DD HH24:MI:SS TZR TZD')
from dual; -- ORA-01857!

--

• Bottom line
  --
  /* I ask: "What's the point of the 'TZR TZD' formatting when ORACLE will only let you create valid combinations anyway?" The answer I do not know. I also do not know why there are timezones in v$timezone_names that seemingly are invalid. I suspect some timezones may have changed in the past - so if I were to query 1976 I'd some entries that are invalid for 2006 would be valid)

Anyway - as I see it, you *never* need to supply the 'TZR TZD' form when putting data into ORACLE, except if you want to validate that the timezone info on the data is correct.

What you would do in the client is, to ensure the sessiontimezone is set correctly vs. the time zone on the db server and then insert the data accordingly.

Oh, and if you use the <TIMESTAMP WITH TIME *LOCAL* ZONE> datatype you *also* have to ensure that the dbtimezone is set correctly and correctly means that it's the same timezone as the OS the server runs on because otherwise the values will not match!

I'm sure I've forgotten a lot of things, but lets leave it here, shall we? :-)
*/

---

Received on Tue Sep 26 2006 - 11:44:49 CDT