Time zone conversion—TODIFFERENETTIMEZONE (Oracle)

Use this function when you want to convert a TIMESTAMP WITH TIME ZONE value into a different time zone. This function only applies to the CDC Replication Engine for Oracle databases.

Oracle databases have two different TIMESTAMP type data types:

TIMESTAMP WITH LOCAL TIME ZONE: Contains an implicit time zone, that of the database.
TIMESTAMP WITH TIME ZONE: Contains an explicit time zone.

Replication of TIMESTAMP WITH TIME ZONE is currently supported by the CDC Replication Engine for Oracle databases, while replication of TIMESTAMP WITH LOCAL TIME ZONE is not. The TODIFFERENETTIMEZONE expression supports both of these data types.

Syntax

%TODIFFERENTTIMEZONE (timestamp_column, time_zone [, sentinel_timestamp1 [, sentinel_timestamp2]...)

Parameters

timestamp_column: Specifies the name of a source column. You cannot specify a journal control field, an expression, or a column function as the input parameter. This parameter could be a TIMESTAMP WITH LOCAL TIME ZONE (if the expression is used as a derived column) or a TIMESTAMP WITH TIME ZONE column (if the expression is used either as a derived column or derived expression on the target).
time_zone: Specifies the destination time zone. The time zone can be specified as an offset in the “+|-HH:MM” format or as an IANA time zone name.
[, sentinel_timestamp1 [, sentinel_timestamp2]...: Specifies one or more timestamp literals for which no conversion is expected. The values should be specified using the following format: “YYYY-MM-DD HH24:MI:SS”. The comparison that is performed between the first parameter and the sentinel value is a literal comparison of the timestamp portion (for TIMESTAMP WITH TIME ZONE values the offset/region information is ignored).

Result data type

The derived expression returns a TIMESTAMP WITH TIME ZONE value.

Examples

Input value	Result
(TSMP, “-08:00”)	Values in the TSMP column are converted by using offset -08:00.
(TSP_COL, ”America/New_York”)	Values in the TSP_COL column are converted to America/New York time zone.
(TSP_COL, ”America/New_York”, “2015-01-01 00:00:00”)	Values in the TSP_COL column are converted to America/New York time zone unless the value of TSP_COL matches the sentinel timestamp 2015-01-01 00:00:00. If the value matches it is not converted. If the data type of TSP_COL is TIMESTAMP WITH LOCAL TIME ZONE, then the returned value is explicitly formatted by using the database time zone because the return value needs to have an explicit time zone.