Databases: Timezones with Postgres

15 April 2022

The sensible established approach when saving times or timestamps is always to save such values as UTC (which is the same as GMT), and then to convert into the timezone of the user when being used for business logic or where this is otherwise required.

Thankfully, Postgres offers functionality which can avoid any issues with timezones.

Postgres offers a timezone-aware type TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ to make getting UTC values into the database easier — simply include the timezone in the string.

By way of example — this is a SELECT but the same syntax would be used with an INSERT statement:

SELECT '2022-04-15 08:30:00 Europe/London'::timestamptz 
AS ts_in_utc;
ts_in_utc
2022-04-15 07:30:00.000000

When pulling data out of the database which has a TIMESTAMPTZ column, you can specify using AT TIME ZONE what timezone you would like the timestamps in.

By way of example:

SELECT ts AT TIME ZONE 'Europe/London' as ts_london
FROM table_name
ORDER BY ts DESC;
ts_london
2022-04-15 08:30:00.000000

Note because the TIMESTAMPTZ only stores the timestamp in UTC and not the timezone, it may be worth storing the timezone in another column for reference.