My Oracle Support Banner

Goldengate Converting Timestamp(0) With Timezone Incorrectly. (Doc ID 2660784.1)

Last updated on MAY 26, 2020

Applies to:

Oracle GoldenGate - Version 18.1.0.0.0 and later
Information in this document applies to any platform.

Symptoms

Replicat applying incorrect date for column type TIMESTAMP WITH TIME ZONE

 

Table Structure....

CREATE TABLE <SCHEMA>.<TABLE>
( "KEY" NUMBER(38,0) NOT NULL ENABLE PRIMARY KEY,
  "COL_TMS_TZ" TIMESTAMP (0) WITH TIME ZONE,                  <===== Issue in replicating
  "TMS" TIMESTAMP);

DML...

insert into <SCHEMA>.<TABLE> (KEY,COL_TMS_TZ,TMS) 

          values(1,to_timestamp_tz ( '01-JAN-1901 01.00.00 AM AMERICA/LOS_ANGELES' , 'DD-MON-YYYY HH.MI.SSXFF AM TZR' ),to_date('01-JAN-1901 01.00.00','DD-MON-YYYY HH.MI.SS'));

 

 

from source...
SQL> select KEY,COL_TMS_TZ,TMS from <SCHEMA>.<TABLE>;

KEY  COL_TMS_TZ                                                          TMS
----  -----------------------------------------------------    -------------------------------
  1   01-JAN-01 01.00.00 AM AMERICA/LOS_ANGELES     01-JAN-01 01.00.00.000000 AM

 

from target...
SQL> select KEY,COL_TMS_TZ,TMS from <SCHEMA>.<TABLE>;

KEY COL_TMS_TZ                                                        TMS
---- -----------------------------------------------------   -------------------------------

  1  06-FEB-37 07.28.16 AM AMERICA/LOS_ANGELES   01-JAN-01   01.00.00.000000 AM <=== PROBLEM with COL_TMS_TZ value

 

In this case the issue happened with ogg v12.2.x and ogg v18.x



Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.