如何使用 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);
Calendar 为 null 非常重要 - 所以请确保您正在使用:
It's very important that Calendar is null - so make sure you're using:
setTimestamp(int,Timestamp).
... NOT setTimestamp(int,Timestamp,Calendar).
... NOT setTimestamp(int,Timestamp,Calendar).
现在应该很清楚这是如何工作的.如果您使用 java.util.Calendar 构建日期:2011 年 1 月 5 日凌晨 3:00 在 America/Los_Angeles(或您想要的任何时区)并调用 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.
注意:网络服务器时区现在完全无关紧要了！如果您不将 useLegacyDatetimecode 设置为 false，则网络服务器时区用于格式化 - 增加了很多混乱.
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 数据库时区是什么来澄清这一点:
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屋！
