My AWS Musings

The default time zone of your RDS database instance is UTC. It simply can not be changed.

RDS does not give you super privileges. That is why you won’t be able to change the global time zone by simply executing:

SET GLOBAL time_zone = 'US/Pacific';


The default_time_zone db parameter is not modifiable.

In this Amazon forum thread, you can clearly see the AWS guy saying that there is no way to change the time zone of a RDS instance.

It is very important to understand that unlike Java (or other programming languages), MySQL does not store time zone in its date, datetime or timestamp datatypes. That is why it’s important that all the dates are stored in one time zone. Most people prefer to store them in their own time zone for easier calculations.

Since you cannot change the database server settings, you will have to change your application. Here are some approaches you can take to make sure that the date and time stored in your database is in your desired time zone.

1) Set time zone per database connection
When ever you create a connection, set time zone using

SET time_zone = 'US/Pacific';

This time zone will be valid only for the connection. That is why it must be done for every single connection you open. If you have a java application, you can use a wrapper around the JDBC driver to accomplish this.

Please note that MySQL allows you to specify time zone by its name (such as ‘US/Pacific’) or by the difference in hours (such as ‘-8:00′). If you are in a part of United States or any other part of the world that uses day light savings, do not use the difference of hours option for obvious reasons.

Named time zones can only be used if the time zone related tables are populated in MySQL. Thankfully they seem to be populated in RDS.

2) Use convert_tz function
You can use MySQL’s convert_tz function to convert the time zone. For example:


insert into my_table (created_date) values (convert_tz(now(), 'GMT', 'US/Pacific'));


For more information on convert_tz function visit convert_tz documentation on MySQL website

3) Set the date/time pragmatically in your application code in every query.
Thus if you have created_date column in your table, do not rely on timestamp datatype’s default value. The default value will be in UTC time zone. Set the value explicitly using application code. If you are using a prepared statement this becomes very easy. All you have to make sure is to pass a date object with appropriate time zone set in it. In order for your application to calculate correct date, you have to make sure that your application instance’s time zone is set to your time zone.

Amazon ec2 instances allow you to change their default time zones. If your web server is also deployed in ec2, I would suggest you to bundle an AMI with your time zone and use it to launch all your instances. I think most of the ubuntu public AMIs have time zone set to UTC.