I recently purchased a MySQL Amazon RDS Instance and noticed that the default timezone for the database is set to UTC. From the research I did online, I found that the value could be easily changed on a per-connection or session basis (requiring a modification of the client code) but to change it permanentely on the server side was not really possible or had major flaws. I eventually came across this blog post: http://www.parhasard.net/entry/2011/07/06/17:37/ that gives a really simple and elegant server side solution to the problem and I hope it will eventually help someone else too!
You need to create the following stored procedure in your default database named “mysql”, it does not need to be created into subsequent databases:
DELIMITER |
CREATE PROCEDURE mysql.store_time_zone ()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = 'America/Montreal';
END IF |
DELIMITER ;
The “America/Montreal” can obviously be modified to whichever timezone suits your needs. You then need to customize the parameter group used by your RDS instance so that it executes the previously defined stored procedure on new connections like so (replace “PARAMGROUP” by the name of the param group used by your instance):
$ rds-modify-db-parameter-group PARAMGROUP --parameters "name=init_connect, value='CALL mysql.store_time_zone', method=immediate"
You can then make sure that the changes were applied using (replace “PARAMGROUP” by the name of the param group used by your instance):
$ rds-describe-db-parameters PARAMGROUP --source=User
DBPARAMETER Parameter Name Parameter Value Source Data Type Apply Type Is Modifiable
DBPARAMETER init_connect CALL mysql.store_time_zone user string dynamic true
$
Do not forget to allow whoever will be connecting to the database permission to execute the stored procedure! Forgetting to do so will result in various errors like “MySQL Server has gone away”. You can grant such permission by executing:
GRANT EXECUTE ON PROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'some_host';
This fix is similar to one I have seen online that asks you to put “SET time_zone = ‘America/Montreal’” directly inside the init_connect parameter. That solution was not working well because it caused connections to hang after an instance restart, most likely caused by the fact that the generic “rdsadmin” user is using UTC dates for some reason.
