MySQL-如何使用正确的时区存储时间?(来自Java)

Question

我正在使用mySQL 5.0。我需要将日期时间信息存储在一列中。我的意思是使用DATETIME或TIMESTAMP列类型。但是我的夏季时间更改有问题。

我的时区是CET。在夏季，我们使用夏季时间（CEST，是GMT 2），在冬季，我们使用冬季（CET，是GMT 1）。在夏季变化为冬季时间的那一天（今年2012年是10月28日），我们有时间2:00 AM两次。这一天的时间顺序为：

... -> 1:00 CEST -> 2:00 CEST -> 2:00 CET (= 3:00 CEST) -> 3:00 CET -> ...

所以，如果我有时间戳/日托时间'2012-10-28 02:00:00'我无法正确说明这段时间在夏季或冬季时间上午2:00。

我有两个不同的Java日期：

Date d1 = new Date(1351382400000); // 2:00 CEST (summer-time)
Date d2 = new Date(1351386000000); // 2:00 CET (winter-time)

并且，当我使用标准时间戳/DateTime格式（即'yyyy-MM-dd HH:mm:ss'）将它们存储在数据库中时，它们都存储相同的数据'2012-10-28 02:00:00'。但是，当我将这些值从数据库重新获得日期变量时，我会得到两个相同的日期。因此输入日期不同，但输出日期相等。
如果我使用FROM_UNIXTIME函数存储日期值：FROM_UNIXTIME(1351382400)和FROM_UNIXTIME(1351386000)，也会发生同样的情况。值存储在数据库列（DateTime或Timestamp Type）中，作为等值。因此，当我将这些值纳入Java的日期对象时，我再次获得两个相等的日期（在冬季）。

有什么方法可以将时区存储在MySQL中，或者如何在DateTime/Timestamp列中处理时区信息？
当然，我可以使用Unix-Timestamp将BigInt值存储在数据库中。但是我想知道是否有任何方法可以通过任何MySQL日期时间类型解决此问题。

任何帮助或技巧都受到赞赏...：）

非常感谢。

honza（Sporak）

编辑＃1：
如果我尝试存储时间戳值，然后将此时间戳值获取到Java的日期中，我会再次获得错误的日期。假设我有带时间戳列的简单表。我以这种方式将数据存储在此表中：

mysql> INSERT INTO `tab` (timestamp_column) VALUES 
          (FROM_UNIXTIME(1351382400)),  // 2:00 CEST (summer-time)
          (FROM_UNIXTIME(1351386000));  // 2:00 CET (winter-time)
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

当我将这些行获取到Java日期对象中时，我得到的两个日期显示了2:00 CET。更重要的是 - 当我在mysql中获取这些行时，我在mysql中再次获得错误的值：

mysql> SELECT UNIX_TIMESTAMP(timestamp_column) from tab;
+--------------------+
| UNIX_TIMESTAMP(ts) |
+--------------------+
|         1351386000 | 
|         1351386000 | 
+--------------------+
2 rows in set (0.00 sec)

所以在我看来，时间戳似乎没用。

Answer 1

您最好的选择是告诉MySQL使用GMT并处理应用程序代码中的所有本地时间问题，而不是数据库。数据库中的值始终是GMT，完全停止，这是明确的。正如您所说，随着日光节省时间（夏季时间）调整，您可以在数据库中获得相同的价值，而对我们来说，这是两个不同的时间。

这也使数据库便携式。如果您搬到北美并开始使用MySQL设置为（例如）中心时间，那么数据库中的值似乎已经移动了几个小时。我在使用服务器当地时间的数据库中遇到了这个问题：当我将其从美国的东海岸移到西海岸，没有考虑检查MySQL是否设置了机器的区域...

Answer 2

DATE， TIME， YEAR和 DATETIME所有存储了相应的日期/时间指示，就像拍摄日历和/或clockface的照片一样：记录设置时钟的时区，因此它们不代表任何特定的时刻。它们对于在特定 local 日期或特定 local 时间上发生的事件有用（即不考虑时区）。

TIMESTAMP存储一个UTC时间戳（自UNIX时期以来为秒），必要时执行向/从会话的time_zone进行转换：它代表了一个精确的，明确的时间时刻。这就是你想要的；只需确保适当设置会话变量（使用SET SESSION time_zone = ...）。

请参阅MySQL Server时区支持有关更多信息。

Answer 3

您可以在数据库中存储之前将日期转换为UTC ，然后在从数据库中读取时转换回您自己的时区。

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());
}