[SOLVED] datetime values offset when restoring from db dump?

sneakyimp · 10-04-2009, 01:26 PM

I have a pretty darn large database dump. I can see there are a few records in it with datetimes all around 15:00 in the afternoon:

Code:

(135312,302028,4,'2009-09-28 15:00:19','Jimmy'),
(135313,302029,4,'2009-09-28 15:08:57','formosa'),
(135314,302030,4,'2009-09-28 15:09:03','tammystrawn');

I have used the mysql command line to restore this dump into a clean database:

Code:

mysql -h domain.com -u user_name -p database_name < backup.sql

When I look in the database after restoring from the dump, i see those records in the correct table, but the time values are different. They are 08:00:19, 08:08:57, and 08:09:03, respectively.

Even more confusingly, I restored the same exact dump file on a different machine and got still different time values around 11:00.

WHAT THE HECK? What could possibly be causing these datetime values to be offset by several hours?

jlinkels · 10-04-2009, 06:57 PM

Most likely the database from where the data was dumped used a different time zone setting than the one you are importing in.

Check both the setting of the time zone of the server, which is used by default by MySQL, or the setting of the global timezone in MySQL. In addition, each client can set a timezone for a session.

Code:

SELECT @@global.time_zone, @@session.time_zone;

http://dev.mysql.com/doc/refman/5.1/...e-support.html

jlinkels

sneakyimp · 10-05-2009, 01:59 PM

I have learned that this problem happens with timestamp fields because mysqldump will attempt to convert them to UTC by default. This seems like it would be a good way to ensure that the time is correct on any machine, but something was not working. I am now using mysqldump with the --skip-tz-utc option and this appears to result in correct time values.