![]() Previous |
![]() Next |
TIMESTAMP_TZ
corresponds to the SQL TIMESTAMP WITH TIMEZONE
data type. It is a variant of TIMESTAMP
that includes a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for collecting and evaluating date information across geographic regions.
Oracle time zone data is derived from the public domain information available at ftp://elsie.nci.nih.gov/pub/
. Oracle time zone data may not reflect the most recent data available at this site.
The TIMESTAMP_TZ
data type is a variant of TIMESTAMP
that includes a time zone offset. When you specify TIMESTAMP_TZ
as a literal, the fractional seconds precision value can be any number of digits up to 9. For example:
TIMESTAMP '1997-01-31 09:26:56.66 +02:00'
Two TIMESTAMP_TZ values are considered identical if they represent the same instant in UTC, regardless of the TIME
ZONE
offsets stored in the data. For example,
TIMESTAMP '1999-04-15 8:00:00 -8:00'
equals
TIMESTAMP '1999-04-15 11:00:00 -5:00'
That is, 8:00 a.m. Pacific Standard Time equals 11:00 a.m. Eastern Standard Time.
You can replace the UTC offset with the TZR
(time zone region) format element. For example, the following example has the same value as the preceding example:
TIMESTAMP '1999-04-15 8:00:00 US/Pacific'
To eliminate the ambiguity of boundary cases when the daylight saving time switches, use both the TZR
and a corresponding TZD
format element. The following example ensures that the preceding example returns a daylight saving time value:
TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'
You can also express the time zone offset using a datetime expression.
If you do not add the TZD
format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME
session parameter set to TRUE
. If that parameter is set to FALSE
, then Oracle interprets the ambiguous datetime as standard time in the specified region.