Why Your Report Is Off by One Day.

By Muhammad Umar·June 12, 2026·7 min read·Issue #18

The dashboard says 47 orders on Tuesday. The operations team counted 49. Both numbers were produced carefully by people who know what they are doing, and neither of them is wrong.

You will spend a day on this. You will check the query, find nothing, check it again, and eventually notice that the two orders in dispute were placed late in the evening.

The bug is not in the query. It is in a word that appears in the requirement and looks like it means something specific.

"Tuesday" is not a property of an order. It is a property of an order and a place, considered together, and if you do not say which place then the answer is whatever your database happened to assume.

Two different things wearing the same name

There are two kinds of time in software and most codebases store both in the same column type.

An instant is a point on the timeline. The moment a payment cleared. It is the same moment for everyone, no matter where they are standing, and it is completely unambiguous.

A calendar date and time is what a clock on a wall says. Nine in the morning on the third. That is not a point on the timeline until you also say which wall.

One order, placed once, and it lands on two different calendar dates depending on who is counting. Neither report is miscounting. They are answering different questions, and the requirement never said which.

Every timestamp bug is the same bug: something converted between an instant and a calendar without being told which calendar.

What your database actually stores

This trips people because the type names promise more than they deliver.

PostgreSQL has timestamp with time zone, which sounds like it stores one. It does not. It converts the input to UTC, stores that instant, and converts back on output using the session time zone.¹ The zone you supplied on the way in is not kept.

-- Same instant, and the answer depends on who is asking.
SET TIME ZONE 'UTC';
SELECT created_at::date FROM orders WHERE id = 1;   -- 2027-02-03

SET TIME ZONE 'America/Los_Angeles';
SELECT created_at::date FROM orders WHERE id = 1;   -- 2027-02-02

That is not a defect. Storing an instant is exactly right for something that happened. The trap is that casting it to a date silently uses whatever the session zone happens to be, so a report can change its answer because it ran from a different machine.

The other type, timestamp without time zone, stores the wall-clock reading and no zone at all. Two rows written from Karachi and Los Angeles sit in the same column with nothing to distinguish them, and the information needed to compare them is gone.

Three things that make it worse

Some days are not 24 hours long

In places that observe daylight saving, one day a year has 23 hours and another has 25.

So "add 24 hours" and "add one day" are different operations, and they give different answers twice a year. Anything scheduled by adding a fixed number of seconds drifts by an hour relative to the wall clock, which is why a nightly job at 02:30 can run twice or not at all.

Local times near the transition are worse. In spring, some wall-clock times never happen. In autumn, some happen twice, and a timestamp of 01:30 on that date is genuinely ambiguous with no way to resolve it from the value alone.

An offset is not a time zone

A timestamp like 2027-02-03T09:00:00+05:00 looks complete and is not. It carries an offset, which is what the clock was doing at that instant, and it does not say where.²

That distinction matters because offsets change and zones are the thing that knows when. Europe/Berlin is a set of rules. +02:00 is one reading of those rules on one day of the year. Given only the offset you cannot work out what the clock will say next winter.

Future events are not instants yet

This is the one that produces the strangest bugs, because the data is correct when written and becomes wrong later.

Someone books a meeting for 09:00 in Berlin, eight months out. You convert it to UTC and store the instant. Done.

Then a government changes its rules. This is not rare: the IANA database that everyone relies on is updated several times a year, and changes are sometimes announced weeks before they take effect.³ Your stored instant is still the instant you computed. It is no longer 09:00 in Berlin, and the person who booked it will arrive an hour out.

The user did not want an instant. They wanted a wall-clock time in a place, and converting early threw away the thing that would have let you recompute it.

What to do instead

Store what actually happened as an instant

Anything in the past is an event that occurred at a moment. Store it as an instant, in a column that means instant, and never as a local wall-clock reading without a zone beside it.

This part most teams get right. The mistakes come later, when the instant gets converted.

Store future intentions as a time and a zone

For anything scheduled, keep the local time and the zone identifier as separate values, and compute the instant when you need it rather than when you save it.

CREATE TABLE meetings (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  local_starts  timestamp NOT NULL,   -- what the invitation says
  zone          text NOT NULL,        -- 'Europe/Berlin', an IANA name
  created_at    timestamptz NOT NULL DEFAULT now()
);

Now a rule change is handled by updating the time zone database, which happens anyway, rather than by finding and rewriting rows.

Wrong when the fixed thing genuinely is the instant. A rocket launch, a market open in a specific exchange, a coordinated cutover. Those should stay as instants, and the local time is the derived value.

Say which day you mean, in writing

For every report that groups by day, the specification needs one more sentence: whose day.

• The customer's local day, which is what a customer-facing summary should use.
• The company's operating day, which is what finance and operations usually mean.
• The UTC day, which is unambiguous, easy to compute, and matches nobody's experience.
• A business day with a defined cutoff, such as an operating day running 06:00 to 06:00.

All four are legitimate and they produce different numbers. The failure is not picking the wrong one. It is picking one implicitly and having a different implicit choice made somewhere else in the system.

Do the grouping in the zone that matters

Once you have decided whose day it is, convert explicitly at the point of grouping and never rely on the session default.

-- The zone is part of the question, so it belongs in the query.
SELECT (created_at AT TIME ZONE 'Europe/Berlin')::date AS day,
       count(*)
FROM orders
GROUP BY day
ORDER BY day;

This query gives the same answer from any machine, in any session, run by anyone. The previous version gave whatever the server was configured to think.

When this is the wrong advice

When everything is in one zone and always will be. A tool used by one office in one country can treat local time as the only time, and the machinery here is overhead against a problem that will not arrive. Be honest about whether that is durable, because a first customer abroad turns it into a migration.

When the value has no time in it. A date of birth is not midnight anywhere. Storing it as an instant creates a bug where none existed, because now it can shift a day depending on how it is read. Some things are plain dates and should stay that way.

When UTC genuinely is the business truth. Infrastructure logs, audit trails, and anything compared across regions are clearer in UTC and should stay there. The point is not that UTC is wrong. It is that UTC is a choice, and it should be a stated one rather than the accident of a server default.

The takeaway

Nobody writes a timestamp bug on purpose. They get written because "day" reads like a fact about an event, when it is really a fact about an event and an observer, and one of those goes missing every time the requirement is written down.

So when someone next asks for a figure grouped by day, the useful reply is one question. Whose day? It takes ten seconds, and it is the difference between two teams with two numbers and two teams with one.