如何防止Mysql Connector/J转换DATE和TIME时区? [英] How to prevent Mysql Connector/J from converting DATE and TIME timezone?

问题描述

我正在使用以下DSN连接到mysql服务器:jdbc:mysql://localhost/my_database?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC.

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

我遇到的问题是java.sql.Date实例正在将时区从我的本地时区转换为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.

例如，我在IST(UTC + 05:30)中，当我在代码中将某个日期字段设置为说"2020-01-22"时，它将作为"2020-01-21"发送到服务器'.我已经从mysql一般日志中验证了这一点.

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.

我尝试了useLegacyDatetimeCode，useTimezone和noTimezoneConversionForDateType的几种组合，但到目前为止，我一直无法获得mysql驱动程序来跳过日期字段的转换.

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.

如何获取mysql驱动程序以跳过日期和时间"字段的转换?

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

我已经尝试了Connector/J驱动程序mysql:mysql-connector-java:<version>的版本6和版本8. 另外，我正在使用JOOQ，并使用一个简单的转换器在LocalDate和java.sql.Date之间进行转换.

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.

推荐答案

我自己也遇到了同样的问题，现在我使用此转换器解决了这个问题:

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...

这篇关于如何防止Mysql Connector/J转换DATE和TIME时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！