Timestamp with Time Zone is a variant of the Timestamp data type that includes a time zone offset, TIME ZONE_HOUR: TIME ZONE_MINUTE, with or without daylight savings or time zone region name. The time zone offset is the difference, in hours and minutes, between the local time zone and UTC (Coordinated Universal Time).
For example, '16-JUN-08 07.01.25.376000 PM -06:00'. In the example, -06:00 is the time zone offset.
Another example is, '05-JUN-2008 07:01:25.376000 PM America/Los_Angeles'. In the example, America/Los_Angeles is the time zone region name.
When you import the Timestamp with Time Zone data type into the Developer tool, the associated transformation data type is timestampWithTZ.
timestampWithTZ has a precision of 36 and a scale of 9. Timestamp with Time Zone displacement value range is from -12:00 < UTC < +14:00.
The Data Integration Service can process Timestamp with Time Zone data type in Oracle and flat file data objects. Timestamp with Time Zone data type is applicable only for the data within the range of Aug 1947 to Dec 2040.
Timestamp with Time Zone data type does not support the following time zone regions in the Developer tool:
AFRICA_CAIRO
AFRICA_MONROVIA
EGYPT
AMERICA_MONTREAL
Timestamp with Time Zone data type uses the IANA standard for the Time Zone Database version 2015b. To avoid data corruption when using the Timestamp with Time Zone data type, ensure that the Oracle DST patch 24 is present on the Oracle server and client. When you enable data object caching, ensure that the Oracle DST patch 24 is present on the database server used for data object caching.
If the patch is not present, download the Oracle DST patch 24 from the Oracle website. To verify that the Oracle server uses the Oracle DST patch 24, run the following command in the command prompt at the Oracle server:
SELECT VERSION FROM v$timezone_file;
You can verify that the version appears as 24. You can also verify the version when you run the following command at the Oracle server: