localdate 的 Spring 数据查询返回错误的条目 - 减去一天

2022-01-18 00:00:00 datetime time timezone mysql spring-data

在我的实体中，我有一个类型为 LocalDate day"的字段在 MySQL 中，它被映射到日期".输入.

In my Entity i have a field of type LocalDate "day" in MySQL it is mapped to "date" type.

MySQL 似乎在 UTC 上运行 SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); 正在返回 00:00:00.我的系统正在运行 CET (UTC+1)

MySQL seems to run on UTC SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); is returning 00:00:00. My system is running CET (UTC+1)

如果我通过 sql 查询它(来自 IntelliJ 的控制台配置了 空时区)查询

If i query it via sql (console from IntelliJ configured with empty Time zone) the query

select * from table where day = '2020-10-18';

返回正确日期的条目.

使用 Spring 数据 findByDay 指定的查询看起来也正确:

The query specified with Spring data findByDay is also looking correct:

2020-11-09 16:16:32.911 DEBUG 5600 --- [main] org.hibernate.SQL:从表 entity0_ 中选择 {所有字段}，其中 entity0_.tag=?2020-11-09 16:16:32.924 TRACE 5600 --- [main] o.h.type.descriptor.sql.BasicBinder:绑定参数 [1] 作为 [DATE] - [2020-10-18]

2020-11-09 16:16:32.911 DEBUG 5600 --- [ main] org.hibernate.SQL : select {all fields} from table entity0_ where entity0_.tag=? 2020-11-09 16:16:32.924 TRACE 5600 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [DATE] - [2020-10-18]

但它返回的条目 date = 2020-10-17

因此我的测试总是失败:

Thus my test is always failing:

@Test
void getWithoutMeldebereiche() {
    LocalDate of = LocalDate.of(2020, 10, 18);
    List<Entity> without =
            repository.findByDay(of);
    assertThat(without, is(not(empty())));
    assertThat(without.get(0).getTag(), is(of) ); //fails due to wrong date
}

数据源也被配置为不带时区(就像在 IntelliJ 中配置的那样):

The data source is also configured without time zone (as it is configured in IntelliJ):

spring.datasource.url=jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF8

如何使这个查询和测试独立于客户端(系统时区)服务器(数据库类型，服务器时区)(我无法控制它们)?如果我通过 IntelliJ 查询为什么它可以工作 - 也没有设置时区?

How to make this query and test work independently of client (system time zone) server (kind of data base, time zone of server) (i ' dont have control over them)? Why it is working if i query via IntelliJ - also no time zone set?

还有比LocalDate"更好的类型吗?映射到日期"；可以用于这个用例，它是真正的日期，因此是无时区的?

Are there better types than "LocalDate" mapped to "date" that can be used for this use case that are really dates and thus time-zone-less?

到目前为止我尝试了什么:不工作:

what i tried so far: not working:

  • spring.jpa.properties.hibernate.jdbc.time_zone=CET 没有帮助
  • spring-boot.run.jvmArguments=-Duser.timezone=UTC 不工作

工作但让我依赖于目标环境中的设置:

working but makes me dependent of set up in target environment:

  • 在连接 url 帮助上添加 &ser​​verTimezone=Europe/Berlin 但这在其他环境中我无法控制.这仅适用于 MySql.
  • 添加 TimeZone.setDefault(TimeZone.getTimeZone("UTC")); 有效
  • adding &serverTimezone=Europe/Berlin on connection url helps but this i can't control in other environments. and this is only working for MySql.
  • adding TimeZone.setDefault(TimeZone.getTimeZone("UTC")); works

推荐答案

MySQL Connector/J 8.0.22 之前版本存在 bug.更新到 8.0.22 将解决此问题.

There was a bug in the MySQL Connector/J earlier than version 8.0.22. Updating to 8.0.22 will fix the issue.

来自 8.0.22 变更日志:

LocalDate、LocalDateTime 和 LocalTime 值通过 Connector/J 设置当服务器之间存在时区差异时被更改和客户.此修复通过处理 LocalDate 来纠正问题，LocalTime 和 LocalDateTime，没有时区转换，也没有到其他日期时间类的中间转换.

LocalDate, LocalDateTime, and LocalTime values set through Connector/J were altered when there was a timezone difference between the server and the client. This fix corrects the issue by handling the LocalDate, LocalTime, and LocalDateTime with no time zone conversion and no intermediate conversions to other date-time classes.

潜在的错误:https://bugs.mysql.com/bug.php?id=93444

相关文章