Change the time zone of an ApsaraDB RDS for PostgreSQL instance - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL stores all timezone-aware timestamps internally in UTC and converts them to the time zone specified by the timezone parameter before displaying results to clients. Change this parameter when your instance's time zone doesn't match your business logic or your application handles data across multiple regions.

Prerequisites

Before you begin, ensure that:

Your RDS instance uses cloud disks

Note

Instances that use Premium Local SSDs do not support the timezone parameter.

How time zones work in PostgreSQL

PostgreSQL supports two timestamp data types. Changing the timezone parameter affects them differently:

Data type	Storage	Effect of changing `timezone`
TIMESTAMP	Stores date and time without time zone data	Query results remain unchanged
TIMESTAMPTZ	Stores UTC internally; displays in the configured time zone	Query results change to reflect the new time zone

If your application needs to display times in a specific local time zone, use TIMESTAMPTZ.

Usage notes

The SET timezone statement applies only to the current session. To make the change permanent, modify the timezone parameter as described below.

To set a time zone for a specific database, run:

ALTER DATABASE <database_name> SET timezone TO '<timezone_name>';

To reset it to the default, run:

ALTER DATABASE <database_name> SET timezone TO DEFAULT;

Change the time zone

Log on to the ApsaraDB RDS console and modify the timezone parameter on the Parameters page. For step-by-step instructions, see Modify instance parameters.

Note

RDS for PostgreSQL accepts only time zone names (full names from pg_timezone_names). UTC offsets (for example, UTC+8) are not accepted as timezone parameter values.

Query supported time zones

Run the following statement to list all supported time zones and their UTC offsets:

SELECT name, utc_offset FROM pg_timezone_names;

The output looks like:

           name           | utc_offset
--------------------------+------------
 GMT0                     | 00:00:00
 Iceland                  | 00:00:00
 America/New_York         | -05:00:00
 America/Los_Angeles      | -08:00:00
 Asia/Shanghai            | 08:00:00
 Asia/Tokyo               | 09:00:00
 Europe/London            | 00:00:00
 ...

Use the value from the name column as the timezone parameter value. For the full reference, see pg_timezone_names.

Next steps

If you didn't specify a time zone when you created your RDS instance, the system assigned a default time zone based on the region you selected. For the full mapping, see Mappings between the regions and default time zones of ApsaraDB RDS for PostgreSQL instances.
To modify the timezone parameter via API, use the ModifyParameter operation.