当服务器时区不是UTC时，在Java中从MySQL检索UTC DATETIME字段

Question

我正在尝试编写代码，以便使用Java和MySQL与第三方开发的数据库进行互操作。此数据库有一个字段，该字段将datetime字段中的时间戳存储为UTC日期。运行数据库和客户端的服务器的时区被设置为非UTC区(Europe/London)，因此默认情况下，时间戳被错误地读回，就像它是本地时间一样。我正在尝试编写代码以UTC的形式读回它。

我在这里读过几个类似的问题，但没有一个对我有效的答案：

• MySQL-如何用正确的时区存储时间？（来自Java）
• 如何将java.util.Date存储到UTC/GMT时区中的MySQL时间戳字段中？
• MySQL中的UTC日期
• 如何设置MySQL的时区？

不幸的是，我无法更改任何服务器设置，因此我尝试使用连接的“time_zone”变量将数据库服务器设置为使用UTC，并将可选的Calendar参数设置为ResultSet.GetTimestamp来检索日期，但这对结果没有影响。下面是我的代码：

private static final Calendar UTCCALENDAR = Calendar.getInstance (TimeZone.getTimeZone (ZoneOffset.UTC));
public Date getDate ()
{
    try (Connection c = dataSource.getConnection ();
         PreparedStatement s = c
             .prepareStatement ("select datefield from dbmail_datefield where physmessage_id=?"))
    {
        fixTimeZone (c);
        s.setLong (1, getPhysId ());
        try (ResultSet rs = s.executeQuery ())
        {
            if (!rs.next ()) return null;
            return new Date (rs.getTimestamp(1,UTCCALENDAR).getTime ());    // do not use SQL timestamp object, as it fucks up comparisons!
        }
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Error accessing dbmail database", e);
    }
}

private void fixTimeZone (Connection c)
{
    try (Statement s = c.createStatement ())
    {
        s.executeUpdate ("set time_zone='+00:00'");
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Unable to set SQL connection time zone to UTC", e);
    }
}

我试图读取的数据库字段中存储了一个值,如下所示：

myhtml" target="_blank">sql> select * from dbmail_datefield where physmessage_id=494539;
+----------------+--------+---------------------+
| physmessage_id | id     | datefield           |
+----------------+--------+---------------------+
|         494539 | 494520 | 2015-04-16 10:30:30 |
+----------------+--------+---------------------+

但不幸的是,结果显示为BST而不是UTC：

java.lang.AssertionError: expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>

Answer 1

您的客户机getdate()代码看起来是正确的。我认为您还需要使用MySQL Connector/J JDBC驱动程序来将存储在表中的日期作为UTC日期处理，以避免错误的时区转换。这意味着设置有效的服务器时区，以及用于JDBCGetTimestamp调用的客户端会话时区和日历。

看看在失败的断言中得到的值，以及错误在哪个方向：

expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>

你得到的是英国夏令时10点30分，也就是格林尼治时间9点30分。这与数据库将表中的10:30视为BST值并在您将其解析为GMT日期之前将其虚假地转换为GMT是一致的。这与被错误地转换为BST的GMT值的方向相反。

这可能是一个特定于JDBC的问题，因为JDBC要求将time times转换到本地区域。（而MySQL C API却没有，这可能是因为C的经典时间类型不像Java那样具有区域感知性。）它还需要知道它从什么区域转换。MySQLtimestamp类型始终存储为UTC。但对于datetime类型没有说明这一点。我认为这意味着MySQL将datetime列值解释为在服务器的时区中。这与断言错误消息中显示的移动方向是一致的。

您设置的time_zone会话变量告诉MySQL服务器您的客户机的时区是什么，但它不影响服务器认为自己的时区是什么。可以使用ServerTimeZoneJDBC连接属性重写。在连接上，将ServerTimeZone设置为UTC，并确保UselegacyDateTimeCode关闭。（如果不起作用，则查看其他与区域相关的属性。）看看这是否使您的日期以UTC的形式出现，并与数据库中的日历字段值相同。

请注意，这将改变数据库中其他datetime值的解释：它们现在看起来都像UTC日期（在JDBC连接的上下文中）。这是否正确将取决于它们最初是如何填充的。虽然您的客户机代码将具有您想要的行为，但我不知道在服务器级别上不将服务器的时区设置为UTC的情况下，是否可以使系统作为一个整体保持完全一致的行为。基本上，如果它的区域没有设置为UTC，那么它就没有为您想要的行为完全配置，您就会绕过它。