How to Handle Timezones in a Database

Quick answer
Store instants — when something happened — in UTC, as a timezone-aware column (timestamptz in PostgreSQL).
Store the user’s zone separately as a name such as Asia/Kolkata, not as an offset, because offsets change with daylight saving.
Convert to a local zone only for display, at the edge, never for arithmetic or storage.
A recurring event needs a local time and a zone, because “9am daily” is not a fixed instant.
Converting offsets rather than zone names is the classic failure: it breaks twice a year and only for some users.

What is the rule for timezones in a database?

Store the instant in UTC and store the zone separately when you need one. A moment in time is a single point, and UTC expresses it unambiguously; a zone is a rule for mapping that point to a local clock, and it belongs to the entity — the user, the event — rather than to the instant. Keeping those two things distinct resolves most time bugs before they happen, because every ambiguity you might have is a question of which of the two you actually meant.

In PostgreSQL, choose timestamptz for instants: it stores a UTC point and converts on display according to the session zone. The confusingly named timestamp without time zone stores a wall-clock reading with no offset, which is right only for a local time whose zone you store alongside. In MySQL, prefer TIMESTAMP for instants and DATETIME for local wall-clock values. The naming is unhelpful in both engines, which is why the decision belongs in a written convention rather than in a habit.

Step 1 — classify every time column you have

For each column, decide which of three things it is. An instant, such as created_at or last_login, is a point in time and goes in a UTC column. A local time, such as a store’s opening time of 9am, is a wall-clock reading that is meaningless without a zone, so it needs a companion zone column. A date, such as an invoice date, is a calendar day, and it should be a date type rather than a timestamp at midnight, so it cannot drift by a few hours into the previous day.

Write the answers down in the schema documentation. This step is where bugs are prevented, because the ambiguity is resolved by a human who knows the business meaning rather than by a default that the database chose. Databases for AI Builders is a useful map of the surrounding decisions.

-- Instants: a point in time, stored as UTC.
ALTER TABLE users ADD COLUMN created_at timestamptz NOT NULL DEFAULT now();

-- A user's zone: the name, not the offset. Names carry the rules.
ALTER TABLE users ADD COLUMN timezone text NOT NULL DEFAULT 'UTC';

-- A recurring event: local wall-clock time plus the zone it is local to.
CREATE TABLE reminders (
  id         bigserial PRIMARY KEY,
  local_time time        NOT NULL,   -- 09:00
  timezone   text        NOT NULL,   -- 'Europe/Berlin'
  user_id    bigint      NOT NULL REFERENCES users(id)
);

-- A calendar date: use a date, not a midnight timestamp.
ALTER TABLE invoices ADD COLUMN issued_on date NOT NULL;

Step 2 — convert only at the edges

Keep the application’s internal handling in UTC and convert in two places only: when formatting for a user, using that user’s stored zone name, and when parsing input that the user provided in their own zone. Everything between those two boundaries stays in UTC. This is the discipline that makes the arithmetic safe, because adding a day to a UTC instant is exact, while adding a day to a local time may cross a daylight-saving transition of twenty-three or twenty-five hours.

Set the session or connection timezone explicitly as well. A database session with a different default zone than your application produces different results for the same query, and that difference is invisible until someone runs it from a different machine. Setting it in the connection configuration removes the dependence on the server’s own setting.

-- Reading: convert to the user's zone for display only.
SELECT created_at AT TIME ZONE u.timezone AS local_created_at
FROM users u WHERE u.id = 42;

-- Boundaries in the user's local day, computed correctly.
-- A day is not 24 hours; let the database apply the zone's rules.
SELECT count(*) FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= (date_trunc('day', now() AT TIME ZONE u.timezone)
                       AT TIME ZONE u.timezone);

-- Pin the session zone so results do not depend on the server default.
SET TIME ZONE 'UTC';

Step 3 — handle the two cases that break naive code

Recurring events are the first. A daily reminder at 9am is not an instant, so storing it as a UTC timestamp and adding 24 hours drifts by an hour at each daylight-saving transition — twice a year, for the affected users, which is exactly the kind of bug that survives testing. Store the local time and the zone, and compute each occurrence by combining them in that zone. The second is user-local boundaries: “today’s orders” means today in the user’s zone, which is not midnight to midnight in UTC. Compute the boundary from the zone rather than assuming a fixed offset.

The third, less obvious case is the ground truth from third parties. Payment providers, calendars, and external APIs often return timestamps with an offset or in their own convention, and the reliable habit is to convert to UTC with the offset respected at the moment of receipt, then discard the offset. Reinterpreting an offset-bearing value as if it were UTC is how a four-hour discrepancy appears between two systems that both believe they are correct.

Step 4 — test the transitions, not the happy path

Write tests that run around daylight-saving transitions in a zone that has them, and around a zone that does not, such as Asia/Kolkata with its half-hour offset. Test a user day boundary across midnight in Kiritimati and across the other side in Honolulu, because those are the extremes that expose an assumption of UTC. Test that a stored local time plus a zone produces the intended instant on both sides of a transition.

Then check the storage convention itself: confirm that a value written at a known instant reads back as that same instant regardless of the connection’s zone. A passing test suite around these cases is worth more than any amount of care in review, because the failure mode is a quiet hour of drift rather than an exception.

Where this bites vibecoders
Generated code stores timestamps with whatever type the ORM defaults to, which is frequently a naive local timestamp, and it computes local days by adding or subtracting hours. Both look correct on the developer’s machine, which is in one zone, and both break for users in another or at a daylight-saving change. Ask explicitly for UTC storage and for zone-aware conversion, and add the timezone column to the user model before any user-facing date logic exists.

Where AI coding assistants get this wrong

Storing naive local timestamps and calling them UTC.
Storing a numeric offset instead of a zone name, which breaks at daylight-saving transitions.
Adding 24 hours to compute the next occurrence of a recurring local event.
Computing “today” as midnight-to-midnight in UTC for users in other zones.
Treating a date as a timestamp at midnight, which can shift to the previous day.
Relying on the server’s default session timezone, which differs between environments.

Checklist

Classify every time column as an instant, a local time, or a date.
Store instants in a UTC-aware column and set the session zone explicitly.
Store zone names, not offsets, next to local times.
Convert only when formatting output and when parsing input.
Model recurring events as local time plus zone.
Test around daylight-saving transitions and in a non-whole-hour zone.

FAQ

Should I use timestamp or timestamptz in PostgreSQL?

Use timestamptz for instants, which is almost always what you mean. Despite the name it does not store a zone; it stores a UTC point and converts for display. timestamp without time zone stores a wall-clock reading with no offset, which is correct only when you are also storing the zone alongside it.

Why not just store the UTC offset?

Because an offset is a snapshot of a rule, not the rule itself. A user in Berlin is +01:00 in winter and +02:00 in summer; storing +01:00 makes every summer date an hour wrong. Store the zone name, such as Europe/Berlin, and let the timezone database apply the current rules.

How do I handle a recurring event like a daily reminder?

Store the local time and the zone, not a UTC instant, and compute each occurrence by combining them. Adding 24 hours to a UTC timestamp drifts by an hour at daylight-saving transitions. This is the clearest case where the zone is part of the data rather than a display detail.

What about time stored by third-party APIs?

Convert it to UTC at the moment you receive it, respecting the offset the API supplied, then store the UTC instant. Do not reinterpret an offset-bearing value as though it were already UTC, which is how the same moment becomes different between two systems. Keep the source value if you need to reconcile with them later.