Date value in mysql tables changes while exporting mysql db

Question

I am exporting mysql table to setup it on live, but while exporting DB I noticed that my date column value is changing.. If it was "2007-06-11 00:00:00" earlier then after export it is now changed to "2007-06-10 18:30:00",

why this is so?
anybody have idea about this?

Answer 1

Bug #13052 existed in versions of MySQL prior to 5.0.15, in which dump files expressed TIMESTAMP columns in the server's timezone but did not include a SET TIME_ZONE command to ensure anyone (or any subsequent server) reading the dump file understood that; without such a command, receiving servers assume that any TIMESTAMP values are in its default timezone.

Therefore a transfer between servers in timezones offset by 18:30 (e.g. from South Australia to California) would lead to the behaviour you observe.

Solutions to this problem, in some vague order of preference, include:

1. Upgrade the version of mysqldump on the original server to 5.0.15 or later (will result in the dumpfile expressing all TIMESTAMP values in UTC, with a suitable SET TIME_ZONE statement at the start);

2. Prior to export (or import), change the global time_zone variable on the source (or destination) server, so that it matches the setting on the other server at the time of import (or export):

   SET GLOBAL time_zone = 'America/Los_Angeles'; -- ('Australia/Adelaide')
   

3. UPDATE the data after the fact, applying MySQL's CONVERT_TZ() function:

   UPDATE my_table
   SET    my_column = CONVERT_TZ(
                        my_column,
                        'America/Los_Angeles',
                        'Australia/Adelaide'
                      );
   

If using either solution 2 or solution 3, beware to use the exact timezone of the relevant server's time_zone variable, in such a manner as to include any daylight savings time. However, note that as documented under MySQL Server Time Zone Support: "Named time zones can be used only if the time zone information tables in the mysql database have been created and populated." The article goes on to explain how to create and populate the time zone information tables.