MySQL Forums
Forum List  »  General

Re: Probable timezone problems: can anyone confirm and offer a solution?
Posted by: Rick James
Date: March 19, 2014 05:20PM

> On March 9, all transactions with a timestamp between 2Am and 3AM were not stored properly.

Guess when Daylight Savings Time changes! (I hate DST.)

You should probably switch to UTC for all records. It _may be_ too late to fix the existing records.

These 4 things (at least) are critical:
mysql> SHOW VARIABLES LIKE '%zo%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| system_time_zone | Pacific Daylight Time |
| time_zone        | SYSTEM                |
+------------------+-----------------------+
But those may not be the "best" values for you.

Note that by saying "SYSTEM", it is depending on the Operating System for the timezone conversion. That is the third variable in the equation.

With all the setting 'right', TIMESTAMPs will be stored in UTC (no DST involved) and converted as you INSERT / SELECT from / to the timezone of the _client_. That is (I think) a 4th variable.

Sorry, I don't have exactly what you need to fix your data, nor to set all the settings to make future times correct.

If you are using DATETIME, think of that as taking a picture of the clock on the wall, and that the clock is adjusted twice a year for DST. DATETIME is (usually) a bad choice for anything that needs values 24/7/365. One hour a year there will be a gap (illegal values), another time of the year, the 'clock' will jump backward an hour, thereby invalidating any comparision of some dates for ">" and "<".

What to do? I guess I would plan on leaving the old data as messed up, plan a switchover time, and become fully UTC for the future.

If you want to experiment (on a separate server) with "fixing" the data, here is one clue:
SELECT of a TIMESTAMP or DATETIME will produce a string that has no hint of timezone. You can turn around and INSERT that into a column with the same or different datatype to trick it into converting differently. Or you can work with any of several datetime functions, possibly writing a one-time patch to fix all datetimes before 3/9, 2am one way, all datetimes after that another way -- be sure to have the system VARIABLES set the new way, too.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.