首页 文章

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

提问于
浏览 次
23

我正在尝试使用Java和MySQL编写代码以与第三方开发的数据库进行互操作 . 此数据库具有一个字段，该字段将 DATETIME 字段中的时间戳存储为UTC日期 . 运行数据库和客户端的服务器的时区设置为非UTC区域（ Europe/London ），因此默认情况下，时间戳的读取不正确，就像它是本地时间一样 . 我正在尝试编写代码以将其读回UTC .

我在这里已经阅读了几个类似的问题，但是他们都没有一个对我有用的答案：

不幸的是，我无法更改任何服务器设置，因此我尝试使用连接的"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);
    }
}

我正在尝试读取的数据库字段中存储了一个值，如下所示：

mysql> 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>
java mysql date datetime jdbc

5 回答

  • 4

    您的客户端 getDate() 代码看起来是正确的 . 我想你还需要让MySQL Connector / J JDBC驱动程序将表中存储的日期视为UTC日期，以避免虚假的时区转换 . 这意味着除了用于JDBC getTimestamp 调用的客户端会话时区和日历之外，还要设置有效的服务器时区 .

    查看失败断言中的值以及错误的方向：

    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一致 . 这是GMT值被虚假转换为BST的相反方向 .

    这可能是特定于JDBC的问题，因为JDBC要求将时间转换为本地区域 . （MySQL C API没有't, probably because C'经典时间类型也不像Java 's are.) And it needs to know what zone it'转换的那样区域感知.MySQL TIMESTAMP 类型总是存储为UTC . 但是 DATETIME 类型没有说明 . 我认为暗示MySQL将把 DATETIME 列值解释为在服务器's time zone. Which you mentioned as being set to BST, and that'中与断言错误消息中显示的移位方向一致 .

    您设置的 time_zone 会话变量告诉MySQL服务器您的客户端计算机会影响服务器认为自己的时区是什么 . 这可以用serverTimezone JDBC connection property覆盖 . 在您的连接上，将 serverTimezone 设置为UTC，并确保 useLegacyDatetimeCode 已关闭 . （并查看其他与区域相关的属性，如果这不起作用 . ）查看是否使用与数据库中相同的日历字段值获取UTC日期 .

    请注意，这将改变数据库中其他 DATETIME 值的解释：它们're all going to look like UTC dates now (in the context of your JDBC connection). Whether that'的正确性将取决于它们最初的填充方式 . 虽然您的客户端代码将具有您想要的行为，但我在服务器级别上将UTC时区限制为UTC . 基本上，如果它没有完全配置你想要的行为，你就会在它周围徘徊 .

    回复于
  • 2

    也许你可以使用JodaTime如下;

    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=?"))
    {
        s.setLong (1, getPhysId ());
        try (ResultSet rs = s.executeQuery ())
        {
            if (!rs.next ()) return null;
            DateTime dt = new LocalDateTime(rs.getTimestamp(1,UTCCALENDAR).getTime ()).toDateTime(DateTimeZone.forID("Europe/London"));  

            return dt.toDate();               }
    }
    catch (SQLException e)
    {
        throw new MailAccessException ("Error accessing dbmail database", e);
    }
}

    EDIT:

    java.util.Date不是TimeZone不可知的 . toDateTime方法负责TimeZone和DST，因此您不关心它

    以下代码：

    public static void main(String[] args) {
    // 29/March/2015 1:05 UTC
    DateTime now = new DateTime(2015, 3,29,1,5,DateTimeZone.UTC);
    // Pre DST 29/March/2015 0:30 UTC
    DateTime preDst = new DateTime(2015, 3,29,0,30,DateTimeZone.UTC);
    System.out.println("1:05 UTC:"+now);
    System.out.println("0:30 UTC:"+preDst);
    DateTimeZone europeDTZ = DateTimeZone.forID("Europe/London");
    DateTime europeLondon = now.toDateTime(europeDTZ);
    System.out.println("1:05 UTC as Europe/London:"+europeLondon);
    DateTime europeLondonPreDst = preDst.toDateTime(europeDTZ);
    System.out.println("0:30 UTC as Europe/London:"+europeLondonPreDst);
}

    将打印：

    1:05 UTC:2015-03-29T01:05:00.000Z
0:30 UTC:2015-03-29T00:30:00.000Z
1:05 UTC as Europe/London:2015-03-29T02:05:00.000+01:00
0:30 UTC as Europe/London:2015-03-29T00:30:00.000Z

    如果你能看到JodaTime负责DST .

    回复于
  • -1

    在我看来，你最好的选择是告诉MySQL使用GMT并处理应用程序代码中的所有本地时间问题，而不是数据库 . 数据库中的值始终为GMT，完全停止，这是明确的 . 正如您所说，通过夏令时（夏令时）调整，您可以在数据库中获得与我们人类相同的两个不同时间的值 .

    这也使数据库可移植 . 如果你移动到北美并开始使用设置为（比如）中央时间的MySQL，那么数据库中的值似乎已经移动了几个小时 . 我有一个我继承的数据库的问题，当我将它从美国东海岸移到西海岸时，使用服务器的本地时间，没想过检查MySQL是否从属于机器的区域...

    long t = 1351382400000; // the timestamp in UTC
String insert = "INSERT INTO my_table (timestamp) VALUES (?)";
PreparedStatement stmt = db.prepareStatement(insert);
java.sql.Timestamp date = new Timestamp(t);
stmt.setTimestamp(1, date);
stmt.executeUpdate();

.....

TimeZone timezone = TimeZone.getTimeZone("MyTimeZoneId");
Calendar cal = java.util.Calendar.getInstance(timezone);
String select = "SELECT timestamp FROM my_table";
// some code omitted....
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
   java.sql.Timestamp ts = rs.getTimestamp(1);
   cal.setTimeInMillis(ts.getTime());
   System.out.println("date in db: " + cal.getTime());
}
    回复于
  • 2

    如果你想要使用时区，您可以将列读作UTC .

    ZonedDateTime zdt = ZonedDateTime.of(rs.getTimestamp(1).toLocalDateTime(), ZoneOffset.UTC);

    接下来，您可以更改为您想要的任何时区：

    zdt = zdt.withZoneSameInstant(ZoneId.of(
            TARGET_ZONE));

    如果您只想阅读日期而且根本不关心区域：

    LocalDateTime ldt = rs.getTimestamp(1).toLocalDateTime()

    您将获得没有时区的LocalDateTime .

    如果必须返回java.util.Date，请使用：

    Date.from(ldt.atZone(ZoneOffset.UTC).toInstant());
    回复于
  • 2

    不要考虑转换或调整时区 . 不要考虑mysql用于存储时间戳的TZ或者像这样的任何想法 . 那些东西已经处理好了 . 您必须处理三件事：INPUT，OUTPUT和错误 .

    INPUT

    当用户输入没有明确时区的日期（在表单中）时，您必须知道他打算使用什么TZ . 您可以使用设置了时区的SimpleDateFormat对象来解决此问题 . 您不必转换输入日期，您必须正确“解释”它 . 一旦您有正确解释的日期或时间戳，您就完成了输入 .

    输入不仅是用户输入，还包括配置文件 .

    OUTPUT

    和这里一样 . 忘记TZ有你的Date对象和时间戳都没有，它们只是自纪元以来的毫秒 . 您必须将日期格式设置为用户期望的TZ，以便他们了解它们 .

    Bugs

    您可能在与TZ相关的代码中有错误，但库也可能有它们！

    我注意到mysql java驱动程序无法将客户端时区传递给服务器 . 此命令 s.executeUpdate ("set time_zone='+xx:yy'"); 是解决方法，但您使用它是错误的 . 在插入和查询之前，您必须告诉服务器客户端正在使用的TZ . 变量存储在会话中 . 也许您可以在连接池配置上自动化它 . 这是必需的，因此服务器知道客户端需要使用什么TZ来读取或写入 . This is not dependent on server TZ . 它并不意味着"store this date in UTC"，它的意思是"this date I am giving to you is UTC"和"Send me result sets in UTC" . 无论你使用Date类和它的内部TZ，驱动程序都会将其搞砸，你需要设置该会话变量 .

    默认情况下，它假定客户端TZ与服务器TZ相同，因此您不必担心它，因为您说它们是相同的 .

    回复于

相关问题