Dealing with time zones

The possible presence of time zone information makes the use of TO_TIMESTAMP_TZ and TO_TIMESTAMP_LTZ more complex than the TO_DATE and TO_TIMESTAMP functions. You may specify time zone information in any of the following ways:

· As a positive or negative displacement of some number of hours and minutes from UTC time (sometimes referred to inappropriately as GMT or Greenwich Mean Time); for example, -5:00 is equivalent to U.S. Eastern Standard Time. Displacements must fall into the range -12:59 and +13:59.

· Using a time zone region name such as US/Eastern, US/Pacific, and so forth.

· Using a combination of time zone region name and abbreviation, as in US/Eastern EDT.

Let's look at some examples. We'll begin with a simple example that leaves off time zone information entirely:

TO_TIMESTAMP_TZ ('123188 083015.50', 'MMDDYY HHMISS.FF')

The date and time in this example work out to be 31-Dec-1998 at 15 1/2 seconds past 8:30 AM. Because no time zone is specified, Oracle will assume that your current session time zone applies. Every Oracle session is associated with a time zone, which you can check using the following query:

SQL> SELECT SESSIONTIMEZONE FROM DUAL; SESSIONTIMEZONE----------------------------------------05:00

The notation -05:00 indicates that your session time zone is five hours and zero minutes behind UTC. Such a difference equates to Eastern Standard Time in the United States. A displacement of +10:00, on the other hand, equates to Australian Eastern Standard Time.

Next, let's represent the time zone using a displacement of hours and minutes from UTC. Note the use of the TZH and TZM to denote the location of the hour and minute displacements in the input string:

TO_TIMESTAMP_TZ ('123188 083015.50 -5:00', 'MMDDYY HHMISS.FF TZH:TZM')

In this example, the datetime value is interpreted as being an Eastern Standard Time value (regardless of your session time zone).

The next example shows the time zone being specified using a time zone region name. The following example specifies America/Detroit, which is equivalent to Eastern Time in the United States. Note the use of TZR in the format mask to designate where the time zone region name appears in the input string.

TO_TIMESTAMP_TZ ('27-Oct-2002 01:30:00.00 America/Detroit', 'dd-Mon-yyyy hh:mi:ssxff TZR')

This example is interesting in that it representsEastern Time, not Eastern Standard Time. The difference is that "Eastern Time" can refer to either Eastern Standard Time or Eastern Daylight Time, depending on whether daylight savings time is in effect. And it might be in effect! We've carefully crafted this example to make it ambiguous. 27-Oct-2002 is the date on which Eastern Daylight Time ends, and at 2:00 AM time rolls back to 1:00 AM. So on that date, 1:30 AM actually comes around twice! The first time it's 1:30 AM Eastern Daylight Time, and the second time it's 1:30 AM Eastern Standard Time. So what time is it, really, when we say it's 1:30 AM on 27-Oct-2002?

The time zone region name alone doesn't distinguish between standard time and daylight savings time. To remove the ambiguity, you also must specify a time zone abbreviation, which we've done in the next example. Note the addition of TZD in the format mask to mark the location of the abbreviation:

TO_TIMESTAMP_TZ ('27-Oct-2002 01:30:00.00 America/Detroit EDT', 'dd-Mon-yyyy hh:mi:ssxff TZR TZD')

To avoid ambiguity, we recommend that you either specify a time zone offset using hours and minutes (as in -5:00) or use a combination of region name and time zone abbreviation. If you use region name alone and there's ambiguity with respect to daylight savings time, Oracle will resolve the ambiguity by assuming that standard time applies.

If you set the session parameter ERROR_ON_OVERLAP_TIME to TRUE, Oracle will give you an error whenever you specify an ambiguous time.

You can get a complete list of the time zone region names and time zone abbreviations that Oracle supports by querying the V$TIMEZONE_NAMES view. Any database user can access that view. When you query it, notice that time zone abbreviations are not unique (see the sidebar).

A Time Zone Standard? As important as time zones are, you would think there would be some sort of international standard specifying their names and abbreviations. Well, there isn't one. Not only are time zone abbreviations not standardized, but there is also some duplication. For example, EST is used in the U.S. for Eastern Standard Time, and also in Australia for Eastern Standard Time, and we assure you that the two Eastern Standard Times are not at all the same! This is why the TO_TIMESTAMP functions do not allow you to specify time zone using the abbreviation alone. Because there is no time zone standard, you might as well ask the source of all those time zone region names in V$TIMEZONE_NAMES. Oracle's source for that information can be found at ftp://elsie.nci.nih.gov/pub. Look especially at the file named tzdata_2002c.tar.gz.