Description:
Javadoc of java.sql.PreparedStatement.setDate() method says:
Sets the designated parameter to the given java.sql.Date value using the **default time zone of the virtual machine** that is running the application.
But, the implementation currently uses timezone of the MySQL Server instead of timezone of Java VM.
See:
* com.mysql.cj.ClientPreparedQueryBindings#setDate(int, java.sql.Date, java.util.Calendar)
* com.mysql.cj.AbstractQueryBindings#setObject(int, java.lang.Object) - which calls setDate with calender=null under the hood
This leads to wrong value of SQL DATE when MySQL server timezone have less offset than Java VM timezone (e.g. MySQL server: UTC, JVM: Europe/Kiev).
For example, with this query
UPDATE t SET date = ?
you do:
statement.setObject(1, LocalDate.of(1990, 10, 20));
or
statement.setDate(position, java.sql.Date.valueOf(LocalDate.of(1990, 10, 20)));
Both variants will actually save "1990-10-19" into DB.
How to repeat:
1. Set timezone of MySQL server instance to UTC.
2. Set timezone of JVM to some timezone east from UTC. E.g. Europe/Kiev.
3. Create a table with `DATE` column.
4. Create PreparedStatement with a query, that updates `DATE` field value and uses argument binding for the new field value.
5. Bind using PreparedStatement.setDate without Calendar argument or .setObject(LocalDate). Use "1990-10-20" as a date value.
6. Execute PreparedStatement.
7. Check the table state with any MySQL client. You will see that value of `DATE` column is "1990-10-19".
Suggested fix:
Change default timezone used by setDate method (if no Calendar instance explicitly given - 3rd argument) to current JVM timezone, as stated in PreparedStatement interface documentation.
See com.mysql.cj.ClientPreparedQueryBindings#setDate(int, java.sql.Date, java.util.Calendar).
Current workaround:
Use setDate variant with Calendar argument explicity passed with Calendar instance containing current JVM timezone. Like:
statement.setDate(1, java.sql.Date.valueOf(LocalDate.of(1990, 10, 20)), Calendar.getInstance());
Never use statement.setObject(1, LocalDate); variant until this bug is fixed or always use MySQL server instance with timezone equal to JVM timezone. But this is not always possible.
