关于MYSQL8时区导致日期字段查询不正确的问题?

有一个Date类型的字段,JDBC的URL里serverTimezone设置的是UTC,用默认过滤器进行查询的时候,发现输入条件大于等于2021-01-01时,会把数据库里的2020-12-31的数据查询出来,直接在控制台写SQL语句xxx>=‘2021-01-01’无问题,不知道是BUG还是哪里设置有问题?日期时间(Datetime)字段没有此问题。

补充一下,发现日期都会加一天,比如数据库是2020-12-31,界面上用等于2021-01-01可以查出来,如果数据库是30日,界面上用等于2020-12-31日查出来。不明白为啥会自动加1天,按gmt+8来算,2020-12-30日加8小时也应该是2020-12-30日吧,怎么变成了31日。界面显示无问题,就是查询器这个条件转换后感觉是错了。

几个问题:

  1. date 类型是 localDate 吗?
  2. 能否开一下 sql 日志,看看执行的 sql 用的日期时间是怎样
  3. 服务是在哪里运行的,运行服务的机器时区是怎么配置的?

1、用studio产生的代码,类型为java.util.Date(@Temporal(TemporalType.DATE));
2、日志显示执行的预编译SQL语句的参数日期是正确的(就是界面的输入值);
3、服务器端的时间是GMT+8;
4、MYSQL的数据库里保存的数据都是按UTC时间保存的(无问题);

按描述可能不是时区的问题,Date 类型的列与时区没有关系,并且数据库里的时间与显示时间一致。
所以可能是条件解析的问题。

仅有一个表的一个字段存在这个问题,还是所有的 date 列都有这个问题?

我测了一下,可以重现这个问题, 这块可能是 CUBA 的BUG。我们讨论一下。

最近花时间看了一下这个问题, 可以说这个问题历史悠久。

简单来说,MySql 在存储 date 类型时会将对日期值进行时区转换, 将日期从 JVM 时区转换为 MySql 服务器设置的时区。 而 date类型的时间部分是空的,即00:00:00,如果JVM时区和 Mysql 时区不一致,这时时区转换将可能差一天。比如对于日期 2021-10-10 ,从 GTM+08:00 转换到 UTC 时区,会减去8个小时。成为2021-10-09 16:00:00 ,取日期部分,就会成为 2021-10-09 。 就这是差一天的原因。

对于这个问题,Mysql 团队曾经在 Mysql 5 Connector 的一个版本中进行过修复。但是在Mysql 8.0 中又去掉了。因为团队不认为这是一个 BUG 。 详情请参考后面的链接。

目前,最直接简单的修复方式是,将 mysql 服务器时区设置成为 web 服务器 jvm 时区一致。

参考:

  1. Wrong java.sql.Date stored if client and server time zones differ
  2. Again wrong java.sql.Date
    server timezone gets applied in PreparedStatment but not when reading ResultSet
  3. https://youtrack.jetbrains.com/issue/TW-55050
  4. https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-datetime-types-processing.html#cj-conn-prop_connectionTimeZone
  5. https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-time-instants.html
1赞

感谢您的回答,我做了如下的测试
1、当设置serverTimezone=UTC时,我在CUBA的界面里录入一个新的数据。把时间设置为2020-12-31后,保存再打开编辑界面时间会变成了2020-12-30(所有时间都会往前移一天);但是在查询过滤器里输入条件='2020-12-31’才能查询出来(显示依然为2020-12-30);
1.录入数据
image
2.再打开这条记录发现时间变了
image
3.查询的时候只能输入2020-12-31,显示结果却是30
image

同时在IDEA里用他的Database(serverTimezone=UTC时)连到同一个数据库,并且设置serverTimezone=UTC,查询等于’2020-12-30’的数据是可以看到该数据的

2、在IDEA里用他的Database(serverTimezone=UTC时)做同样的动作(先新增,再查询)无任何问题,全部正确。

另外按serverTimezone=Asia/Shanghai设置,CUBA和IDEA里都无问题。就是当serverTimezone=UTC时CUBA里有问题。

如果按您解释的是MYSQL驱动的问题,那为啥用数据库工具和用CUBA的情况完全不一样呢?

另外一个疑问就是既然date字段他这样处理,那为啥datetime字段,对于2020-12-31 00:00:00的数据,怎么没这个问题呢

驱动都是8.0.21

目前JVM和和MYSQL服务器的时区都是Asia/Shanghai

暂时不清楚 IDEA 的处理机制,我想大概与插入数据的方式有关。如果是字面量插入日期,可能不会进行转换。因为 mysql 驱动中是对 date 类型的参数进行处理。
你在 IDEA 里是如何插入数据的?

date 类型没有时间部分, 在存入mysql 时已经往前推了一天的,比如 2021-12-31,时区转换后就成了 2021-12-30 ,在从mysql 读取时是按 2021-12-30 00:00:00 再转换回jvm 时区,即使加8个小时,还是 12-30 。而 datetime 是有时间部分的。比如 2020-12-31 00:00:00 ,在存入 mysql 时减8个小时,取出时加8个小时,时间是保持不变的。

对于时间的处理可跟踪 mysql 驱动源码进行深入了解:
参考 ClientPreparedStatement 类的 setDate\setTimestamp 方法。
SqlDateValueFactory 类的 createFromDate\createFromTimestamp 方法。

我直接写过SQL语句,也用编辑界面提交过,都一样!

不清楚 idea 的处理机制,这也超出了 CUBA 的范围。你可以自行研究一下。也许 IDEA 对mysql 驱动进行了扩展。印象中 mysql 的数据类型转换部分允许扩展。你可以再深入研究一下。

好的,谢谢了。那就只能设置serverTimezone=Asia/Shanghai来解决了。

1赞