Dates, times and time zones

Two kinds of timestamp

PostgreSQL has two timestamp types, and the names are misleading:

Type	What it stores	On output
timestamptz (timestamp with time zone)	One exact instant, kept internally in UTC	Converted to the session’s time zone
timestamp (without time zone)	A date and a clock reading, nothing more	Exactly as stored

Despite its name, timestamptz does not remember the time zone you gave it. It uses the zone to work out the exact instant, stores that instant, and forgets the zone.

See the difference

Store the moment Asha placed her first order, 10:15 in Pune, in both types:

CREATE TEMP TABLE events (label text, with_tz timestamptz, without_tz timestamp);
INSERT INTO events VALUES
  ('order placed in Pune', '2025-03-02 10:15+05:30', '2025-03-02 10:15+05:30');

Now read it back from sessions in three different time zones:

Session time zone	with_tz	without_tz
UTC	2025-03-02 04:45:00+00	2025-03-02 10:15:00
Asia/Kolkata	2025-03-02 10:15:00+05:30	2025-03-02 10:15:00
America/New_York	2025-03-01 23:45:00-05	2025-03-02 10:15:00

with_tz is the same instant every time, shown on three different clocks: in New York it was still the evening before. without_tz says “10:15” everywhere, and nothing in it tells you where those 10:15 were. The +05:30 in the input was silently thrown away.

Showing local time

To display an instant as a particular place’s local time, use AT TIME ZONE:

SELECT ordered_at, ordered_at AT TIME ZONE 'Asia/Kolkata' AS pune_local_time
FROM orders WHERE id = 101;

       ordered_at       |   pune_local_time
------------------------+---------------------
 2025-03-02 04:45:00+00 | 2025-03-02 10:15:00

The result is a plain clock reading for display, not an instant to store.

When plain timestamp and date are right

• date for things that are a whole day wherever you are: a birthday, a joining date, an invoice date. customers.joined_on is a date.
• timestamp without time zone for a wall-clock time that should not move with the viewer: “the shop opens at 09:00”, in whichever city the shop is.

Everything that happened at a moment, use timestamptz.

Check yourself

1. What does timestamptz actually store?

   Show answer

   One exact instant, held internally as UTC. The time zone given on input is used to compute that instant and is then discarded; output is converted to the session’s time zone.

   I could explain this without looking

2. You insert '2025-03-02 10:15+05:30' into a timestamp column. What is stored?

   Show answer

   2025-03-02 10:15:00. For timestamp without time zone, PostgreSQL silently ignores the offset, so the information about which 10:15 is lost.

   I could explain this without looking

3. Which type for an order’s placed-at time, a user’s birthday, and a store’s daily opening time?

   Show answer

   timestamptz for the order (a moment), date for the birthday (a whole day), and time or timestamp without time zone for the opening time (a wall-clock reading).

   I could explain this without looking