MySQL 时间“不正确”问题

[JasonLaw]

准备工作（数据库）

docker run --rm --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=test -e TZ='Asia/Shanghai' -p 3306:3306 -d mysql:8

docker exec -it some-mysql mysql -u root -pmy-secret-pw

use test;
create table t (dt datetime);

真实结果 & 期望结果

程序通过 MyBatis 执行insert into t (dt) values (#{now})，变量now是通过Instant.now()产生的，其值为2020-08-22T11:48:22.150Z，这是正确的。但是为什么数据库存储的是2020-08-22 06:48:22呢？不应该是2020-08-22 19:48:22吗？2020-08-22 06:48:22是怎么来的？

[xupefei]

你用的镜像有 tzdata 包么？
mysql select now()看看结果？

[JasonLaw]

`docker exec -it some-mysql mysql -u root -pmy-secret-pw`，然后执行`select now();`，其结果为`2020-08-22 20:19:13`。

[JasonLaw]

@xupefei #1 `docker exec -it some-mysql mysql -u root -pmy-secret-pw`，然后执行`select now();`，其结果为`2020-08-22 20:19:13`。

[xupefei]

把 now()插到表里在读出来，如果没问题的话就开始 debug mybatis 吧。

[xuanbg]

docker 容器的时区、mysql 的时区、jvm 的时区、tomcat 的时区，它们都不是一回事。要全统一为东八区，才能够让时间和现实保持一致。

[rockyou12]

首先你数据类型是啥？ long 、timestamp 、datetime ？
然后你工具不同也可能造成显示不一样（起码我之前用 navicat 和 idea 看 pg 的 timestampz，同个时间时区就是显示不一样），你 sql 里显式转成 utc 时间看是不是一致

[JasonLaw]

@xupefei #4
@xuanbg #5
@rockyou12 #6

通过在 connection URL string 中显式地设置时区（ serverTimezone=Asia/Shanghai ）解决了，原因是时区的不一致导致的，默认的时区应该是 UTC−05:00 。

Setting the MySQL JDBC Timezone In Spring Boot | Baeldung - https://www.baeldung.com/mysql-jdbc-timezone-spring-boot#param

[chihiro2014]

时区问题

[petelin]

所以 mysql 里面不要用时间 用一个 int64 不香吗

[JasonLaw]

@petelin #9 关于使用什么数据类型存储时间，网上真的什么说法都有。下面是我找到的一些资料，希望对看到这个主题的人有所帮助。对于使用 BIGINT 存储时间，我暂时不太清楚那样子做的利弊，可能之后会尝试一下，看看它的利弊到底是什么。

integer - Should I use a big INT or regular INT in MySQL to store a timestamp? - Stack Overflow - https://stackoverflow.com/questions/2031228/should-i-use-a-big-int-or-regular-int-in-mysql-to-store-a-timestamp

mysql - DATETIME VS INT for storing time? - Stack Overflow - https://stackoverflow.com/questions/43705935/datetime-vs-int-for-storing-time

Should I use the datetime or timestamp data type in MySQL? - Stack Overflow - https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql

Adam D'Angelo's answer to What is the best way to store a timestamp in MySQL? - Quora - https://www.quora.com/What-is-the-best-way-to-store-a-timestamp-in-MySQL/answer/Adam-DAngelo

[m1ch3ng]

试试：echo "Asia/Shanghai" > /etc/timezone

[palfortime]

@JasonLaw 不是默认时区是-05:00，是中国的时区缩写是 CST，美国 Central Standard Timezone 缩写也是 CST，mysql 默认认为 CST 是美国那个时区。

[JasonLaw]

@palfortime 我不太明白你所说的“mysql 默认认为 CST 是美国那个时区”，我已经设置了时区为 Asia/Shanghai 了。

[palfortime]

@JasonLaw 具体我也没有深入研究，我猜是服务器返回了 cst，mysql connector 就按美国 cst 时间来计算，传给服务器的时间就是西五区时间，也就是我国时间减十三小时。
其实，我主要想表达你说的默认时区是西五区的说法是有问题的。一般来说有默认时区也应该是 GMT 。

[palfortime]

详细看了一下：
1. com.mysql.cj.protocol.a.NativeProtocol 的 configureTimezone 方法，会获取 mysql 服务器变量 system_time_zone，我的 mysql 中这个变量是 CST ；
2. 接着，通过 TimeUtil.getCanonicalTimezone 这个方法，把 CST 转成唯一的时区表现格式；
3. TimeUtil.getCanonicalTimezone 最终是从 sun.util.calendar.ZoneInfoFile 里获取到 CST 的映射：{ "CST", "America/Chicago" }；

所以，客户端误认为服务器使用 Chicago 的时间，上送时间时就会转换成对应的 Chicago 时间，但服务器实际是使用北京时间，所以导致服务器保存的时间与实际相差了 13 个小时。

[anUglyDog]

@xuanbg 所以只需要系统对接系统只允许传递时间戳，根本不用考虑这些时区问题。