如何使用Java在数据库连接中更改MySQL时区? [英] How to change MySQL timezone in a database connection using Java?

问题描述

MySQL的时区为"GMT + 8"，而Tomcat的时区为"GMT".当我将 datetime 保存到数据库中时，一切似乎都正常，但是当我检查数据库中的 datetime 值时，我看到了"GMT"值.

MySQL runs with timezone "GMT+8", but Tomcat with "GMT". When I save datetime to my database, everything seems to be OK, but when I check the datetime value in the database, I see the "GMT" value.

另外，当我尝试从数据库中获取值时，值已更改，似乎数据库中的值被视为"GMT + 8"，因此Java将值更改为"GMT".

Also when I try get the value from the database the value is changed, seems like the value in the database is taken as "GMT+8", so Java changes the value to "GMT".

我已经这样设置了连接URL:

I have set the connection URL like this:

useTimezone=true&serverTimezone=GMT

但它不起作用.

推荐答案

useTimezone是较旧的解决方法. MySQL团队最近才重写了setTimestamp/getTimestamp代码，但只有在设置连接参数useLegacyDatetimeCode = false且使用的是最新版本的mysql JDBC连接器时，才能启用它.例如:

useTimezone is an older workaround. MySQL team rewrote the setTimestamp/getTimestamp code fairly recently, but it will only be enabled if you set the connection parameter useLegacyDatetimeCode=false and you're using the latest version of mysql JDBC connector. So for example:

String url =
 "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false

如果下载mysql-connector源代码并查看setTimestamp，则很容易看到正在发生的事情:

If you download the mysql-connector source code and look at setTimestamp, it's very easy to see what's happening:

如果使用旧日期时间代码= false，则调用newSetTimestampInternal(...).然后，如果传递给newSetTimestampInternal的Calendar为NULL，则将日期对象格式化为数据库的时区:

If use legacy date time code = false, newSetTimestampInternal(...) is called. Then, if the Calendar passed to newSetTimestampInternal is NULL, your date object is formatted in the database's time zone:

this.tsdf = new SimpleDateFormat("''yyyy-MM-dd HH:mm:ss", Locale.US);
this.tsdf.setTimeZone(this.connection.getServerTimezoneTZ());
timestampString = this.tsdf.format(x);

日历为空非常重要-因此请确保您使用的是

It's very important that Calendar is null - so make sure you're using:

setTimestamp(int,Timestamp).

...不是setTimestamp(int，Timestamp，Calendar).

... NOT setTimestamp(int,Timestamp,Calendar).

现在应该很清楚这是如何工作的.如果您使用java.util.Calendar在America/Los_Angeles(或您想要的任何时区)中构造日期:2011年1月5日3:00 AM，并调用setTimestamp(1，myDate)，则将使用您的日期，请使用SimpleDateFormat在数据库时区中对其进行格式化.因此，如果您的数据库位于America/New_York，它将构造要插入的字符串'2011-01-05 6:00:00'(因为NY比LA提前3小时).

It should be obvious now how this works. If you construct a date: January 5, 2011 3:00 AM in America/Los_Angeles (or whatever time zone you want) using java.util.Calendar and call setTimestamp(1, myDate), then it will take your date, use SimpleDateFormat to format it in the database time zone. So if your DB is in America/New_York, it will construct the String '2011-01-05 6:00:00' to be inserted (since NY is ahead of LA by 3 hours).

要获取日期，请使用getTimestamp(int)(不带日历).它将再次使用数据库时区来建立日期.

To retrieve the date, use getTimestamp(int) (without the Calendar). Once again it will use the database time zone to build a date.

注意:现在Web服务器的时区已经完全不相关了！如果未将useLegacyDatetimecode设置为false，则使用Web服务器的时区进行格式化-会增加很多混乱.

Note: The webserver time zone is completely irrelevant now! If you don't set useLegacyDatetimecode to false, the webserver time zone is used for formatting - adding lots of confusion.

注意:

MySQL可能抱怨服务器时区不明确.例如，如果您的数据库设置为使用EST，则Java中可能存在多个EST时区，因此您可以通过准确地告诉它mysql-connector的数据库时区来为mysql-connector澄清这一点:

It's possible MySQL my complain that the server time zone is ambiguous. For example, if your database is set to use EST, there might be several possible EST time zones in Java, so you can clarify this for mysql-connector by telling it exactly what the database time zone is:

String url =
 "jdbc:mysql://localhost/mydb?useLegacyDatetimeCode=false&serverTimezone=America/New_York";

只有在抱怨时才需要这样做.

You only need to do this if it complains.

这篇关于如何使用Java在数据库连接中更改MySQL时区?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！