Java SQL date is off by 1 day

Question

I'm using a MySQL server which is located in the same timezone as me. Im trying to insert a java.util.Date into the database on a column of type DATE with the following code:

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); dateFormat.setTimeZone(TimeZone.getTimeZone("GMT+2")); Date date = dateFormat.parse(map.value("date"));

Where "date" is inserted. The problem is that the date is off by one day when looking in the database. Example: I inserted 2020-05-02, the database shows 2020-05-01. This gives the problem that when I want to get the matches with date 2020-05-02, I have to search for 1 day earlier which I dont want to do. Also this happens between specific times on the day so im assuming its a timezone problem but I dont know how to fix it!

Tips?

Answer 1

This issue is coming from the MySQL Connector in java. Version 8.0.19 converts the date using the JVMs timezone which results in the 1 day off problem. This is resolved in the 8.0.20 patch. Read here https://dev.mysql.com/doc/relnotes/connector-j/8.0/en/

Answer 2

For a date-only value, without time-of-day and without time zone, you should be using the MySQL type DATE when defining your column. This type matches the DATE type defined in the SQL standard.

On the Java side, never use java.util.Date. That class is part of the terrible date-time classes bundled with the earliest versions of Java. They were years ago supplanted by the java.time classes defined in JSR 310.

For a date-only value in Java, use java.time.LocalDate.

In your map, you should be storing LocalDate object rather than mere string. But if you must, you can parse and generate strings. The format of your strings appear to coincide with the standard format defined in ISO 8601. The java.time classes use those standard formats by default. So no need to specify a formatting pattern.

LocalDate localDate = LocalDate.parse( "2021-01-23" ) ;

Objects of the java.time classes can be exchanged with your database by using a JDBC driver compliant with JDBC 4.2 and later.

Write that to the database.

myPreparedStatement.setObject( … , localDate ) ;

Retrieve from database.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

Using the approach shown above makes time zones irrelevant: LocalDate via JDBC 4.2 or later on a column of type MySQL DATE — That is, unless your JDBC driver is buggy (Bug #30877755): See Answer by Teh Swish.