MySQL - 如何使用正确的时区存储时间?(来自Java) [英] MySQL - how to store time with correct timezone? (from Java)

问题描述

我使用的是 MySQL 5.0.我需要将日期时间信息存储在一列中.我的意思是使用 DATETIME 或 TIMESTAMP 列类型.但我对夏季和冬季时间的变化有疑问.

I'm using MySQL 5.0. I need to store date-time information in one column. I mean to use DATETIME or TIMESTAMP column type. But I have problem with summer-winter time change.

我的时区是 CET.夏季我们使用夏令时(CEST，GMT+2)，冬季我们使用冬季时间(CET，GMT+1).在夏令时变为冬令时的那一天(今年 2012 年是 10 月 28 日)我们有两次凌晨 2:00 的时间.这一天的时间顺序是:

My timezone is CET. In summer we use summer-time (CEST, it is GMT+2) and in winter we use winter-time (CET, it is GMT+1). In the day when the summer time changes into winter time (in this year 2012 it was on 28th of October) we have time 2:00AM two-times. The sequence of time in this day is:

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

因此，如果我有时间戳/日期时间2012-10-28 02:00:00"，我无法正确判断该时间是代表夏季时间还是冬季时间凌晨 2:00.

So if I have timestamp/datetime '2012-10-28 02:00:00' I'm not able to say correctly if this time represents 2:00AM in summer time or in winter time.

我有两个不同的 Java 日期:

I have two different Java dates:

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

当我使用标准时间戳/日期时间格式(即'yyyy-MM-dd HH:mm:ss')将它们存储在数据库中时，它们都存储相同的数据'2012-10-28 02:00:00'.但是当我从数据库中将这些值返回到日期变量中时，我得到了两个相同的日期.所以输入日期不同，但输出日期相同.
如果我使用 FROM_UNIXTIME 函数存储日期值:FROM_UNIXTIME(1351382400) 和 FROM_UNIXTIME(1351386000)，也会发生同样的情况.值作为相等值存储在数据库列(DATETIME 或 TIMESTAMP 类型)中.因此，当我将这些值放入 Java 的 Date 对象中时，我会再次获得两个相等的日期(在冬季).

And when I store them in the database using standard timestamp/datetime format (ie. 'yyyy-MM-dd HH:mm:ss'), both of them store the same data '2012-10-28 02:00:00'. But when I get these values from database back into Date variables I get two same dates. So input dates were different but output dates are equal.
The same occurs if I use the FROM_UNIXTIME function to store date value: FROM_UNIXTIME(1351382400) and FROM_UNIXTIME(1351386000). Values are stored in the database column (DATETIME or TIMESTAMP type) as equal values. So when I get these values into Java's Date object I get two equal dates again (in winter time).

有什么方法可以在 MySQL 中存储时区，或者如何处理 DATETIME/TIMESTAMP 列中的时区信息?
当然，我可以使用 unix-timestamp 将 BIGINT 值存储在数据库中.但我想知道是否有任何方法可以解决任何 MySQL 日期时间类型的问题.

Is there any way to store the timezone in MySQL, or how to handle with timezone information within DATETIME/TIMESTAMP columns?
Of course I can store BIGINT values in the database with unix-timestamp. But I'm wondering if there's any way how to solve this problem with any MySQL date-time type.

感谢任何帮助或技巧... :)

Any help or trick is appreciated ... :)

非常感谢.

Honza (sporak)

Honza (sporak)

编辑#1:
如果我尝试存储时间戳值，然后将此时间戳值提取到 java 的 Date 中，我会再次得到错误的 Date.假设我有一个带有 TIMESTAMP 列的简单表.我以这种方式将数据存储在此表中:

EDIT #1:
If I tried to store timestamp values and then fetch this timestamp values into java's Date, I get faulty Date again. Let's say I have simple table with TIMESTAMP column. I store data in this table this way:

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 Date 对象中时，我得到两个相同的日期，显示 2:00 CET.更重要的是 - 当我在 MySQL 中获取这些行时，我在 MySQL 中再次得到错误的值:

When I fetch these rows into java Date object, I'm getting two same dates that shows 2:00 CET. And what's more - when I fetch these rows in MySQL I get wrong values again in MySQL:

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

所以 TIMESTAMP 在我看来没什么用.

So TIMESTAMP seems to me to be little useless.

推荐答案

在我看来，最好的办法是告诉 MySQL 使用 GMT 并处理应用程序代码中的所有本地时间问题，而不是数据库.数据库中的值始终是 GMT，句号，这是明确的.正如您所说，通过调整夏令时(夏令时)，您最终可以在数据库中得到相同的值，对于我们人类来说，这两个时间是不同的.

Your best bet, in my view, is to tell MySQL to use GMT and handle all local time issues in your application code, not your database. The values in the database would always be GMT, full stop, which is unambiguous. As you say, with daylight savings time (summer time) adjustments, you can end up with the same value in your database for what is, to us humans, two different times.

这也使数据库具有可移植性.如果您搬到北美并开始使用设置为(例如)中央时间的 MySQL，突然之间，您的数据库中的值似乎已经移动了几个小时.我继承的数据库使用服务器的本地时间时遇到了这个问题:当我将它从美国东海岸移动到西海岸时，没有考虑检查 MySQL 是否设置为使用机器的区域......

This also makes the database portable. If you move to North America and start using MySQL set to (say) Central time, all of a sudden the values in your database seem to have moved several hours. I had that issue with a database I inherited which was using the server's local time: When I moved it from the east coast of the U.S. to the west coast, not having thought to check whether MySQL was set to use the machine's zone...

这篇关于MySQL - 如何使用正确的时区存储时间?(来自Java)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！