If I am moving databases between servers datetime/timestamp fields with different timezones; I have been doing:
mysqldump --tz-utc=false database_name;
then reloading it on the server with different timezone. This appears to work and the dates are correct.
It seems that according to sql manual I shouldn't have to do this:
This option enables
TIMESTAMPcolumns to be dumped and reloaded between servers in different time zones.
mysqldumpsets its connection time zone to UTC and adds
SET TIME_ZONE='+00:00'to the dump file. Without this option,
TIMESTAMPcolumns are dumped and reloaded in the time zones local to the source and destination servers, which can cause the values to change if the servers are in different time zones.
--tz-utcalso protects against changes due to daylight saving time.
--tz-utcis enabled by default. To disable it, use
I guess I don't understand exactly how this works.
What is the proper way to move data between servers that have different timezones?
EDIT to show that --tz-utc=false is a real statement
cmuench@cmuench:~$ mysqldump --tz-utc=false pos > 1.sql; cmuench@cmuench:~$ mysqldump --tz-utc=true pos > 2.sql; cmuench@cmuench:~$ mysqldump --tz-utc pos > 3.sql; cmuench@cmuench:~$ diff 1.sql 2.sql 10a11,12 > /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; > /*!40103 SET TIME_ZONE='+00:00' */; 2903a2906 > /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; 2913c2916 < -- Dump completed on 2017-07-12 0:29:54 --- > -- Dump completed on 2017-07-12 0:29:57 cmuench@cmuench:~$ diff 2.sql 3.sql 2916c2916 < -- Dump completed on 2017-07-12 0:29:57 --- > -- Dump completed on 2017-07-12 0:30:01