MySQL Bugs: #106518: LocalDateTime parameter values are being passed to query with OS timezone

Bug #106518	LocalDateTime parameter values are being passed to query with OS timezone
Submitted:	19 Feb 2022 15:29	Modified:	21 Mar 2022 11:04
Reporter:	Roman Zinchuk	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	mysql-connector-java:8.0.27	OS:	Windows
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
LocalDateTime.of(2022, Month.FEBRUARY, 14, 0, 0) was passed to query with os timezone (why??)
2022-02-14T00:00:00.000+0200

How to repeat:
1. Set os timezone to non-UTC (e.g EET).
2. Set connection url param serverTimezone=UTC
3. Here is pseudocode to make a query to reproduce the issue (using Spring framework):
var date = LocalDateTime.of(2022, Month.FEBRUARY, 14, 0, 0);
var query = entityManager.createNativeQuery("SELECT :myLocalDateTime", String.class);
query.setParameter("myLocalDateTime", date);
query.getResultList();
It passes time-zone that makes my queries wrong. I expected that LocalDateTime shoould be zoneless.

Suggested fix:
Remove timezone from resolved query param

I'm using &logger=Slf4JLogger&profileSQL=true connectin url params to log queries.

I see two queries in my logs (1 did only 1 query, maybe some sort of debugging info )
First one has this date representation
2022-02-13 22:00:00
And second one has this:
2022-02-14T00:00:00.000+0200
Maybe it will help somehow

LocalDate works as expected, final query has correct value
'2022-02-14'
but intermediate one has zone suffix (i don't know how sql logging works, but it's strange that localDate has something to do with zone)
2022-02-14T00:00:00.000+0200

Hi,

It's hard to say what's really happening without seeing a real server queries, table and definitions, c/J connection string. Please, provide more details so we could point the problem and propose a workaround if possible.

But it's a higher chance that the issue is not related to c/J. Instant, LocalDateTime, OffsetDateTime and ZonedDateTime are all mapped to TIMESTAMP in Hibernate. So all of them are processed identically, with applying the local time zone. Probably you need to use a String instead to avoid such conversions.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".