This topic describes how to change the time zone of an ApsaraDB RDS for PostgreSQL instance that uses standard SSDs or enhanced SSDs (ESSDs). You can change the time zone in the ApsaraDB RDS console or by calling an API operation.
PrerequisitesThe RDS instance uses standard or enhanced SSDs.
SET timezonestatement takes effect only for the current session. If you want the change to permanently take effect, we recommend that you change the time zone based on the description in Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
- If you want to specify a time zone for a database, execute the
ALTER DATABASE <Name of the database> SET timezone TO '<Name of the time zone>';statement.Note If you want to restore the time zone of a database to the default time zone, execute the
ALTER DATABASE <Name of the database> SET timezone TO DEFAULT;statement.
- PostgreSQL supports two data types for timestamps: TIMESTAMP and TIMESTAMPTZ. We recommend that you select a data type based on your business requirements.
- TIMESTAMP: stores a combined date and time value in UTC, but does not store time zone data. If you change the time zone based on this topic, the query results of the value of this type remain unchanged.
- TIMESTAMPTZ: stores a combined time zone-aware date and time value. When you insert a value of the TIMESTAMPTZ type, the system converts the value into a UTC value and stores the UTC value in a table. When you query the value from your RDS instance, the system converts the value into the time that is specified by the configured time zone for the instance or database. If you change the time zone based on this topic, the query results of the value of this type vary based on the new time zone.
Change the time zone
You can change the time zone only of an RDS instance that uses standard SSDs or ESSDs. To change the time zone, log on to the ApsaraDB RDS console and modify the timezone parameter on the Parameters page. For more information, see Modify the parameters of an ApsaraDB RDS for PostgreSQL instance.
Query supported time zones
You can run the following command to query supported time zones:
select name,utc_offset from pg_timezone_names;