How do I change the time zone for an Amazon Aurora DB cluster?

Short description

In Amazon Aurora, the time_zone parameter is a cluster level parameter. The parameter group provides a default time zone for the cluster. This is the default for all databases. The default value of time_zone is UTC, and after you change the value of time_zone, your change is applied to all the nodes in the cluster.

Note: The default_time_zone parameter refers to server time zone. You can't modify this parameter.

Important: There is a difference between DB cluster parameter groups and DB instance parameter groups.

• A DB parameter group acts as a container for engine configuration values that are applied to one or more DB instances. DB parameter groups apply to DB instances in both Amazon Relational Database Service (Amazon RDS) and Amazon Aurora. These configuration settings apply to properties that can vary between the DB instances in an Aurora DB cluster, such as the sizes for memory buffers.
• A DB cluster parameter group acts as a container for engine configuration values that are applied to every DB instance in an Aurora DB cluster. For example, the Aurora shared storage model requires that every DB instance in an Aurora cluster use the same setting for parameters, such as innodb_file_per_table. So parameters that affect the physical storage layout are part of the DB cluster parameter group. The DB cluster parameter group also includes default values for all the DB instance-level parameters.

Resolution

This example changes the time_zone parameter to ASIA/CALCUTTA using Amazon Aurora MySQL-Compatible Edition 5.7.

1.    Create a custom DB cluster parameter group. For more information, see Working with parameter groups.

2.    Modify the dynamic parameter time_zone="ASIA/CALCUTTA".

3.    Attach the new DB cluster parameter group that you created to the cluster, and then manually reboot the DB cluster to apply the changes.

4.    Check the timestamp of your DB instance by running the select @@time_zone command:

mysql> select @@time_zone;
+---------------+
| @@time_zone   |
+---------------+
| Asia/Calcutta |
+---------------+
1 row in set (0.00 sec)

Note: When you set the time zone at session level, this overrides both the database and parameter time zone. But, this is maintained only for the session. When you run the SET time_zone = 'Europe/Helsinki'; command, this sets the time zone for all the databases to Europe/Helsinki. The new time zone takes precedence over the parameter group time zone. A time zone set at database level overrides the parameter group time zone. A session time zone overrides both the parameter group time zone and a time zone set at database level.