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

本文介绍了localdate 的 Spring 数据查询返回错误的条目 - 减去一天的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在我的实体中，我有一个类型为 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 帮助上添加 &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

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

问题描述

推荐答案

相关文档推荐