Month: May 2015

Change Amazon RDS database timezone to non UTC

Amazon RDS is a very powerful hosted Relational Database solution. I recently came across a big annoyance with it which was not being able to set a default timezone. I had a PHP application along with several scripts talking to MySQL on Amazon RDS.

There are many solutions available around the internet like changing the code on application side and setting the timezone on every connection from the application. However, i wanted a server side solution  to ensure that the timezone is changed for every connection. It would have been hazardous if i fail to change the code even at one place in my applications/scripts.

Many places talked about using CURRENT_USER() function in a stored procedure, checking if the user is not rdsadmin, then set the timezone for that session. However, it did not work for me. Also i wanted to change timezone only for certain users in my database. So, without further discussion, i will jump straight into the implementation.

I created a stored procedure named “change_time_zone” in “mysql” database which is the default database in a MySQL installation.

DELIMITER #
CREATE PROCEDURE mysql.change_time_zone ()
IF user() REGEXP '^(user1|user2|user3)' THEN
SET SESSION time_zone = "America/New_York"
END IF #
DELIMITER ;

Read More