How to prevent Mysql Connector/J from converting D

Question

I am connecting to a mysql server using the following DSN: jdbc:mysql://localhost/my_database?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC.

The problem I'm getting is that the java.sql.Date instance is getting timezone converted to UTC from my local timezone. My application treats dates as timezone agnostic and this is causing a few problems.

For instance, I'm in IST (UTC+05:30), when I set some date field to say '2020-01-22' in code, it gets sent to the server as '2020-01-21'. I have verified this from the mysql general log.

I have tried a few combinations of useLegacyDatetimeCode, useTimezone and noTimezoneConversionForDateType but I've been so far unable to get the mysql driver to skip conversion of the date field.

How do I get the mysql driver to skip the conversion for the Date and Time fields?

I have tried both version 6 and 8 of the Connector/J driver mysql:mysql-connector-java:<version>. Also, I'm using JOOQ and using a simple converter to convert between LocalDate and java.sql.Date.

Answer 1

If you really want to have a "time-zone agnostic" date, you would have to use LocalDate within Java. LocalDate maps quite nicely to MySQL's DATE type.

https://thoughts-on-java.org/hibernate-5-date-and-time/

No time zones involved at all.

Answer 2

I just had the same problem myself and for now I solved it with this converter:

public final class DateConverter implements Converter<Date, LocalDate> {
    @Override
    public final LocalDate from(final Date value) {
        if(null == value) {
            return null;
        } else {
            return value.toLocalDate();
        }
    }

    @Override
    public final Date to(final LocalDate value) {
        if(null == value) {
            return null;
        } else {
            return new Date(value.atStartOfDay(ZoneOffset.UTC).toInstant().toEpochMilli());
        }
    }

    @Override
    public final Class<Date> fromType() {
        return Date.class;
    }

    @Override
    public final Class<LocalDate> toType() {
        return LocalDate.class;
    }
}

I must admit that I did not yet think about the behaviour, if this converter is used with different timezones...