0

I have this weird phenomenon going in my code. I am using spring data jpa and MySQL database. I have a table named 'Submission' which has date field named 'finishedAt'. The datatype of that field is: 'Date'.

I have a mapped in class in Java:

@Entity
@Table(name = "Submission")
@Getter
@Setter
public class Submission{
     //All the fields here

     @Temporal(javax.persistence.TemporalType.DATE)
     java.util.Date finishedAtValue 
}

There is one method which tries to access this date value. When I print the date I see a value of previous day.

Example:

Lets say the date value in database is 2020-06-05 (yyyy-mm-dd)

The code snippet in the method is as follows:

//Code to get the entity from database successfully - submission

log.info("DateTimeAnswer retrieved from database: "+submission.getFinishedAtValue);
/*Expected date value from above log: 2020-06-05
Actual date value from above log: 2020-06-04*/

Not able to understand why this inconsistency while extracting this date value. Any help would be appreciated. Thanks!
VMA
  • 89
  • 1
  • 4
  • 16
  • The most likely cause is a mismatch between the time zone in your application and the session time zone used by the database. MySQL has a number of date bugs too. What is the time zone in the database, client, and session? In your application, log the result of `TimeZone.getDefault()`, and the result of `SELECT @@global.time_zone, @@session.time_zone` using the JPA database connection. – erickson May 08 '20 at 18:27
  • See also the release notes for the newest MySQL Connector/J driver, which fixed a bug in `Date` handling: https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/news-8-0-20.html – erickson May 08 '20 at 18:28
  • database time zone and app time zone are different. SELECT @@global.time_zone returns +03.00 and TimeZone.getDefault() returns sun.util.calendar.ZoneInfo[id="America/Denver"] – VMA May 08 '20 at 19:12
  • But what about `@@session.time_zone`? – erickson May 08 '20 at 19:36
  • It returns +03.00. – VMA May 08 '20 at 20:29
  • See [this Answer](https://stackoverflow.com/a/61595617/642706) with link to bug report on the faulty JDBC driver, *MySQL Connector*. Update to version 8.0.20 or later. – Basil Bourque May 09 '20 at 05:20

2 Answers2

1

java.util.Date class getter method returns the index for the day. This is Date class behavior.

Issue seems more or less related to TimeZone difference.

Please try the answers from similar question below.

Similar Question : SimilarQuestion

Tarun
  • 685
  • 3
  • 16
  • Thanks for your response. If I have date in database as: 2020-06-01 it logs the date value as 2020-05-31. – VMA May 08 '20 at 19:15
  • I've added link to similar question which will help you understanding it better way . It seems more or less related to timezone difference. – Tarun May 08 '20 at 20:04
  • Thanks for the reference! – VMA May 08 '20 at 20:29
1

Check the time portion of the date before storing it and after retrieving it. I'm guessing this is due to a bug in the MySQL driver prior to 8.0.20. If the date 2020-06-05 is treated as midnight +03:00, then converted to -06:00, it would be 6 PM on June 4, Mountain daylight time.

erickson
  • 265,237
  • 58
  • 395
  • 493