Time zone management in PostgreSQL

Time zones trail only character encoding among computing's least-loved topics, and PostgreSQL's timestamp with time zone compounds the discomfort with behaviour that surprises newcomers. What follows is a practical grounding in how the type works and how to keep it from causing trouble.

What the SQL standard gives you

The standard's provisions for time zones are thin. Its definition states:

The surface of the earth is divided into zones, called time zones, in which every correct clock tells the same time, known as local time. Local time is equal to UTC (Coordinated Universal Time) plus the time zone displacement, which is an interval value that ranges between INTERVAL '–14:00' HOUR TO MINUTE and INTERVAL '+14:00' HOUR TO MINUTE. The time zone displacement is constant throughout a time zone, and changes at the beginning and end of Summer Time, where applicable.

In practice this means only a fixed offset from UTC can be represented. Applications facing daylight savings transitions must adjust the offset themselves. The standard also appears to treat the offset as part of the timestamp value itself, though it is not fully explicit on that point.

PostgreSQL's two timestamp types

PostgreSQL offers timestamp (equivalently timestamp without time zone) and timestamp with time zone, often written timestamptz. The former knows nothing of time zones and is not our concern. Both resolve to microseconds — six decimal places.

timestamp with time zone departs from the standard in two ways:

• No time zone is stored; the value is kept as seconds since midnight 2000-01-01 UTC.
• A time zone can be specified in more ways than a plain UTC offset, which makes daylight savings time changes tractable.

The first point confuses people the most. "Timestamp with time zone" is a misnomer — "absolute timestamp" would describe it better, because the value is a fixed point in time independent of the zone where the event happened.

Storing timestamps as seconds since the Unix epoch (midnight 1970-01-01 UTC) is a bad practice worth calling out. It brings no database-side benefit and several drawbacks: the stored values cannot be read by eye; most datetime arithmetic is lost, including expressions like current_timestamp - INTERVAL '1 month'; and the resulting WHERE conditions grow more convoluted, which harms query performance.

The TimeZone parameter

Session behaviour is governed by the database parameter TimeZone. Every time PostgreSQL renders a timestamp with time zone as a string, it formats the value according to the session's TimeZone and prints the matching UTC offset.

Crucially, TimeZone is not meant to be fixed once on the server. Each session should override it to match the client's local zone, so the same instant displays differently — and correctly — for each user.

Three ways to name a time zone

Beyond the standard's modest offerings, PostgreSQL accepts time zone specifications in three forms. All three work as values for TimeZone, in the AT TIME ZONE construct, and inside timestamp constants.

IANA names

The IANA time zone database, also called the Olson database, is maintained by the organization best known for top-level domains and IP ranges, and it underlies time zone handling in software worldwide. Names follow the pattern "Area/Location", where Area is a continent or ocean, or the special Etc for zones with no geographic home, and Location names the principal city whose surrounding zone is meant.

To see what your installation offers, query the system catalog pg_timezone_names. PostgreSQL can use its own copy of the IANA database or the operating system's, decided at build time; run pg_config --configure and check for --with-system-tzdata to learn which. Many zones, such as Europe/Vienna and Europe/Paris, currently agree, but distinct names remain justified since they have differed historically and may differ again.

The IANA data carries present and historical definitions for both standard and daylight saving time:

The first two results diverge because Vienna switched to daylight savings on the last Sunday of March 2022; the last result reflects that before 1893 Vienna ran on local solar time rather than an offset from Greenwich Mean Time. For the richest functionality and the least friction, IANA names are the recommended route.

Abbreviations

Familiar short forms like PST and CET are listed in pg_timezone_abbrevs. They have real weaknesses. They encode a fixed UTC offset with no daylight saving information — hence the need for both CET and CEST. And they are ambiguous internationally: on a given installation IST may mean Israel Standard Time (UTC+2 in winter), while an Irish reader takes it for Irish Summer Time and roughly a billion people expect India Standard Time. Apart from UTC, abbreviations are best avoided.

POSIX specifications

POSIX-style zones are documented in appendix B of the PostgreSQL manual. Their advantage is that they can describe daylight saving transitions, which may help in cases the IANA database does not cover — however unlikely such a case is. The European Union's current arrangement looks like this in full:

Read it as: winter uses the abbreviation CET, one hour ahead of UTC; daylight saving uses CEST, beginning on the last Sunday (day 0) of March (month 3) at 2 am and ending the last Sunday of October at 3 am.

POSIX zones have drawbacks of their own:

• Their offset sign is inverted relative to every other time zone notation. 2022-04-01 12:00:00 UTC-2 equals 2022-04-01 12:00:00+02, i.e. two hours east of UTC.
• Complex POSIX zones cannot appear in timestamp literals, because they break the timestamp parser.
• Historical time zone changes cannot be expressed in this syntax at all.

The sign convention alone trips up people constantly, so POSIX zones are best left alone as well.

Casts versus AT TIME ZONE

Two mechanisms move values between timestamp with time zone and timestamp. A cast always reads the plain timestamp in the zone named by TimeZone:

AT TIME ZONE performs both directions. Given a timestamp with time zone, it yields the timestamp a wall clock in the named zone would display:

Noon UTC corresponds to 2 pm on a Viennese wall clock. Given a plain timestamp, it returns the timestamp with time zone for the absolute instant that the named zone's wall clock would be showing:

When a Viennese clock reads noon, the absolute time is 10 am UTC.

Choosing an application strategy

Two approaches work well:

• Everything is timestamp; UTC is stored and the application performs all conversions.
• Everything is timestamp with time zone; each session sets TimeZone correctly and PostgreSQL performs all conversions.

Hybrid arrangements tend to produce confusion and pain.

Recommendations

Confusion is easy here, but most of it can be sidestepped: use timestamp with timezone everywhere, stay with IANA time zone names, and set TimeZone to the client's zone in every session. PostgreSQL then handles the conversions for you.

For analytical queries that need to group nearby timestamps, the new date_bin function is worth a look.