PostgreSQL stores timestamps with time zones in Greenwich Mean Time (GMT); however, the database can be configured to return timestamps in a different time zone.

To set the default time zone for a database, use the ALTER DATABASE command. For example, the following command sets the default time zone for the mydb database to US/Eastern:

ALTER DATABASE mydb SET TIMEZONE TO ‘US/Eastern’;

Note that SQL queries will not return timestamps in the new time zone until the session is disconnected and a new session is established.

To display the current session time zone, use the SHOW timezone command.

SHOW TIMEZONE;

Time Zone Names

To get a list of time zone names, use the following SQL command:

SELECT * FROM pg_timezone_names;

For US time zones specifically, use the following command:

SELECT * FROM pg_timezone_names WHERE name ilike ‘us/%’;

Timestamps And GMT

When importing timestamps without time zone data from an external source, or when updating timestamp data with timestamps without time zones, it is important to specify the time zone of the data so the database stores it correctly. For example, the following query updates the cust_date_added column in the customers table to 2023-01-25 13:00:00 in the US/Eastern time zone:

UPDATE customers
SET cust_date_added = TIMESTAMP WITHOUT TIME ZONE ‘2023-01-25 13:00:00’ AT TIME ZONE ‘US/Eastern’
WHERE cust_customer_number = ‘PM0001’;

If the time zone had not been specified, the database would have assumed the actual value of 2023-01-25 13:00:00 was in GMT.

The following query returns the GMT date actually stored in the cust_date_added column, as well as the date converted to Eastern time:

SELECT cust_customer_number, cust_date_added, cust_date_added AT TIME ZONE ‘America/New_York’ AS edt_date_added
FROM customers
WHERE cust_customer_number = ‘PM0001’;

The AT TIME ZONE function can be used to convert a timestamp to a different time zone. This function is useful for displaying timestamps in the local time zone or for comparing timestamps from different time zones.

For more information on PostgreSQL date and time types, visit the documentation page.