3

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.

Improve this question

3 Answers 3

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

You can, and probably should, hide the use of GMT from users. Convert according to an appropriate locale for display. "Appropriate" is a non-trivial issue. Location of server? Location of user? Location of business HQ?
@PatriciaShanahan: Right, as I said, handle it in application code, so the data in the database isn't ambiguous.
@T.J.Crowder - I know us English love GMT (I am English but loves Scotland!) but GMT? Why not UTC - Keeps the Yanks happy!?
@EdHeal: More than once writing the above I thought "I should probably say UTC, or GMT (UTC), or something," but ultimately thought it might be confusing. As the OP was using timezone indicators, and GMT is the usual timezone indicator for UTC...
UTC is not the same as GMT: it is based on atomic time and includes leap seconds.
|
3

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.

Improve this answer

11 Comments

See my edit #1 in question. Timestamp seems to me to be useless.
@sporak: Your problem there is not with TIMESTAMP, but FROM_UNIXTIME() which converts into a DATETIME in the session's time_zone. Ensure that your session time_zone is set appropriately, as stated in my answer above.
This I don't understand a little. 1351382400 is unique moment. When I insert this timestamp and immidiately I fetch different value 1351386000. But maybe it works in that way, that FROM_UNIXTIME() converts timestamp into string '2012-10-28 02:00:00' and this value is of course ambigous. If this works this way, how to store into TIMESTAMP column value of 1351382400 timestamp?
I think that time_zone affect only conversion of inserted date. But when I'm storing unique GMT moment (1351382400) I don't need any time-conversion. I'm giving unique timestamp, so I think time_zone won't take any affect. I have feeling that MySQL changes timestamp 1351382400 into 1351386000 and I have no possibility to affect it.
Your previous comment above is correct, "FROM_UNIXTIME() converts timestamp into string '2012-10-28 02:00:00' and this value is of course ambiguous." If you already have a timestamp and want to insert without any conversion, just store it as an integer. If you want MySQL to handle timezone issues for you, make sure you set time_zone appropriately.
|
0

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

1 Comment

i am using this to download financial tick data from various time zones, then use them in trading simulations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.