Incorrect date display in the time zone column

[tellienne]

Hello!
We noticed that the connector records dates incorrectly if a special time zone is specified in the field.

How to play:

1. Create a column with the timestamptz type in Postgres.
2. In our example, the date in the column is stored with the time zone utc +3. For example - 2024-12-01 00:00:00.000 +0300
3. Start transfer
4. The transfer will save the date in this column with the time zone of the clickhouse server. By default UTC: 2024-11-30 21:00:00.
5. Set the clickhouse column to a time zone other than UTC:

MODIFY COLUMN IF EXISTS column_name Nullable(DateTime64(6,'Europe/Moscow'));

The date will be recalculated to +3 like 2024-12-01 00:00:00.000 +0300. this is the result we need

6. run a snapshot using ad hoc snapshot
7. As a result of the snapshot, the date will be oversaved as 2024-11-30 21:00:00.000 +0300.
   The time is actually saved in UTC, but with a non-UTC time zone

In Postgres we use dates with different time zones. One table can have columns with the timestamp type and store the date in utc, and columns with the timestamptz type, where the time zone is UTC+3.
In clickhouse we need to display dates in the same time zones as in Postgres

[subkanthi]

Hi @tellienne , we dont get the timezone information from debezium, you can use this config variable clickhouse.datetime.timezone to set the timezone for the connector.

https://github.com/Altinity/clickhouse-sink-connector/blob/develop/doc/timezone.md