Postgres timestamp or timestamptz? Getting time zones right
Short answer: PostgreSQL has two timestamp types that behave very differently. timestamp (without time zone) stores the date and time exactly as given and silently ignores any time zone in the input. timestamptz (with time zone) converts the input to UTC when storing and back to the session's time zone when reading. For most business data, such as orders, payments, sign-ins and logs, use timestamptz so every value marks an exact moment; for future appointments in a specific place, also store the location's time zone name so you can show the right local time.
What each type actually does
PostgreSQL's documentation sets out the rules:
timestampistimestamp without time zone. The SQL standard requires plaintimestampto mean the version without a time zone, and PostgreSQL follows that. If the input includes a time zone, PostgreSQL silently ignores it: the stored value is just the date and time fields.timestamptzistimestamp with time zone. An input with an explicit zone is converted to UTC using that zone's offset. An input without a zone is assumed to be in the session'sTimeZonesetting. Either way, the value is stored as UTC, and the original zone isn't kept.- On output,
timestamptzis converted from UTC to the session's time zone and displayed as local time there.
Despite the name, timestamptz doesn't store a time zone. It stores an exact instant and presents it in whichever zone the connection uses.
How bookings end up hours out
A typical story: a booking app stores appointment times as plain timestamp. The web server's time zone is UTC, staff are in Sydney, and some customers book from Perth. One part of the code inserts times as local Sydney time, another inserts UTC from an API, and the column accepts both without complaint because any zone information is ignored. The database now holds a mix of meanings, and nothing records which is which.
Daylight saving makes it worse. Sydney and Melbourne change their clocks while Brisbane and Perth don't, so an offset that was right in winter is wrong in summer.
Use timestamptz for moments in time
Anything that records when something happened should be timestamptz:
- orders, payments and refunds,
- sign-ins and account changes,
- messages, emails and webhook events,
- audit and error logs.
These are exact moments. Storing them as instants means comparisons, sorting and "in the last 24 hours" queries are correct no matter where users or servers are.
To show them in a specific zone, either set the session time zone for that connection or use PostgreSQL's AT TIME ZONE construct, for example to show a Melbourne branch's orders in Melbourne time.
Use full zone names, not abbreviations
PostgreSQL accepts time zones as full names such as Australia/Adelaide, as abbreviations such as ACST, or as POSIX-style strings. Its documentation notes it uses the widely used IANA time zone data for full names, and that abbreviations only define a fixed offset, unlike full names, which carry daylight saving rules. For business logic, store and use full zone names.
Future appointments need a little more
For something like "dentist appointment at 9:00 on 14 March in Adelaide", the customer cares about 9:00 on the clock in Adelaide. PostgreSQL's documentation says that for future times it assumes the latest known rules will continue indefinitely. If those rules change after you store the booking, the instant you stored may no longer be 9:00 local time.
A common approach for appointments is to store:
- the local date and time as entered, as
timestampwithout time zone, - the location's IANA zone name in a separate column,
- optionally, a computed
timestamptzfor sorting and reminders, recalculated if needed.
That's a deliberate use of plain timestamp, documented in your schema, not an accident.
Avoid "time with time zone"
PostgreSQL also has a time with time zone type. Its documentation recommends using types that contain both a date and a time when working with time zones, and doesn't recommend time with time zone, which exists mainly for legacy and standards compliance. For opening hours, store a plain time plus the branch's zone name.
Converting an existing column
Moving a column from timestamp to timestamptz needs care, because the documentation says conversions between the two types assume the plain timestamp is in the session's time zone. Decide what the existing values mean first:
- If they were all written in UTC, convert with an explicit
USINGclause that says so, such ascreated_at AT TIME ZONE 'UTC'. - If they were written in a particular local zone, use that zone name instead.
- If they're mixed, you'll need to clean them using other clues, such as which part of the app wrote them.
Test on a copy of the database, compare a sample of rows before and after, and check what your application and ORM send afterwards.
Check your application layer
Your database driver, ORM and API format all affect what arrives. Send timestamps with an explicit offset, such as ISO 8601 strings ending in Z or +10:30, and make sure your code doesn't strip it. Set the server and connection time zones deliberately rather than relying on defaults.
An illustrative example
A physiotherapy group with clinics in Adelaide and Darwin finds reminders going out an hour early for half the year. Appointment times are stored as plain timestamp in server local time. The developer adds a clinic time zone column, converts the event and payment columns to timestamptz with an explicit USING clause, keeps appointment start times as local time plus zone, and generates reminder times from those. Reminders now follow each clinic's daylight saving rules.
FAQ
Does timestamptz use more storage?
Both types use the same amount of space. The difference is behaviour, not size.
Should the server run in UTC?
It helps, but don't depend on it. Make the type and conversions explicit.
Why does my query show a different time than my app?
Your query tool's session time zone may differ from the app's. Check the TimeZone setting in each.
What about date-only values like birthdays?
Use the date type. It has no time zone and doesn't need one.
If your app's booking or order times drift between time zones and you want the database sorted out, tell us which tables hold your dates.