kafka输出中关于 datetime timestamp 和时区的问题

场景

有如下从MySQL经kafka最终复制到DB2的过程：

MySQL --(dtle)--> kafka --(Confluent JDBC Sink Connector)--> DB2

• 其中MySQL和DB2的默认时区推测为+8.
• 源端、目标端试验表各有4个字段
• 进行两次测试, JDBC Sink Connector配置项db.timezone分别设为了+8和UTC

结果

field	MySQL type	DB2 type	kafka中间值	+8区结果	UTC结果
createDate	date	date	该日期零点的UTC timstamp / 24 / 3600	正确	早1天
createTime	datetime	time	+8区timestamp * 1000	晚8小时	正确
updateDate	timestamp	timestamp	UTC时间如2000-01-01T00:00:00Z	正确	早8小时
updateTime	datetime	timestamp	+8区timestamp * 1000	晚8小时	正确

对于db.timezone的两种配置, 各有一部分数据类型是正确的.

需要解释

• 哪种db.timezone是合理设置
• 应从哪个角度修正时区偏差

MySQL行为

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

对于date和datetime, 不按@@time_zone进行转换, select结果都是insert时的字面日期时间.

debezium行为

The DATETIME type represents a local date and time such as "2018-01-13 09:48:27". As you can see, there is no time zone information. Such columns are converted into epoch milliseconds or microseconds based on the column’s precision by using UTC. The TIMESTAMP type represents a timestamp without time zone information. It is converted by MySQL from the server (or session’s) current time zone into UTC when writing and from UTC into the server (or session’s) current time zone when reading back the value. For example:

已知dtle kafka输出和dbz一致.