Datetime columns with non UTC TZ on source and target are not replicated properly

[aadant]

How to reproduce :

For example use the demo

https://github.com/Altinity/clickhouse-sink-connector/discussions/810

config.yml

database.connectionTimeZone: "America/Chicago"
clickhouse.datetime.timezone: "America/Chicago"

docker-compose.yml

for both MySQL and CH add the following 2 lines :

environment:
     - TZ=America/Chicago

on MySQL :

alter table employees add column hireDate DateTime null;
update employees set hireDate = now() where 1=1;
select * from employees;

on CH, notice that the DateTime is different (6 hours behind)

Please note that the test case works fine with Timestamp :

alter table employees  drop column hireDate;
alter table employees add column hireDate Timestamp null;
update employees set hireDate = now() where 1=1;
select * from employees;

If you only have DateTime, you can use this for now :

database.connectionTimeZone: "UTC"
clickhouse.datetime.timezone: "UTC"

[aadant]

This bug will affect schemas with tables with DateTime and Timestamp columns which ... happens.

[aadant]

@subkanthi any progress ?

[subkanthi]

mysql> CREATE TABLE `temporal_types_DATETIME1` (                                             `Type` varchar(50) NOT NULL,                                             `Minimum_Value` datetime(1) NOT NULL,
                                  `Mid_Value` datetime(1) NOT NULL,                                             `Maximum_Value` datetime(1) NOT NULL,                                             `Null_Value` datetime(1) DEFAULT NULL,                                             PRIMARY KEY (`Type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `temporal_types_DATETIME1` VALUES ('DATETIME(1)','1000-01-01 00:00:00.0','2022-09-29 01:48:25.1','9999-12-31 23:59:59.9',NULL);
Query OK, 1 row affected (0.00 sec)

6c1171c3b7a :) select * from temporal_types_DATETIME1;

SELECT *
FROM temporal_types_DATETIME1

Query id: 6d8df463-88a4-49d4-824f-778f72b46085

┌─Type────────┬─────────Minimum_Value─┬─────────────Mid_Value─┬─────────Maximum_Value─┬─Null_Value─┬────────────_version─┬─is_deleted─┐
│ DATETIME(1) │ 1900-01-01 00:00:00.0 │ 2022-09-29 01:48:25.1 │ 2299-12-31 23:59:59.0 │       ᴺᵁᴸᴸ │ 1900198620167143484 │          0 │
└─────────────┴───────────────────────┴───────────────────────┴───────────────────────┴────────────┴─────────────────────┴────────────┘

1 row in set. Elapsed: 0.002 sec. 

mysql> alter table temporal_types_DATETIME1 add column New_Value datetime(1);

ALTER TABLE sbtest.temporal_types_DATETIME1 ADD COLUMN New_Value Nullable(DateTime64(1,'UTC'))

if clickhouse.datetime.timezone is set to America\Chicago then the ALTER table will be translated as

ALTER TABLE sbtest.temporal_types_DATETIME1 ADD COLUMN New_Value Nullable(DateTime64(1,'America/Chicago'))

INSERT INTO `temporal_types_DATETIME1` VALUES ('DATETIME(1)','1000-01-01 00:00:00.0','2022-09-29 01:48:25.1','9999-12-31 23:59:59.9',NULL, '2022-09-29 01:48:25.1');

[aadant]

@subkanthi i am surprised you managed to replicate the DateTime, can you check if the now() return the same rough time.

this is important for the repro :

environment:
     - TZ=America/Chicago

[subkanthi]

mysql> show global variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CDT | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)

mysql> select @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+

Debezium seems to use the time_zone in MySQL and in docker containers the timezone is set to UTC by default. The environment variable TZ sets the timezone of container.

I have no name!@f146dfa44554:/$ date
Mon Mar 24 12:14:57 CDT 2025

[subkanthi]

mysql> INSERT INTO `temporal_types_DATETIME1` VALUES ('DATETIME(1)','1000-01-01 00:00:00.0','2022-09-29 01:48:25.1','9999-12-31 23:59:59.9',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from temporal_types_DATETIME1;
+-------------+-----------------------+-----------------------+-----------------------+------------+
| Type        | Minimum_Value         | Mid_Value             | Maximum_Value         | Null_Value |
+-------------+-----------------------+-----------------------+-----------------------+------------+
| DATETIME(1) | 1000-01-01 00:00:00.0 | 2022-09-29 01:48:25.1 | 9999-12-31 23:59:59.9 | NULL       |
+-------------+-----------------------+-----------------------+-----------------------+------------+
1 row in set (0.00 sec)

32deeb898a85 :) select * from temporal_types_DATETIME1;

SELECT *
FROM temporal_types_DATETIME1

Query id: a199f47a-0fd1-4160-ae57-9dc28a8c9ea2

┌─Type────────┬─────────Minimum_Value─┬─────────────Mid_Value─┬─────────Maximum_Value─┬─Null_Value─┬────────────_version─┬─is_deleted─┐
│ DATETIME(1) │ 1900-01-01 18:00:00.0 │ 2022-09-29 01:48:25.1 │ 2299-12-31 17:59:59.0 │       ᴺᵁᴸᴸ │ 1904988143887581244 │          0 │
└─────────────┴───────────────────────┴───────────────────────┴───────────────────────┴────────────┴─────────────────────┴────────────┘

1 row in set. Elapsed: 0.002 sec. 

[aadant]

Please note that this bug is not fixed for DateTime(4), DateTime(5) and DateTime(6) in version 2.6.0. see https://github.com/Altinity/clickhouse-sink-connector/issues/1056

[subkanthi]

Fix did not cover DATETIME(4/5/6)

[subkanthi]

@Selfeer please also check the limits(datetime)