Re: ORACLE timezone summary

DA Morgan wrote:
> Martin T. wrote:
> > 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
>
> Have you looked at the demos in Morgan's Library at www.psoug.org?
>
> If you have recommendations or improvements I'd be happy to post them
> with attribution.
> --

I had looked into it shortly.

What I miss there, is what I could not find anywhere else, and that is an explanation on how the named timezones relate to the UTC offset ORACLE will calculate. (I think Oracle "just" implements some common timezone standard, so probably it would be described in this standard, but I couldn't find a good documentation on this.)

Anyway. Seeing how your library is layed out, I think the following short infos may be easily added there:

• Look up the UTC offset of a named timezone (for a given date&time): 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;
• Lookup UTC offset for a named timezone at the currect server system time: SELECT TZ_OFFSET('CET') FROM DUAL;
• The DBTIMEZONE will *only* affect the <TIMESTAMP WITH LOCAL TIME ZONE> datatype, and not the time reported by SysTimeStamp (And you only can change the DBTIMEZONE when your database does not contain a column with local tz.)
• [ Oracle9i Database Globalization Support Guide; under: Time Zone Parameters for Databases]
• "The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. (...)"

ALTER DATABASE set time_zone='CET';
SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP, DBTIMEZONE, SESSIONTIMEZONE FROM DUAL;
ALTER DATABASE set time_zone='CST';
SELECT SYSTIMESTAMP, CURRENT_TIMESTAMP, DBTIMEZONE, SESSIONTIMEZONE FROM DUAL; I hope this is of any use to you. (If you add it, no attribution is needed.)

best regards,
Martin Received on Wed Sep 27 2006 - 02:09:25 CDT