MySQL - how to store time with correct timezone? (

问题:

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.

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 -> ...

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.

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)

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).

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 ... :)

Thanks a lot.

Honza (sporak)

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

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)

So TIMESTAMP seems to me to be little useless.

回答1:

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.

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 slaved to the machine's zone...

回答2:

DATE, TIME, YEAR and DATETIME all store the respective date/time indications, just like taking a photograph of a calendar and/or clockface: they do not record the timezone in which the clock was set, so they don't represent any particular moment in time. They're useful for events that occur on a specific local date or at a specific local time (i.e. irrespective of timezone).

TIMESTAMP stores a UTC timestamp (as seconds since the UNIX epoch), performing conversion to/from your session's time_zone as necessary: it represents a precise, unambiguous moment in time. This is what you want; just be sure to set your session variable as appropriate (with SET SESSION time_zone = ...).

See MySQL Server Time Zone Support for more information.

回答3:

You could convert the date to UTC before storing in the database, then convert back to your own time zone when reading from the database.

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