Java - How do I prevent a LocalDate shifting back one day to MySQL Date?

Question

I googled around but didn't find an immediate solution.

the data object has a LocalDate field
the MySQL database has a matching DATE column

Date is created, for example: LocalDate d = LocalDate.of(2020, 1, 3);

It's written to SQL as: stmt.setObject(1, d)); // or setDate with Date.valueOf(d)

In SQL it's stored as UTC (I guess) so due to the offset (+1) it's now 2020-01-02 and also fetched as such. (It obviously can't restore to the next day if DATE column doesn't store the hours.)

Are you sure the column in the database table is of type `DATE`? The db engine converting it due to an offset of some hours indicates a different type. Maybe `DATETIME` or even `TIMESTAMP`. — deHaar, Oct 08 '20 at 13:07
this may help to diagnose your issue: https://stackoverflow.com/questions/19023978/should-mysql-have-its-timezone-set-to-utc — fantaghirocco, Oct 08 '20 at 13:09
Have you tried to set a `String` instead of an `Object`? You could try `stmt.setString(1, d.format(DateTimeFormatter.ISO_LOCAL_DATE);` and let the database convert it to a `DATE`. — deHaar, Oct 08 '20 at 13:09
@deHaar using the setString with the ISO_LOCAL_DATE formatter works, thank you! If you would provide that as answer, I'll gladly accept. — FDM, Oct 08 '20 at 13:16

score 1 · Accepted Answer · answered Oct 08 '20 at 13:18

You can try to set a String instead of an Object and let the database convert that to a DATE. This would basically look like

LocalDate d = LocalDate.of(2020, 1, 3);
stmt.setString(1, d.format(DateTimeFormatter.ISO_LOCAL_DATE));

This should keep the date values as they are (if working at all). The Object conversion could be a problem here.

Java - How do I prevent a LocalDate shifting back one day to MySQL Date?

1 Answers1