Hello,

I'm testing Debezium SQLServer connector 1.9.6.Final and I noticed an unexpected behaviour on datetime columns.

Context

SQLServer instance:

• SQLServer 2019 instance with Europe/Paris timezone.
• A table contain a datetime column. That means its value relates to the timezone of the instance.
• The column contains the value: 2008-10-31 00:00:00.000
  Meaning: October 31th 2008 at midnight in Paris.

Kafka Connector:

• Standard configuration, no column transformer.
• Kafka Connect JVM is running using UTC timezone.

• {
    "name": "client-mailboxes-la-cdc-source-connector",
    "config": {
      "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
      "database.server.name": "la-client-mailboxes-db",
      "database.hostname": "xxx",
      "database.port": "xxx",
      "database.user": "xxx",
      "database.password": "xxx",
      "database.dbname": "xxx",
      "plugin.name": "pgoutput",
      "schema.include.list": "dbo",
      "table.include.list": "dbo.tbl_test",
      "decimal.handling.mode": "string",
      "binary.handling.mode": "base64",
      "time.precision.mode": "adaptive",
      "tasks.max": 1,
      "poll.interval.ms": 60000,
      "database.history.kafka.bootstrap.servers": "kafka-latest:9092",
      "database.history.kafka.topic": "client-mailboxes-cdc-db-history",
      "transforms": "route",
      "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
      "transforms.route.regex": ".*",
      "transforms.route.replacement": "client-mailboxes-la-cdc",
      "key.converter": "org.apache.kafka.connect.storage.StringConverter",
      "key.converter.schemas.enable": false,
      "value.converter": "org.apache.kafka.connect.json.JsonConverter",
      "value.converter.schemas.enable": false,
      "message.key.columns": "dbo.tbl_test:id"
    }
  }  

• No database.server.timezone specified because it has been deprecated.

Message in the topic:

{
  "before": null,
  "after": {
    "id": 4534959,
    "created_at": 1225411200000
  },
  "source": {
    "version": "1.9.6.Final",
    "connector": "sqlserver",
    "name": "la-client-mailboxes-db7",
    "ts_ms": 1668784927380,
    "snapshot": "last",
    "db": "archives",
    "sequence": null,
    "schema": "dbo",
    "table": "tel_test",
    "change_lsn": null,
    "commit_lsn": "000914a7:00002a5d:0001",
    "event_serial_no": null
  },
  "op": "r",
  "ts_ms": 1668781327575,
  "transaction": null
}

The value of the field created_at is 1225411200000.
It represents the Epoch value. If we convert it back to the ISO format, we get 2008-10-31T00:00:00Z

It is the same value as the database but in UTC instead of Europe/Paris !

The expected value is 2008-10-30T23:00:00Z (Epoch 1225407600000) which has one hour less that the one from the database.

Because 2008-10-31T00:00:00 Europe/Paris is equals to 2008-10-30T23:00:00 UTC.

I was thinking that the connector queries CURRENT_TIMEZONE_ID() to convert all columns that depend on the instance timezone to the correct Epoch value.

What is wrong in the connector configuration or in my reasoning ? Thanks for your help.