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
