MySQL Bugs: #47084: Times and SBR on machines in different time zones

Bug #47084	Times and SBR on machines in different time zones
Submitted:	3 Sep 2009 5:25	Modified:	3 Sep 2009 10:31
Reporter:	Susanne Ebrecht	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
In MySQL datatype
Time, Timestamp and Datetime are defined without time zone informations.

The function now() is not a deterministic function.

In SBR we made the date/time functions like now() SBR safe.

That means, when you have the master in Paris and the server in New York:

Let us say it will be 2009-09-03 19:00 in Paris.
The locale New York time will be 13:00.

INSERT INTO paris_server(col) VALUES(now());

In Paris the column will get the value 2009-09-03 19:00

Because there is no time zone information at the data type and now() is SBR safe the value 2009-09-03 19:00 will be replicated.

Means also the column in New York server will get the value:

2009-09-03 19:00

19:00 and not 13:00 ... missing time zone informations at the data type means that the servers aren't able to calculate transparent and fully automatic correct locale times here.

So when you will try to get the unix timestamp out of the columns (i.e. by using the function unix_timestamp) then you will get different values.

SELECT unix_timestamp(col) ...;

Result in Paris:
1251997200

Result in New York:
1252018800

I think this should be pointed out in documentation

How to repeat:
See above

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.