Nov 9, 2026

Your dashboard is showing the wrong day — it's a timezone bug

A sale made at 11pm shows up on the wrong day, and the daily totals change depending on who is looking at the dashboard. The data is fine. The day boundary is the bug.

I have shipped this bug more than once, and every time it looked like the data was wrong. A dashboard groups orders by day, and the daily totals don't match the report someone exported yesterday. A sale made late at night shows up on the wrong day. Two people open the same dashboard and see different numbers for the same date.

The data is fine. The bug is the day boundary — whose midnight you used to decide which day a row belongs to.

A timestamp is a moment in time. A day is a wall-clock idea that depends on where you are standing. 11:30pm on Monday in Dhaka is still Monday afternoon in London and already Tuesday morning in Sydney. Group by UTC days and your "day" starts at 6am Dhaka time. Group by the viewer's local day and the same row lands on a different date for every timezone that looks at it. Neither is wrong in the abstract. Both are wrong if you picked one without deciding.

Three rules keep this from happening again.

Store instants, not wall-clock readings. In PostgreSQL that means timestamptz for anything that happened — created_at, published_at, every event time. timestamptz stores an absolute instant: what you insert is converted to UTC, and what you select is rendered in the reading session's timezone. timestamp without time zone stores exactly the digits you gave it, with no offset and no meaning. The same value read from a session in another timezone is a different moment, and nothing in the column will tell you. The Postgres wiki lists "don't use timestamp (without time zone)" alongside its advice against char(n) and money, and it is right. Both types cost 8 bytes, so the correct one is free.

The same rule applies to the API. Send ISO 8601 with the trailing Z — 2026-11-09T17:30:00Z — and never a bare string like 2026-11-09 17:30:00 with no offset. Six months from now, nobody will reliably know what zone that string meant.

Convert to local time before you group, not after. This is the one that actually fixes the dashboard. Grouping by UTC day is the default in most queries, and it is wrong for any report a human reads in their own timezone:

-- Groups by UTC days: your "day" starts at 6am in Dhaka
SELECT date_trunc('day', created_at) AS day, count(*)
FROM orders GROUP BY 1;

Convert first, then truncate:

-- Groups by Dhaka days
SELECT date_trunc('day', created_at AT TIME ZONE 'Asia/Dhaka') AS day, count(*)
FROM orders GROUP BY 1 ORDER BY 1;

Name the zone — Asia/Dhaka, America/New_York — never a fixed offset like +06:00. A fixed offset is right until daylight saving moves it, and then every day boundary shifts and rows change days twice a year. Let the timezone database do the work.

Format at the edge, and only at the edge. Local time enters the picture at the very last step, when a human is about to see the value. In the browser, Intl.DateTimeFormat does the conversion for you, daylight saving included, with no library:

const formatter = new Intl.DateTimeFormat("en-GB", {
  timeZone: "Asia/Dhaka",
  dateStyle: "medium",
  timeStyle: "short",
});

formatter.format(new Date("2026-11-09T17:30:00Z"));

Pass an explicit timeZone. toLocaleString() without one uses the browser's local zone, not the zone you intended — which is the same bug wearing a different coat. And never parse a string without a Z or offset as if it were UTC: new Date("2026-11-09T17:30:00") is interpreted in the visitor's own timezone, silently shifting every timestamp by their offset.

Daylight saving is where the naive version of this breaks quietly. When clocks jump forward, one hour of local time simply does not exist; when they fall back, one hour repeats. Add one hour to 01:30 on a spring-forward night and a naive timestamp happily gives you 02:30 — a time no clock showed that night — while timestamptz gives you the correct instant. Any job duration, session length, or SLA timer that spans the switch is off by an hour, twice a year, with no error anywhere.

There is one honest exception, and it is worth naming: a plain timestamp is the right type for a wall-clock value that is meant to mean the same thing in every zone — a shop that opens at 9:00am, a reminder that should fire at 8:00am wherever the user is. And a calendar date with no time component, like a date of birth, belongs in a date column, not a timestamp you will spend the rest of the project stripping times off. The rule is not "always timestamptz" — it is "know whether you are storing a moment or a wall-clock reading, and pick the type that says so."

The dashboard never lied. It just answered a question nobody asked: which rows fall on the same UTC day. Decide whose day you mean, store the instant, convert before you group, and format at the edge. The totals will stop moving.