春季数据查询localdate返回错误的条目-减去一天 [英] Spring data query for localdate returns wrong entries - minus one day
问题描述
在我的实体中，我有一个类型为 LocalDate 的天"字段.在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';
返回具有正确日期的条目.
Returns entries with correct date.
使用Spring数据 findByDay 指定的查询也看起来正确:
The query specified with Spring data findByDay is also looking correct:
2020-11-09 16:16:32.911调试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:
- 在连接网址上添加& serverTimezone = 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版.更新到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.
从 通过Connector/J设置的LocalDate，LocalDateTime和LocalTime值服务器之间的时区不同时被更改和客户.此修复程序通过处理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 这篇关于春季数据查询localdate返回错误的条目-减去一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
