Replicated timestamp column in different timezone

Question

I have the master running Mysql 5.5.38 on Ubuntu 14.04 LTS. The timezone in the master is UTC+5:30. The slave is in AWS RDS which runs in UTC. The slave is primarily used for running for SQL queries by staff.

To maintain their sanity, I setup the timezone hack. This also applies to the replication user.

One of our innodb tables records scheduled appointments. The relevant parts of the table are below:

`startDateTime` timestamp NOT NULL DEFAULT '2000-01-01 12:00:00', `endDateTime` datetime DEFAULT NULL, `createdOn` timestamp NULL DEFAULT NULL,

I am seeing the following time on the master when querying the table:

• startDateTime - IST
• endDateTime - IST
• createdOn - IST

However on the slave:

• startDateTime - UTC
• endDateTime - IST
• createdOn - IST

Running SELECT NOW(); on either servers shows current time in UTC+5:30. The bin_log format on both master and slave is STATEMENT

How do I ensure that the replicated column startDateTime is also in IST?

Update:

• RDS is a hosted mysql with no access to my.cnf. Futher time_zone variable is restricted and hard coded to UTC

Answer 1

The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values.

http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

Change your binlog format to ROW to eliminate the vagaries of statement-based replication and then change your DATETIME columns to TIMESTAMP (on the master, let the change replicate).

This solves the issues you're experiencing.

Although... it solves entirely the wrong problem, which is that it's typically better to store times properly in UTC and localize them as needed. Of course, that's what TIMESTAMP columns essentially do -- they store datetimes as UTC after converting then from the current session time zone, and then they convert back when you read them.

If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

Answer 2

I had similar issues a month or 2 ago. There is a solution for this. But this has been implemented in CentOS.

There are 2 steps for the resolution [run this on slave]:-

• mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql
  This command should copy all the time from unix timezone directory to mysql.time_zone_name table
  

• After this you need to tell mysql which timezone to use, to do this
  Edit /etc/my.cnf to add a line default_time_zone=Asia/Kolkata , Asia/Kolkata could be changed to any timezones you want.
  
  This change will require mysql restart to take in effect.
  If you do not want to restart then simply use this in mysql command
  set global time_zone = 'Asia/Kolkata'; set session time_zone = 'Asia/Kolkata';

Hope this helps!!