Re: timezones, ui, user-friendly selection

Oleg,

I believe the package you referenced is no longer needed for 9i.

First of all, ideal storage for date/time with time zone preserved is TIMESTAMP WITH TIME ZONE, which was introduced in 9i. Second, the problem with choosing correct time zone abbreviation for a region is not trivial - there's no standard on time zone abbreviations and due to this they are sometimes ambiguous. For your example of US/Pacific region there are PST (Standard time), PDT (Daylight time), PWT (War Time, was valid only during the WWII) and LMT (no idea what it is, seems it's not in use anymore.) So when your user selects US/Pacific (or America/Los_Angeles, which is now its standard name), timezone abbreviation is also required to distiguish between standard and daylight time. Luckily, Oracle correctly recognizes when daylight savings are in effect, and can display the correct abbreviation with TZD format mask, so you have nothing to worry about here, except for boundary cases when daylight savings are switched (session parameter ERROR_ON_OVERLAP_TIME controls how Oracle treats boundary cases: if it's TRUE then Oracle will give you error for ambiguosly specified time, otherwise it will assume standard time.)

As of conversions between time zones, Oracle9i provides several conversion routines for the new TIMESTAMP data type. Those you are interested in are FROM_TZ and TO_TIMESTAMP_TZ. There are also new format masks for time zones (TZH, TZM, TZR and TZD, for UTC offset hours, minutes, region name and time zone abbreviation respectively.)

Ultimately, for being user-friendly with respect to time zones, you only need to provide them with region names as the only choice, and store all datetime data as TIMESTAMP WITH TIME ZONE to preserve user's time zone information. For each user you will also store her selected region name and use it with TZR mask to accept and display time the way the user enters and expects it. You may also want to look at session-modifiable TIME_ZONE parameter, which sets time zone for the session, so you can set it to user's region name once for the session with
ALTER SESSION SET TIME_ZONE = 'Region/Name' and forget about it - Oracle will handle time zone conversions automatically for you (except, as I said, very special boundary cases where you will either get assumed standard time or receive an error.)

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Oleg Paraschenko" <usenet_at_sein.sportwetten.spb.ru> wrote in message
news:70f723dc.0212252155.66a7ada3_at_posting.google.com...


> Hello!

>

> "Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message

news:<auc4mc$ie7$1_at_babylon.agtel.net>...


> > Which Oracle version is in use? With different versions you have different

> > set of choices for implementation.

>

>   Version is 9.0.1. Now we plan to use TZ library

> (http://www.weitz.de/timezone.html) to convert values of type "date"

> between timezones. But we like to hear other recommendations.

>

> --

> Oleg