Thesqldump between servers with different timezones (tz-utc)

datedatetimeMySQLtimezone

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:

From manual:

--tz-utc

This 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: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

Best Answer

That sounds like the "proper" way.

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.

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

PostgreSQL – Streaming Replication Between Hosts with Different Timezones

what will be the effect of this on fields of type "timestamp with timezone"?

They're converted from their internal UTC representation to UTC-5 at the moment. They'll be converted using UTC+4 after. So applications will see a 9-hour time shift if they're ignoring the time zone. If they respect the time zone reported in the timestamp then there will be no effect.

One notable exception to this is where the timestamp is truncated to a date. The timestamp may fall on a different day in one time zone to in another time zone. Truncation of a timestamp with time zone to a date discards time zone information. So be cautious with date_trunc, casts from timestamp with time zone to date, calls to extract or date_part, etc.

what will be the effect of this on fields of type "timestamp without timezone"?

Nothing.

what will happen to replication if we change the slave timezone?

Nothing. Replication is block level. It doesn't care about the timezone setting in the slightest.

This is just the same as changing TimeZone in the master with a SET TimeZone at the SQL level, or in the config file. So you can just try it with a standalone DB.

e.g.

test=> SHOW TimeZone;
    TimeZone    
----------------
 Australia/West
(1 row)

test=> CREATE TABLE tztz(tstz timestamptz, ts timestamp);
CREATE TABLE
test=> INSERT INTO tztz(tstz, ts) values (current_timestamp, current_timestamp);
INSERT 0 1
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts     
-------------------------------+------------+----------------------------+------------
 2015-08-02 20:48:56.664932+08 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = UTC;
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts     
-------------------------------+------------+----------------------------+------------
 2015-08-02 12:48:56.664932+00 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

test=> SET TimeZone = 'UTC-4';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts     
-------------------------------+------------+----------------------------+------------
 2015-08-02 16:48:56.664932+04 | 2015-08-02 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

Note that in both cases the timestamp is the same, because 16:48 at UTC+04 is the same as 12:48 at UTC or 20:48 at UTC+8 (Australia/West).

However, when the timezone shift causes the timestamp to be in the prior or next day in that time zone...:

test=> SET TimeZone = 'UTC-12';
SET
test=> SELECT tstz, tstz::date, ts, ts::date FROM tztz;
             tstz              |    tstz    |             ts             |     ts     
-------------------------------+------------+----------------------------+------------
 2015-08-03 00:48:56.664932+12 | 2015-08-03 | 2015-08-02 20:48:56.664932 | 2015-08-02
(1 row)

it's all fine with the timestamps still, but the timestamp with time zone truncated to date has moved to a different day.

If you're confused by the offsets seeming backwards that's because PostgreSQL respects the POSIX standard for time zone offsets which are, infuriatingly, backwards to the offsets most people use day to day.

Best Answer

Related Solutions

Mysql – How to compare (in MySQL) a DATETIME value to a TIMESTAMP value

PostgreSQL – Streaming Replication Between Hosts with Different Timezones

Related Question