How to set database timezone for a AWS RDS instance [duplicate]

Question

We use the latest MySQL server on the AWS RDS instance and we have configured to run it on US-East data center. We are assuming that any new Date() or Time.now() invocation will store the date in the timezone in which the database server is running.

Is there a way to point my AWS RDS instance running on US-East to point to the PST timezone, so that any persistence of date will store the values in PST instead of EST. (i.e. If store an object around 10 AM EST, the db column should reflect 7 AM EST instead).

Answer 1

Before the rest of my answer, I'd just like to recommend right now that if you have any option to change your application to use UTC it will save you a lot of grief now and in the future.

However given the context of your question, I'm assuming that this isn't an option and that you're adapting a system designed to use MySQL in a traditional server environment where you can just change the timezone, and that there's code logic that expects this timezone and can't be easily adapted to use UTC.

But if you really need it to store as PST, read on.

---

You're correct that mySql will use the server's timezone for timestamp storage by default, however your assumption that RDS instances have their timezones set based on the AWS region in which they are launched is incorrect - all RDS instances are launched with their timezone set as UTC, and this configuration can't be changed:

The time zone is currently not modifiable. There is indeed a Parameter value in rds-describe-db-parameters called "default_time_zone" but it's marked as not modifiable.

So your only option is to set the timezone on each connection your application makes to the database instance to PST. You can use the SET SESSION time_zone = 'PST' query to execute in every connection an application makes by following the two steps found here:

1. Create the following stored procedure (UTC-8 is PST):

   DELIMITER |  
   CREATE PROCEDURE mysql.store_time_zone ()  
      IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN     
          SET SESSION time_zone = '-8:00';  
      END IF 
   |
   DELIMITER ;
   

2. Connect to your instance, and run the following command:

   $ rds-modify-db-parameter-group PARAMGROUP --parameters "name=init_connect, value='CALL mysql.store_time_zone', method=immediate"
   

3. You may need to grant EXECUTE permissions to the users that will be connecting to the database, otherwise you may get a connection error:

   GRANT EXECUTE ON PROCEDURE mysql.store_time_zone TO 'user'@'host';
   

Now every query executed against your RDS instance by any client should use PST without modifying any application logic and without needing to update any previously-stored timestamps in the database.