patternsqlMinor
mysqldump between servers with different timezones (tz-utc)
Viewed 0 times
timezonesserverswithutcmysqldumpdifferentbetween
Problem
If I am moving databases between servers datetime/timestamp fields with different timezones; I have been doing:
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:
From manual:
This option enables
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
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:
From manual:
--tz-utcThis option enables
TIMESTAMP columns to be dumped and reloaded between servers in different time zones. mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without this option, TIMESTAMP columns 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-utc also protects against changes due to daylight saving time. --tz-utc is enabled by default. To disable it, use --skip-tz-utc.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:57
cmuench@cmuench:~$ diff 2.sql 3.sql
2916c2916
-- Dump completed on 2017-07-12 0:30:01Solution
That sounds like the "proper" way.
A
A
Since mysqldump is essentially a bunch of
A
TIMESTAMP column contains the time in UTC, but converts when storing and fetching, so that you can only see the value converted to your local time.A
DATETIME column, on the other hand, is like taking a picture of a clock. There is no TZ conversion during store/fetch.Since mysqldump is essentially a bunch of
SELECTs, and the reload is a bunch of INSERTs, the data in the file looks like a picture of a clock. By using UTC during the fetch, the file will contain UTC time. By having that SET, the INSERT will, again, do no conversion to mess up the value.Context
StackExchange Database Administrators Q#121715, answer score: 4
Revisions (0)
No revisions yet.