Replicated timestamp column in different timezone 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
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.