timestamptz vs timestamp: Postgres time zones done right

Australia has three main time zones, and only some of them observe daylight saving. Sydney and Melbourne shift; Brisbane doesn't; Perth is two or three hours behind depending on the month; Adelaide is on the half hour. If your timestamps are the wrong type, all of that turns into a support ticket that says "yesterday's total changed".

What each type actually stores

The names mislead almost everyone. timestamptz does not store a time zone. It stores an absolute instant: what you insert is converted to UTC, and what you select is rendered in the reading session's TimeZone setting. Two clients in Sydney and London see different text for the same stored value, and both are right.

timestamp without time zone stores exactly the digits you gave it, with no offset and no meaning. "2026-10-04 02:30:00" is just a label. It doesn't identify a moment, and two systems can disagree about which moment it was.

set timezone = 'Australia/Sydney';
select now();                          -- 2026-08-05 19:03:11.42+10
select now() at time zone 'utc';       -- 2026-08-05 09:03:11.42   (a bare timestamp)
select now() at time zone 'Australia/Perth'; -- 2026-08-05 17:03:11.42

Note what AT TIME ZONE does: applied to a timestamptz it returns a bare timestamp — the local wall-clock reading in that zone. Applied to a bare timestamp it does the reverse, interpreting it as local time in that zone and returning a timestamptz. It's one operator doing two jobs, which is why it confuses people.

Which type to use

• timestamptz — created_at, paid_at, logged_in_at, expires_at. Anything that marks a real event.
• timestamp — genuinely zoneless wall-clock values: a store's 9:00 am opening time, a recurring reminder that should fire at 8:00 am wherever the user is.
• date — a calendar date with no time component: date of birth, invoice date. Don't store these as timestamps; you'll spend the rest of the project stripping times off them.
• timestamptz + a zone column — when you need to know where as well as when: store Australia/Brisbane in a text column beside the instant. This is the only correct way to reconstruct "9 am the customer's time" for a future date, because a future zone rule can change.

Prefer timestamptz with a default of now() and let the database record the instant, rather than trusting whatever a client's clock believes.

The reporting bug everyone ships once

This looks obviously right and is wrong:

-- groups by UTC days: your "day" starts at 10am Sydney time
select date_trunc('day', created_at) as day, count(*)
from orders group by 1;

Convert first, then truncate:

select date_trunc('day', created_at at time zone 'Australia/Sydney') as day, count(*)
from orders group by 1 order by 1;

Same for a date range. To count "everything that happened on 4 October, Sydney time", write the boundaries in local time and let Postgres convert them:

select count(*)
from orders
where created_at >= timestamp '2026-10-04 00:00' at time zone 'Australia/Sydney'
  and created_at <  timestamp '2026-10-05 00:00' at time zone 'Australia/Sydney';

Use half-open ranges (>= and <) rather than between. between is inclusive at both ends, which double-counts anything landing exactly on midnight, and gets worse with fractional seconds.

This form is also index-friendly: the comparison is against the bare column, so an index on created_at is usable. Wrapping the column in at time zone inside the WHERE clause hides the index — as with any function on an indexed column, per the indexing rules. If you must group by local day frequently, index the expression itself.

Never use a fixed offset

+10:00 is right for Sydney for roughly half the year. From October to April it's an hour out, so every day boundary moves and rows change days — the classic "our report is off by a day, twice a year" bug. Abbreviations are no better: AEST pins you to standard time, and some abbreviations are ambiguous worldwide.

Name the zone and let the tz database do the work: Australia/Sydney, Australia/Melbourne, Australia/Brisbane, Australia/Adelaide, Australia/Perth, Australia/Darwin, Australia/Hobart. Postgres knows the historical rules for each, so a timestamp from 2019 gets 2019's rules.

select * from pg_timezone_names where name like 'Australia/%';

The session time zone is not a strategy

SET timezone only changes how values are displayed and how bare timestamps are interpreted. It's fine for interactive psql, but don't let application correctness depend on it: through a connection pooler, a session setting may not be the one your next query gets, and your driver may be setting its own. Be explicit in the SQL instead.

Handy conversions

-- Unix epoch → timestamptz
select to_timestamp(1785000000);

-- timestamptz → epoch seconds
select extract(epoch from created_at);

-- start of the current month, Sydney time, as an instant
select date_trunc('month', now() at time zone 'Australia/Sydney')
       at time zone 'Australia/Sydney';

-- age in whole years (birth dates are `date`, not timestamps)
select extract(year from age(current_date, date_of_birth));

Fixing it after the fact

If you've been storing bare timestamps that were always meant to be UTC, the conversion is a one-liner — but on a large table it rewrites the whole thing, so treat it as a real migration:

alter table orders
  alter column created_at type timestamptz
  using created_at at time zone 'utc';

Get the using clause right: name the zone the existing values were actually recorded in. If they were local Sydney times, use 'Australia/Sydney', and remember any value falling in a daylight-saving gap or overlap is genuinely ambiguous. Rehearse it on a masked clone first and run it with the precautions in zero-downtime migrations.

None of this is host-specific — it's how PostgreSQL behaves everywhere. Check your own server default with show timezone;, then stop depending on it: store timestamptz, name the zone in the query, and every report comes out right in Sydney, Brisbane and Perth alike.