I have application started on tomcat on MACHINE_A with timzone GMT+3
I use remote mysql server started on MASHINE_B with time zone UTC.
We use spring-data-jpa for pesistence.
As example of problem I will demonstrate repository:
public interface MyRepository extends JpaRepository<MyInstance, Long> { Optional<MyInstance> findByDate(LocalDate localDate); } if I pass localDate for 2018-09-06 I get entities where date is 2018-09-05(previous day)
In logs I see:
2018-09-06 18:17:27.783 TRACE 13676 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [DATE] - [2018-09-06] I googled that question a lot and found a several articles with the same content(for example https://moelholm.com/2016/11/09/spring-boot-controlling-timezones-with-hibernate/)
So I have following application.yml:
spring: datasource: url: jdbc:mysql://localhost:3306/MYDB?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC username: root password: ***** jpa: hibernate: naming: physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl properties: hibernate: show_sql: true use_sql_comments: true format_sql: true type: trace jdbc: time_zone: UTC But it doesn't help anyway.
We use following connector:
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.12</version> </dependency> How can I resolve my problem?
P.S.
I tried to run both applications with the same timezone. At this case averything works as expected
4 Answers
Answers 1
If you're using LocalDate in Java, you should use a DATE column in MySQL. This way the problem will be solved.
If you use LocalDateTime, try setting the property like this in Spring Boot:
spring.jpa.properties.hibernate.jdbc.time_zone=UTC For a more detailed explanation, check out this article. You can find a test case in my High-Performance Java Persistence GitHub repository which works just fine.
Answers 2
Ideally, your both servers should be in same time zone and preferred one be in UTC time zone. And to show correct time to user in his timezone; you parse it in browser itself. And while retrieving data from DB; you use UTC time. This way you will not have issue while fetching data from DB
Answers 3
In MySQL...
TIMESTAMP internally stores UTC, but converts to/from the server's timezone based on two settings. Check those settings via SHOW VARIABLES LIKE '%zone%'; Properly configured, the reader may see a different time than the writer (based on tz settings).
DATE and DATETIME take whatever you give it. There is no tz conversion between the string in the client and what is stored in the table. Think of it a storing a picture of a clock. The reader will see the same time string that the writer wrote.
Answers 4
I faced similar issues while creating some integration tests for a spring-boot application using hibernate. The database I used here was postgreSQL.
As another answer correctly points out, you can set the hibernate.jdbc.time_zone=UTC property like discribed. Nevermind this didn't solve my issues, so I had to set the JVM default time zone with the help of the following in my spring-boot applications main class:
@PostConstruct public void init(){ TimeZone.setDefault(TimeZone.getTimeZone("UTC")); // It will set UTC timezone System.out.println("Spring boot application running in UTC timezone :"+new Date()); // It will print UTC timezone } This should also solve your problems. You can gather more informations here.
Reason
I guess your problem (retrieving date - 1 day) comes from your specific setup. If your application is running in UTC and requesting timestamps from a database in GMT+3 it resolves in a earlier date, because the applications context (JVM and Hibernate are responsible here) is 3 hours behind the database context in UTC. Simple example:
2018-12-02 00:00:00 - 3hours = 2018-12-01 21:00:00
As you are only looking to the dates: 2018-12-02 - 3hours = 2018-12-01
0 comments:
Post a Comment