当服务器时区不是UTC时，从Java中的MySQL中检索UTC DATETIME字段 [英] Retrieving UTC DATETIME field from MySQL in Java when server timezone is not UTC
问题描述
我正在尝试编写代码以使用Java和MySQL与第三方开发的数据库进行互操作.该数据库具有一个字段，该字段将时间戳记存储在DATETIME字段中作为UTC日期.将同时在其上运行数据库和客户端的服务器的时区设置为非UTC区域(Europe/London)，因此默认情况下，时间戳被错误地读回，就像本地时间一样.我正在尝试编写代码以将其读回UTC.
I'm trying to write code to interoperate with a third-party-developed database using Java and MySQL. This database has a field that stores a time stamp in a DATETIME field as a UTC date. The timezone for the server on which both the database and client run is set to a non-UTC zone (Europe/London), so by default the timestamp is read back incorrectly as if it were a local time. I'm trying to write code to read it back as UTC.
我在这里阅读了几个类似的问题，但是没有一个问题对我有用:
I have read several similar questions here, but none of them have an answer that works for me:
- MySQL - how to store time with correct timezone? (from Java)
- How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?
- Date in UTC in mysql
- How do I set the time zone of MySQL?
不幸的是，我无法更改任何服务器设置，因此我尝试使用连接的"time_zone"变量将数据库服务器设置为使用UTC并将可选的Calendar参数设置为ResultSet.getTimestamp来检索日期，但是对结果没有影响.这是我的代码:
Unfortunately, I cannot change any server settings, so I have tried using the connection's "time_zone" variable to set the database server to use UTC and the optional Calendar parameter to ResultSet.getTimestamp to retrieve the date, but this has no effect on the result. Here is my code:
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);
}
}
我尝试读取的数据库字段具有一个存储在其中的值，如下所示:
The database field I'm trying to read has a value stored in it as follows:
mysql> select * from dbmail_datefield where physmessage_id=494539;
+----------------+--------+---------------------+
| physmessage_id | id | datefield |
+----------------+--------+---------------------+
| 494539 | 494520 | 2015-04-16 10:30:30 |
+----------------+--------+---------------------+
但是不幸的是，结果是BST而不是UTC:
But unfortunately, the result comes out as BST not UTC:
java.lang.AssertionError: expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>
推荐答案
您的客户端getDate()代码就目前而言看起来是正确的.我认为您还需要获取MySQL Connector/J JDBC驱动程序，以将表中存储的日期作为UTC日期，以避免虚假的时区转换.这意味着，除了在执行操作时，还要设置有效的服务器时区，以及用于JDBC getTimestamp调用的客户端会话时区和Calendar.
Your client getDate() code looks correct as far as it goes. I think you also need to get the MySQL Connector/J JDBC driver to treat the dates stored in the table as UTC dates, to avoid a spurious time zone conversion. This means setting the effective server time zone, in addition to the client session time zone and Calendar used for JDBC getTimestamp calls as you're doing.
看看失败的断言中获得的值以及错误的方向:
Take a look at the values you got in your failed assertion, and which direction the error is in:
expected:<Thu Apr 16 11:30:30 BST 2015> but was:<Thu Apr 16 10:30:30 BST 2015>
您返回的时间是格林尼治标准时间10:30 BST(格林尼治标准时间).这与数据库将表中的10:30视为BST值并在您将其解析为GMT日期之前为您虚假地将其转换为GMT一致.这就是将GMT值错误地转换为BST的相反方向.
What you got back was 10:30 BST, which is 9:30 GMT. This is consistent with the database treating that 10:30 in the table as a BST value and spuriously converting it to GMT for you, before you parse it as a GMT date. That's the opposite direction of a GMT value being spuriously converted to BST.
这可能是特定于JDBC的问题，因为JDBC要求将时间转换为本地区域. (没有MySQL C API的地方，可能是因为C的经典时间类型不像Java那样识别区域.)而且，它还需要知道它将从转换成哪个区域. MySQL TIMESTAMP类型始终存储为UTC.但这并未针对DATETIME类型进行说明.我认为，这意味着MySQL将把DATETIME列值解释为在服务器的时区中.您提到将其设置为BST，这与声明错误消息中显示的移位方向一致.
This may be a JDBC-specific issue, because JDBC requires that time times be converted to the local zone. (Where the MySQL C API doesn't, probably because C's classic time types are not zone-aware the way Java's are.) And it needs to know what zone it's converting from, as well. The MySQL TIMESTAMP type is always stored as UTC. But that's not stated for the DATETIME type. I think that implies that MySQL is going to interpret DATETIME column values as being in the server's time zone. Which you mentioned as being set to BST, and that's consistent with the direction of the shift shown in your assertion error message.
您设置的time_zone会话变量告诉MySQL服务器您客户端计算机的时区是什么，但是它不会影响服务器认为自己的时区是什么.可以用 serverTimezone覆盖JDBC连接属性.在您的连接上，将serverTimezone设置为UTC，并确保useLegacyDatetimeCode已关闭. (并查看其他与区域相关的属性，如果该方法不起作用.)查看该日期是否以与数据库中相同的日历字段值的UTC形式通过.
The time_zone session variable you set is telling the MySQL server what your client machine's time zone is, but it doesn't affect what the server thinks its own time zone is. That can be overridden with the serverTimezone JDBC connection property. On your connection, set the serverTimezone to UTC, and make sure useLegacyDatetimeCode is off. (And look through the other zone-related properties if that doesn't work.) See if that gets your dates to come through as UTC with the same calendar field values as in the database.
请注意，这将改变数据库中其他DATETIME值的解释:现在，它们都看起来像UTC日期(在JDBC连接的上下文中).这是否正确将取决于它们最初是如何填充的.尽管您的客户端代码将具有您想要的行为，但是我不知道在不将服务器时区设置为服务器级别的UTC的情况下，是否可以使此系统作为一个整体来完全一致地运行.基本上，如果没有将其区域设置为UTC，则不会针对您想要的行为对其进行完全配置，而您将陷入困境.
Be aware that this is going to change the interpretation of other DATETIME values in your database: they're all going to look like UTC dates now (in the context of your JDBC connection). Whether that's correct is going to depend on how they were populated initially. While your client code will have the behavior you want, I don't know if this system as a whole can be made to behave fully consistently without setting the server's time zone to UTC at the server level. Basically, if it doesn't have its zone set to UTC, it's not fully configured for the behavior you want, and you're kludging around it.
这篇关于当服务器时区不是UTC时，从Java中的MySQL中检索UTC DATETIME字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
