Issue with replicating 'timestamp with timezone' data type in a PostgreSQL to PostgreSQL environment.

[khchoy]

Hi  All,

When replicating from PostgreSQL to PostgreSQL, there seems to be an issue with replicating the 'timestamp with timezone' data type. The problem is that the data is always reflected in the target as UTC time values. Is this an unsupported behavior?

In the case of Full Loading, when retrieving data, it appears that the data is forcefully converted to UTC, similar to logs. For CDC, since the stored values are already in UTC, they are retrieved as is.

00037280: 2023-08-22T16:48:43:8506 [SOURCE_UNLOAD ]V: Execute: 'SELECT "key_col","col_timestamp_1", cast("col_timestamptz" AT TIME ZONE 'UTC' as timestamp without time zone) FROM "test"."tb_timstamp"' (ar_odbc_stmt.c:2741)

Is it possible to control these processes with a specific parameter toggle (ON/OFF)? Also, could you please explain the purpose of the internal parameter 'calculateTimestampWithZone'?

Regards,

Kwang Ho

[sureshkumar]

Hello @khchoy 

PostgreSQL has timestamp datatype that has no time zone information and timestamptz datatype that always store date time information in UTC value, both are in 8 bytes.

Refer:

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-timestamp/https://www.postgresql.org/docs/current/datatype-datetime.html

Regards,
Suresh

[DesmondWOO · Accepted solution]

Hi @khchoy ,

The parameter "calculateTimestampWithZone" serves the purpose of resolving an issue related to incorrect replication of timestamptz values when a connection between the source database is disrupted. By default, this parameter is enabled.

As you mentioned in the post, the timestamptz value is cast as AT TIME ZONE 'UTC' in the SQL statement. It is hard-coded and cannot be modified or worked around. Please submit your request in the Ideation.

Regards,
Desmond