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

Short description

In Aurora, the time_zone parameter is set at the cluster level. The parameter group provides a default time zone for the cluster and all databases. The default value of time_zone is UTC. If you change the value of time_zone, then your change applies to all the nodes in the cluster.

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

• DB instance parameter groups act as containers for engine configuration values that apply to one or more DB instances. DB instance parameter groups apply to DB instances in both Amazon Relational Database Service (Amazon RDS) and 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 apply 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. Parameters that affect the physical storage layout and engine parameters 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

New DB cluster parameter groups

To use Amazon Aurora MySQL-Compatible Edition 5.7 or PostgreSQL to change the time_zone parameter to ASIA/CALCUTTA, complete the following steps:

1. Create a custom DB cluster parameter group.
2. Modify the dynamic parameter time_zone to "ASIA/CALCUTTA".
3. Attach the new DB cluster parameter group to the cluster, and then manually reboot the writer instance to apply the changes.
4. To check the timestamp of your DB instance, run the following command:
   MySQL:

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

   PostgreSQL:

   postgresql> select * from pg_timezone_names;
   +---------------+
   | timezone_names |
   +---------------+
   | Asia/Calcutta |
   +---------------+
   1 row in set (0.00 sec)

Existing DB cluster parameter groups

For existing custom parameter groups, the parameter is dynamic. You don't need to reboot the writer instance to apply the changes. Instead, you can update the parameter to attach the existing cluster parameter group to the cluster. Then, exit the session and reconnect to the cluster. This process recycles the connection.

Note: You can also set the time zone at the session level. However, the session time zone overrides the DB cluster's time zone only for the duration of the session.