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

Question

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

Answer 1

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