Adjusting for the default time-zone setting on RDS

Question

We recently switched to an RDS instance and noticed that bunch of our database tasks were getting triggered 4 hours earlier than needed. On investigating further, the problem is caused by the default time-zone setting (UTC) on the RDS instance. Since this setting can not be altered, we would like to fix the issue on the code level globally across all our applications using this database instance. I tried to set the time-zone on the db instance I create to 'US/Eastern' by using

set GLOBAL time_zone = 'US/Eastern'" OR
set time_zone = 'US/Eastern'"

But that generates an error "Database error: Unknown or incorrect time zone: 'US/Eastern'"

What do you think I am doing wrong here? Does anyone has used any other solutions ?

Answer 1

Unfortunately it's not possible to set the default_timezone in the RDS DB ParameterGroups so your attempt was the right direction already.

$ rds-describe-db-parameters default | grep "time_zone"
DBPARAMETER  default_time_zone                                                                   engine-default  string   static   false

To set the global value via SET GLOBAL you need to have the SUPER privilege which is not granted to you as a RDS user.

The only way to set the time_zone is on a per-connection basis

mysql> SET time_zone = timezone;

On my machines I've tried US/Eastern successfully but I got a quite old generation running.

To determine the timezones you have available log into your box

mysql -h yourboxhost.rds.amazonaws.com -u <youruser> -p

and type

mysql> SELECT * FROM mysql.time_zone_name;

You should get a list of installed and valid timezone names you can set on your instance

+----------------------------------------+--------------+
| Name                                   | Time_zone_id |
+----------------------------------------+--------------+
| Africa/Abidjan                         |            1 |
| Africa/Accra                           |            2 |
| Africa/Addis_Ababa                     |            3 |
| Africa/Algiers                         |            4 |
| Africa/Asmara                          |            5 |
| Africa/Asmera                          |            6 |
| Africa/Bamako                          |            7 |
| Africa/Bangui                          |            8 |
| Africa/Banjul                          |            9 |
| Africa/Bissau                          |           10 |
| Africa/Blantyre                        |           11 |
| Africa/Brazzaville                     |           12 |
| Africa/Bujumbura                       |           13 |
| Africa/Cairo                           |           14 |
etc...

You have to set the time_zone each time you connect to your database server

For example if you use the php Mysqli extension you can do this

$mysqli = mysqli_init();
mysqli_options($mysqli,MYSQLI_INIT_COMMAND,"SET time_zone = 'Africa/Brazzaville'" );
mysqli_real_connect($mysqli,$host, $user, $pass,$dbName) or die ('Unable to connect');

Otherwise just manually ( in terms of let your database connector do it ) execute the SET time_zone = '<YOUR_DESIRED_TIMEZONE>' Query right after you've connected to your database

Answer 2

tldr;

Create a "shared" schema that all your users have EXECUTE access to, create a SPROC that modifies the session timezone and modify the init_connect MySQL parameter to call it.

---

As Ryan Weir pointed out in his excellent answer in a duplicate question this should probably be avoided if possible. If, however, you are like me and want to implement it for the sake of convenience and sanity then I took Ryan's solution and made a few modifications.

If you have multiple users setup in MySQL with varying permissions then simply putting the sproc in the mysql schema might have problems. To solve this I created a new schema called "shared" and gave all my users EXECUTE access to this schema. I then created the following stored procedure.

DROP PROCEDURE IF EXISTS shared.store_time_zone;
CREATE PROCEDURE shared.`store_time_zone`()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN     
    SET SESSION time_zone = 'US/Pacific';  
END IF;

I prefer to set 'US/Pacific' to handle daylight savings but you should test this to make sure your MySQL instance recognizes it first. Just execute the following query SET SESSION time_zone = 'US/Pacific'; to make sure it works. To look up your timezone execute SELECT * FROM mysql.time_zone_name;

At this point I recommend testing the permissions before you go modifying the paramter group and potential break everything. Simply connect to the DB (preferably with a user that has low level permissions and/or is commonly used) and execute the following queries.

CALL shared.store_time_zone;
select now();

Hopefully you didn't get any errors and the correct time showed up.

Next you will need to modify the init_connect parameter in the DB Parameter Group that your RDS instance is using. You can do this in the RDS web console, through the API or the command line utility. If you use the command line it will look like this:

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

If you do it through the web console then you just need to change the value of init_connect.

CALL shared.store_time_zone

Go back to your RDS instance in the web console and scroll the details pane down to the DB Parameter Group. It should say something like (applying) or (in-sync). Once it is (in-sync) go test everything out to make sure there are no problems.

If at this point you run into problems and need to roll things back then I recommend setting the init_connect value to something harmless like:

SET SESSION time_zone = '-00:00';

Setting it back to blank is impossible to do from the web console. See this thread for more details on why one can't restore the empty value for the DB parameter

Answer 3

The time_zone setting of RDS database instances can now be modified: https://aws.amazon.com/de/premiumsupport/knowledge-center/rds-change-time-zone/

Answer 4

I did the following steps, So that I could change the timezone

1. login to RDS and Create New Parameter Group.

2. Edit the newly created Parameter Group

3. Set timezone Ex:Asia/Calcutta and Save Changes

4. Modify RDS instance, change DB's Parameter Group to newly created parameter group

5. Save And Reboot RDS instance

Answer 5

@Thomas Paine's solution works for me except I had to user user() instead of current_user() as inside the context of init_connect current_user() returns the master RDS user. (By master I do not mean rdsadmin which is the real root user but the user created with the DB instance with most privileges.)