MySQL Bugs: #104822: LocalDate is shifted a day back on saving when the client date is not UTC

Thank you for your help! If the status of the bug report you submitted changes, you will be notified. You may return here and check on the status or update your report at any time. That URL for your bug report is: http://bugs.mysql.com/104822.

Bug #104822	LocalDate is shifted a day back on saving when the client date is not UTC
Submitted:	3 Sep 2021 11:36	Modified:	3 Sep 2021 12:51
Reporter:	Vojtech Knyttl	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.26	OS:	Any
Assigned to:	Alexander Soklakov	CPU Architecture:	Any

Description:
This is a follow up issue for:

https://bugs.mysql.com/bug.php?id=93444

I also posed a question regarding this on: 

https://stackoverflow.com/questions/69044170/hibernate-jdbc-shifting-localdate-a-day-back

It seems that the issue above was resolved for LocalDateTime, but not LocalDate.

Once you have client in CEST timezone and server in UTC, every storage of LocalDate will shift the timestamp a day back.

How to repeat:
1. Set client to CEST+2
2. Set server to UTC
3. Store a LocalDate in a mysql DATE column.
4. The field will be shifted one day back.

Hi Vojtech,

Could you provide a reproducible test case without Hibernate involved?
It looks strange because c/J should not use any time part when storing LocalDate to DATE column and retrieving it as a LocalDate. It's either Hibernate created the column with a different type or it's used a wrong setter/getter.

I am sorry for not attaching it instantly:

```
fun main() {
    TimeZone.setDefault(TimeZone.getTimeZone("Europe/Prague")) // JVM timezone UTC-07:00
    val cnxnUrl = ("jdbc:mysql://host/db?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC")
    try {
        DriverManager.getConnection(cnxnUrl, "user", "pass").use { cnxn ->
            println(cnxn.metaData.driverVersion)
            val st = cnxn.createStatement()
            st.execute("CREATE TEMPORARY TABLE foo (id INT PRIMARY KEY, dt DATETIME)")
            val ldtSent = LocalDate.of(2018, 12, 1)
            System.out.printf("LocalDate sent: %s%n", ldtSent.toString())
            val ps = cnxn.prepareStatement("INSERT INTO foo (id, dt) VALUES (?,?)")
            ps.setInt(1, 1)
            ps.setObject(2, ldtSent)
            ps.executeUpdate()
            val rs = st.executeQuery("SELECT dt FROM foo WHERE id=1")
            rs.next()
            val ldtRcvd =
                rs.getObject(1, LocalDateTime::class.java)
            System.out.printf("LocalDate rcvd: %s%n", ldtRcvd.toString())
        }
    } catch (ex: Throwable) {
        ex.printStackTrace(System.err)
    }
}
```

Returns:

```
LocalDate sent: 2018-12-01
LocalDate rcvd: 2018-11-30T00:00
```

I basically used the same example from:

https://bugs.mysql.com/bug.php?id=93444

And used LocalDate instead of LocalDateTime.

Okay, for some reason it still uses the old version of the connector.

I am sorry, it was obviously not properly updated.