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:

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:

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:

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.

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 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).

• ⇐ Назад
• 79
• 80
• 81
• 82
• 83
• 84
• 858687
• 88
• 89
• 90
• 91
• 92
• 93
• 94
• Далее ⇒