timestamptz vs timestamp in SQL: storing time correctly

One type stores a real moment in history; the other stores plain digits on a wall with no zone attached. Choosing between timestamptz and timestamp decides whether your data lines up for users across time zones or silently drifts apart. Let's make it concrete.

An instant vs a wall clock

timestamptz (aka timestamp with time zone) stores an absolute instant: internally it is UTC, and the session zone is applied only on display. timestamp (without time zone) is a "naive wall clock": the number 2024-03-15 10:00 with no answer to "10am where exactly".

SET timezone = 'UTC';
SELECT
  now()::timestamptz                    AS instant,   -- absolute, stored as UTC
  '2024-03-15 10:00'::timestamp         AS wall_time; -- naive, no zone

The same timestamptz renders differently across sessions, yet it is one instant:

SET timezone = 'America/New_York';
SELECT '2024-03-15 10:00+00'::timestamptz;  -- shows 2024-03-15 06:00:00-04

SET timezone = 'Europe/Moscow';
SELECT '2024-03-15 10:00+00'::timestamptz;  -- shows 2024-03-15 13:00:00+03

Why events should almost always be timestamptz

Any "when did this happen" column, a signup, a payment, a log line, is a moment in history. Store it as timestamp and you drop the zone, so two rows from Lisbon and Moscow become incomparable.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text,
  country    text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id         bigserial PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  amount     numeric(12,2) NOT NULL,
  status     text NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now()
);

Range queries are then correct no matter the server zone:

SELECT count(*)
FROM orders
WHERE created_at >= '2024-03-01 00:00+00'
  AND created_at <  '2024-04-01 00:00+00';

A zoneless timestamp is justified only rarely: a "date of birth", a "store opens at 09:00", things that are identical in every zone and do not name a specific instant.

The silent bug when you mix the types

The nastiest trap is assigning a "bare" zoneless string to a timestamptz column. PostgreSQL will not error: it quietly fills in the zone from the current session. Change the session and the meaning of your data changes too.

SET timezone = 'America/New_York';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');   -- interpreted as 10:00 New York

SET timezone = 'UTC';
INSERT INTO orders (user_id, amount, created_at)
VALUES (1, 50.00, '2024-03-15 10:00');   -- interpreted as 10:00 UTC

The two rows look identical but are different instants, hours apart.

Gotcha: a timestamptz::timestamp cast (and the reverse) does not convert the clock, it just strips or attaches a zone using session rules. Comparing a timestamptz to a timestamp also silently coerces one through the session zone. Always set the zone explicitly in the literal ('... +00') or pin SET timezone = 'UTC'.

Store in UTC, display in the client's zone

The working rule: store everything as timestamptz, and do the report-time conversion with the AT TIME ZONE operator on output.

SELECT
  id,
  created_at,                                  -- instant (UTC under the hood)
  created_at AT TIME ZONE 'Europe/Berlin' AS berlin_wall_time
FROM orders
ORDER BY created_at DESC
LIMIT 5;

AT TIME ZONE is a type switch:

• timestamptz AT TIME ZONE 'zone' yields a timestamp (the wall clock in that zone);
• timestamp AT TIME ZONE 'zone' yields a timestamptz (treats the naive time as local to that zone).

Grouping by an employee's "local day" looks like this:

SELECT
  e.dept,
  date_trunc('day', o.created_at AT TIME ZONE 'Europe/Berlin') AS local_day,
  sum(o.amount) AS revenue
FROM orders o
JOIN employees e ON e.id = o.user_id
GROUP BY e.dept, local_day
ORDER BY local_day;

The employees(id, name, manager_id, dept, salary) table ties each order to a team here.

Differences in MySQL and ClickHouse

• MySQL: the TIMESTAMP type stores UTC and converts via @@session.time_zone, which is closer to timestamptz, but its range is capped to 1970-2038. The DATETIME type stores no zone at all (the naive timestamp analog). For events people usually pick TIMESTAMP, or DATETIME plus a separate zone column.
• ClickHouse: DateTime stores Unix time (UTC) and can carry a zone name as a type parameter, DateTime('Europe/Berlin'); the zone affects parsing and display, not the stored value. For sub-second precision use DateTime64.

Bottom line: events go in timestamptz, naive timestamp is for zone-independent data only, convert with AT TIME ZONE on output, and always state the zone in literals so you never catch a silent shift.