如何将 java.util.Date 存储到 UTC/GMT 时区的 MySQL 时间戳字段中？

How to store a java.util.Date into a MySQL timestamp field in the UTC/GMT timezone?

提问by Behrang

I used a new Date() object to fill a field in a MySQL DB, but the actual value stored in that field is in my local timezone.

我使用了一个新的 Date() 对象来填充 MySQL 数据库中的一个字段，但该字段中存储的实际值在我的本地时区中。

How can I configure MySQL to store it in the UTC/GMT timezone?

如何配置 MySQL 以将其存储在 UTC/GMT 时区？

I think, configuring the connection string will help but I don't know how. There are many properties in the connection string like useTimezone, serverTimzone, useGmtMillisForDatetimes, useLegacyDatetimeCode, ...

我认为，配置连接字符串会有所帮助，但我不知道如何。连接字符串中有许多属性，例如 useTimezone、serverTimzone、useGmtMillisForDatetimes、useLegacyDatetimeCode...

采纳答案by MiniQuark

The short answer is:

简短的回答是：

• add "default-time-zone=utc" to my.cnf
• in your code, always "think" in UTC, except when displaying dates for your users

• when getting/setting dates or timestamps with JDBC, always use the Calendar parameter, set to UTC:

  resultset.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

• either synchronize your servers with NTP, or rely only on the database server to tell you what time it is.

• 将“default-time-zone=utc”添加到 my.cnf
• 在您的代码中，始终以 UTC 时间“思考”，除非为您的用户显示日期

• 使用 JDBC 获取/设置日期或时间戳时，始终使用 Calendar 参数，设置为 UTC：

  结果集.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

• 要么将您的服务器与 NTP 同步，要么仅依靠数据库服务器来告诉您现在是什么时间。

---

---

The long answer is this:

长答案是这样的：

When dealing with dates and timezones in any database and with any client code, I usually recommend the following policy:

在处理任何数据库和任何客户端代码中的日期和时区时，我通常推荐以下策略：

1. Configure your database to use UTC timezone, instead of using the server's local timezone (unless it is UTC of course).

   • How to do so depends on your database server. Instructions for MySQL can be found here: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html. Basically you need to write this in my.cnf: default-time-zone=utc

   • This way you can host your database servers anywhere, change your hosting location easily, and more generally manipulate dates on your servers without any ambiguity.

   • If you really prefer to use a local timezone, I recommend at least turning off Daylight Saving Time, because having ambiguous dates in your database can be a real nightmare.
     • For example, if you are building a telephony service and you are using Daylight Saving Time on your database server then you are asking for trouble: there will be no way to tell whether a customer who called from "2008-10-26 02:30:00" to "2008-10-26 02:35:00" actually called for 5 minutes or for 1 hour and 5 minutes (supposing Daylight Saving occurred on Oct. 26th at 3am)!

2. Inside your application code, always use UTC dates, except when displaying dates to your users.

   • In Java, when reading from the database, always use:

   Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

   • If you do not do this, the timestamp will be assumed to be in your local TimeZone, instead of UTC.

3. Synchronize your servers or only rely on the database server's time

   • If you have your Web server on one server (or more) and your database server on some other server, then I strongly recommend you synchronize their clocks with NTP.

   • OR, only rely on one server to tell you what time it is. Usually, the database server is the best one to ask for time. In other words, avoid code such as this:

   preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = ? WHERE [...]");
   java.util.Date now = new java.util.Date(); // local time! :-(
   preparedStatement.setTimestamp(1, new Timestamp(now.getTime()));
   int result = preparedStatement.execute();

   • Instead, rely on the database server's time:

   preparedStatement = connection.prepareStatement("UPDATE my_table SET my_time = NOW() WHERE [...]");
   int result = preparedStatement.execute();

1. 将您的数据库配置为使用 UTC 时区，而不是使用服务器的本地时区（当然，除非它是 UTC）。

   • 如何执行取决于您的数据库服务器。MySQL 的说明可以在这里找到：http: //dev.mysql.com/doc/refman/5.0/en/time-zone-support.html。基本上你需要在 my.cnf 中写这个：default-time-zone=utc

   • 通过这种方式，您可以在任何地方托管您的数据库服务器，轻松更改您的托管位置，并且更一般地在您的服务器上操纵日期而不会产生任何歧义。

   • 如果您真的更喜欢使用本地时区，我建议至少关闭夏令时，因为数据库中的日期不明确可能是一场真正的噩梦。
     • 例如，如果您正在构建电话服务并且您在数据库服务器上使用夏令时，那么您是在自找麻烦：将无法判断客户是否从“2008-10-26 02:30 :00" 到 "2008-10-26 02:35:00" 实际上调用了 5 分钟或 1 小时 5 分钟（假设夏令时发生在 10 月 26 日凌晨 3 点）！

2. 在您的应用程序代码中，始终使用 UTC 日期，除非向您的用户显示日期。

   • 在 Java 中，从数据库读取时，始终使用：

   时间戳 myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

   • 如果您不这样做，时间戳将被假定为您当地的时区，而不是 UTC。

3. 同步您的服务器或仅依赖数据库服务器的时间

   • 如果您的 Web 服务器在一台（或多台）服务器上，而数据库服务器在其他服务器上，那么我强烈建议您将它们的时钟与 NTP 同步。

   • 或者，仅依靠一台服务器来告诉您现在几点了。通常，数据库服务器是最好的询问时间的服务器。换句话说，避免这样的代码：

   prepareStatement = connection.prepareStatement("UPDATE my_table SET my_time = ? WHERE [...]");
   java.util.Date now = new java.util.Date(); // 当地时间！:-(
   prepareStatement.setTimestamp(1, new Timestamp(now.getTime()));
   int result = prepareStatement.execute ();

   • 相反，依靠数据库服务器的时间：

   prepareStatement = connection.prepareStatement("UPDATE my_table SET my_time = NOW() WHERE [...]");
   int 结果 = PreparedStatement.execute();

Hope this helps! :-)

希望这可以帮助！:-)

回答by Powerlord

Well, if we're talking about using PreparedStatements, there's a form of setDatewhere you can pass in a Calendar set to a particular time zone.

好吧，如果我们谈论的是使用PreparedStatements，则有一种setDate 形式，您可以在其中传递设置为特定时区的 Calendar。

For instance, if you have a PreparedStatement named stmt, a Date named date, and assuming the date is the second parameter:

例如，如果您有一个名为 stmt 的 PreparedStatement，一个名为 date 的 Date，并假设日期是第二个参数：

stmt.setDate(2, date, Calendar.getInstance(TimeZone.getTimeZone("GMT")));

The best part is, even if GMT is an invalid time zone name, it still returns the GMT time zone because of the default behavior of getTimeZone.

最好的部分是，即使 GMT 是无效的时区名称，由于 getTimeZone 的默认行为，它仍然返回 GMT 时区。

回答by user38051

A java Date is timezone agnostic. It ALWAYS represents a date in GMD(UTC) in milliseconds from the Epoch.

Java 日期与时区无关。它始终表示以 GMD(UTC) 表示的日期（以毫秒为单位）。

The ONLY time that a timezone is relevant is when you are emitting a date as a string or parsing a data string into a date object.

时区相关的唯一时间是当您将日期作为字符串发出或将数据字符串解析为日期对象时。

回答by Ted Bigham

MiniQuark gave some good answers for databases in general, but there are some MySql specific quirks to consider...

MiniQuark 一般为数据库提供了一些很好的答案，但有一些 MySql 特定的怪癖需要考虑......

Configure your database to use UTC timezone

配置您的数据库以使用 UTC 时区

That actually won't be enough to fix the problem. If you pass a java.util.Date to MySql as the OP was asking, the MySql driver will change the valueto make it look like the same local time in the database's time zone.

这实际上不足以解决问题。如果您按照 OP 的要求将 java.util.Date 传递给 MySql，则 MySql 驱动程序将更改该值，使其看起来与数据库时区中的本地时间相同。

Example: Your database if configured to UTC. Your application is EST. You pass a java.util.Date object for 5:00 (EST). The database will convert it to 5:00 UTC and store it. Awesome.

示例：您的数据库（如果配置为 UTC）。您的申请是 EST。您为 5:00 (EST) 传递一个 java.util.Date 对象。数据库会将其转换为 5:00 UTC 并存储。惊人的。

You'd have to adjust the time before you pass the data to "undo" this automatic adjustment. Something like...

在传递数据以“撤消”此自动调整之前，您必须调整时间。就像是...

long originalTime = originalDate.getTime();
Date newDate = new Date(originalTime - TimeZone.getDefault().getOffset(originalTime));
ps.setDate(1, newDate);

Reading the data back out requires a similar conversion..

读回数据需要类似的转换。

long dbTime = rs.getTimestamp(1).getTime();
Date originalDate = new Date(dbTime + TimeZone.getDefault().getOffset(dbTime));

Here's another fun quirk...

这是另一个有趣的怪癖......

In Java, when reading from the database, always use: Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

在 Java 中，从数据库读取时，始终使用： Timestamp myDate = resultSet.getTimestamp("my_date", Calendar.getInstance(TimeZone.getTimeZone("UTC")));

MySql actually ignores that Calendar parameter. This returns the same value regardless of what calendar you pass it.

MySql 实际上忽略了该 Calendar 参数。无论您传递什么日历，这都会返回相同的值。

回答by Chris M.

I had the same problem, and it took me nearly a day to track down. I'm storing DateTime columns in MySQL. The RDS instance, running in Amazon's Cloud, is correctly set to have a UTC timestamp by default.

我遇到了同样的问题，我花了将近一天的时间才找到。我在 MySQL 中存储 DateTime 列。默认情况下，在 Amazon 云中运行的 RDS 实例正确设置为具有 UTC 时间戳。

The Buggy Code is:

越野车代码是：

    String startTime = "2013-02-01T04:00:00.000Z";
    DateTime dt = ISODateTimeFormat.dateTimeParser().parseDateTime(startTime);            

    PreparedStatement stmt = connection.prepareStatement(insertStatementTemplate);

    Timestamp ts = new Timestamp(dt.getMillis());
    stmt.setTimestamp(1, ts, Calendar.getInstance(TimeZone.getTimeZone("UTC"))); 

In the code above, the ".setTimestamp" call would NOT take the date as a UTC date!

在上面的代码中，“.setTimestamp”调用不会将日期作为 UTC 日期！

After hours of investigating, this turns out to be a known bug in the Java / MySQL Driver. The call to setTimestamp listerally just ignores the Calendar parameter.

经过数小时的调查，结果证明这是 Java / MySQL 驱动程序中的一个已知错误。对 setTimestamp 的调用只是忽略了 Calendar 参数。

To fix this add the "useLegacyDatetimeCode=false" to your database URI.

要解决此问题，请将“ useLegacyDatetimeCode=false”添加到您的数据库 URI。

private final static String DatabaseName =
  "jdbc:mysql://foo/?useLegacyDatetimeCode=false";

As soon as i did that, the date stored in the MySQL database was in proper UTC form, rather than in the timezone of my local workstation.

一旦我这样做了，存储在 MySQL 数据库中的日期就是正确的 UTC 格式，而不是我本地工作站的时区。