Store points in time as timestamptz

Time bugs are some of the hardest to spot, because the data looks fine until servers, users or daylight saving rules disagree. Picking the right type up front removes a whole class of them.

• timestamp (without time zone) stores a wall-clock reading with no idea which zone it came from.
• timestamptz stores an absolute instant (internally in UTC) and converts on display using the session's TimeZone setting. Use it for created, updated and event times.
• date stores a calendar date. Use it for things like a birthday.

When zones differ, plain timestamp values become ambiguous: the same value can mean different instants.

Check an existing schema

Query information_schema.columns for data_type = 'timestamp without time zone' and plan a migration for every hit that records when something happened.

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  placed_at  timestamptz NOT NULL DEFAULT now(),
  ship_date  date
);