CertGrid CertGrid
Concepts·PostgreSQL

PostgreSQL Timestamps and Time Zones

The same instant stored in both timestamp types, then read in a different session timezone. One returns the same digits and now means a different moment. The other returns a different rendering of the same moment.

Schemas and Data Guide 8 of 47 Intermediate

Written against the versions above. `timestamptz` does not store a timezone. It stores an instant in UTC and renders it in the session's timezone - the name misleads almost everyone at first.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Know what timezone the server thinks it is in

    Etc/UTC here, which is the right answer for a server and worth checking on any machine you inherit.

    This setting does not change what is stored. It decides how timestamptz values are rendered, and what an input string with no offset is assumed to mean. Two servers with different timezone settings will show the same stored row differently, which is a genuinely confusing bug to chase.

    bash Example session 
    psql -d appdb -c "SHOW timezone" TimeZone---------- Etc/UTC(1 row)

    Expected resultEtc/UTC.

    Success conditionYou know the reference frame for every timestamp on this server.

  2. Render one stored value in three ways

    One column, one row, three renderings.

    created_at displays with +00 - the session timezone's offset, which is UTC here. AT TIME ZONE 'UTC' converts it to a plain timestamp with no offset. AT TIME ZONE 'Asia/Kolkata' gives 19:29 against 13:59 - the same instant, five and a half hours later in local terms.

    Nothing about the stored value changed between those three columns. timestamptz stores an instant; the timezone is a lens you look through, and AT TIME ZONE swaps the lens.

    bash Example session 
    psql -d appdb -x -c "SELECT created_at, created_at AT TIME ZONE 'UTC' AS in_utc, created_at AT TIME ZONE 'Asia/Kolkata' AS in_ist FROM customers LIMIT 1"-[ RECORD 1 ]-----------------------------created_at | 2026-08-27 13:59:02.047544+00in_utc     | 2026-08-27 13:59:02.047544in_ist     | 2026-08-27 19:29:02.047544

    Expected resultThe same instant shown with +00, as bare UTC, and as 19:29 in Kolkata.

    Success conditionYou can render a stored instant in any timezone on demand.

  3. Store one instant in both types and change the session timezone

    This is the step that settles the argument. Two columns, timestamp and timestamptz, given the same moment - noon UTC on the first of June.

    Read back with the session timezone set to Asia/Kolkata:

    naive returns 2026-06-01 12:00:00 - unchanged. The same digits. But the session is now in a different timezone, so those digits now describe a different instant, five and a half hours from the one that was stored. The value did not travel; its meaning did.

    aware returns 2026-06-01 17:30:00+05:30 - different digits, and it is the same instant, correctly re-expressed with the offset shown.

    timestamp without a timezone is a calendar reading, not a moment. It is the right type for "the shop opens at 09:00" and the wrong type for anything that happened. Use timestamptz for events - always - and reach for timestamp only when you can articulate why the local reading is the fact you want.

    bash Example session 
    psql -d appdb -c "CREATE TABLE ts_demo (naive timestamp, aware timestamptz)"CREATE TABLEpsql -d appdb -c "INSERT INTO ts_demo VALUES ('2026-06-01 12:00:00','2026-06-01 12:00:00+00')"INSERT 0 1psql -d appdb -c "SET timezone='Asia/Kolkata'; SELECT naive, aware FROM ts_demo"SET        naive        |           aware---------------------+--------------------------- 2026-06-01 12:00:00 | 2026-06-01 17:30:00+05:30(1 row)

    Expected resultnaive reading 12:00:00 unchanged, aware reading 17:30:00+05:30.

    Success conditionYou can demonstrate why timestamptz is the default choice.

Troubleshooting

Official sources

← All PostgreSQL guides