Instructions on how to set time_zone Session Parameter with RDS

After much search and finding refrences like these that deem this impossible, even by AWS support, we have figured out how to set the default time zone for every session.

The reason AWS doesn’t support this is because it messes with the Replication/Multi-AZ and other aspects of the proprietor MySQL architecture on RDS.

Amazon RDS doesn’t allow you to change timezone, It keeps same UTC time zone across all regions.

The time_zone variable in the parameter group is read only, however if you have had to deal with these limitations you might be familiar with the init_connect command, the only problem is that if you use init_connect to change the time you can totally mess up your database when the rdsadmin user does DBA procedures on your MySQL database.

You can change the time zone on each connection or session by making a procedure in default mysql database and call this function on each init connection as long as the session does not belong to the SUPER privileged, automatically created and necessary rdsadmin user.

1- Create the procedure where -4 is the time difference with UTC

CREATE DEFINER=`mysql`@`%` PROCEDURE `fix_time`()
BEGIN
IF CURRENT_USER()<>’rdsadmin@%’ THEN
SET SESSION time_zone =’-4:00′;
END IF;
END

2- Update your custom parameter group to call this stored procedure at the beginning of each session:

You can create/edit Parameter Groups from the AWS RDS web console, or use aws-cli like this to set the init_connect to CALL fix_time():

aws rds modify-db-parameter-group --db-parameter-group-name parameter_group_name --parameters "ParameterName=init_connect, ParameterValue=CALL mysql.fix_time, ApplyMethod=immediate"

3- Finally make sure your instance uses this parameter group, reboot an Valla!

 

Related Post

Leave a Reply

Your email address will not be published. Required fields are marked *

Mobile Detected
Tablet Detected
Desktop Detected
Large Screen Detected
Retina Display Detected