mysql - 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