Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.3k views
in Technique[技术] by (71.8m points)

mysql - Spring data query for localdate returns wrong entries - minus one day

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

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

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.

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

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]

But it is returning entries with 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
}

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

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?

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 does not help
  • spring-boot.run.jvmArguments=-Duser.timezone=UTC is not working

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

  • 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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

From the 8.0.22 changelog:

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.

The underlying bug: https://bugs.mysql.com/bug.php?id=93444


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...