timestamp and timestamptz

Learning Focus

Use this lesson to choose the correct timestamp type and avoid time zone ambiguity.

Overview

• timestamp: date/time without time zone
• timestamptz: an absolute point in time, displayed in session time zone

Rule of thumb:

• use timestamptz for events

Example

CREATE TABLE events (
  event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  happened_at timestamptz NOT NULL DEFAULT now()
);

Convert for display:

SELECT happened_at AT TIME ZONE 'Asia/Singapore' AS local_time
FROM events;

What's Next

• Next: interval
• Section Overview