# The report that is off by exactly one day: tracing time zones, DST and date formats in client data

> When dashboard revenue is wrong by exactly one day, the cause is rarely the formula. It is usually a timestamp that nobody labelled with a time zone.

Original: https://fdetimes.net/en/guides/report-off-by-one-day-time-zones/

On Monday morning the client sends a screenshot. Friday's revenue on the dashboard you just deployed is lower than the figure in their point-of-sale system, and Saturday's is higher. The weekly total still matches; only some of the late-night orders have been pushed into the next day.

Faced with this kind of bug, many people's first instinct is to open the SQL and check the formula. Usually the formula is fine. The problem is that a string such as `03/04/2024 23:30` has passed through three systems, and each of them guessed which time zone it belonged to.

For an FDE this is not unusual. You are the first person to touch the client's real data: exports from an ERP, logs from servers on another continent, spreadsheets someone typed by hand.

Get time right from the start and the client will trust your numbers. Get it wrong and every meeting afterwards will revolve around the question "why is this number off?"

## Why is a timestamp ambiguous?

Take the string `01/11/06`. Read as day-month-year, it is 1 November 2006. Read in the American month-day-year order, it is 11 January 2006.

Read as year-month-day, it is 6 November 2001. No library can guess correctly for you, because this is a convention of whoever exported the file, not a property of the data.

Once the format is clear, the next question is the time zone. The Python documentation calls a datetime without a time zone "naive": it does not carry enough information to locate itself unambiguously relative to other timestamps.

Whether it represents UTC or local time is left to the program to decide. Python also recommends using "aware" datetimes, which carry a time zone, to represent UTC, because many methods treat naive datetimes as local time. That is also why `utcnow()` was deprecated in version 3.12.

JavaScript is even less predictable. According to MDN, when a string has no offset, the date-only form is interpreted as UTC while the date-time form is interpreted as local time. MDN acknowledges this as a long-standing specification error that is inconsistent with ISO 8601, but it cannot be fixed without breaking web compatibility.

```js
new Date("2024-03-01")        // midnight UTC
new Date("2024-03-01T00:00")  // midnight in the running machine's local time
```

In a browser in a negative-offset time zone, such as anywhere in the US, the first line displays as the evening of 29 February. That is all it takes for a date picker on a dashboard to slip back a day.

## What should a timestamp carry with it?

For data to be unambiguous, every timestamp needs three things: the absolute instant, the name of the time zone where the event happened, and the report's convention for what a "day" is. Miss any one of the three and a bug will appear sooner or later.

The absolute instant should be stored in UTC. When exchanging it as a string, use ISO 8601 with the `Z` suffix, for example `2024-03-05T05:30:00Z`, so nobody has to guess.

Remember one thing about PostgreSQL: the `timestamp with time zone` type stores its value internally in UTC and does not retain the original time zone. If you later need to know which store an order was placed in, and on whose clock, you have to store that time zone in a separate column.

That column should hold an IANA time zone name in Area/Location form, such as `America/Chicago` or `Asia/Ho_Chi_Minh`, not an abbreviation. The IANA tz documentation gives CST as an example: it means one thing in China and another in North America.

A fixed offset such as `-06:00` is not safe either, because governments can change their time rules in the future. Store the zone name and keep the tzdata package up to date, and the system will calculate correctly when the rules change.

**Key point:** Store the instant in UTC, store the time zone name separately, and only convert to a "day" when building the report.

## Applying this to a chain of stores in Chicago

Imagine the client is a chain of stores in Chicago. Their point-of-sale system exports a CSV with an `order_time` column in the form `03/04/2024 23:30`, with no time zone.

The first job is not writing code but asking the client two questions: is the order in the file month/day or day/month, and is the time in the file store time or server time?

Suppose the client confirms month/day and store time.

```python
from datetime import datetime
from zoneinfo import ZoneInfo

STORE_TZ = "America/Chicago"   # IANA name confirmed by the client, not "CST"

def parse_order_time(raw: str) -> datetime:
    naive = datetime.strptime(raw, "%m/%d/%Y %H:%M")  # MDY, as confirmed by the client
    local = naive.replace(tzinfo=ZoneInfo(STORE_TZ))   # naive -> aware
    return local.astimezone(ZoneInfo("UTC"))           # store in UTC
```

When writing to the table, `order_ts` is of type `timestamptz` and `source_tz` is a text column holding `America/Chicago`. The daily report converts to local time first and only then cuts the day:

```sql
SELECT date(order_ts AT TIME ZONE source_tz) AS business_day,
       sum(amount)
FROM orders
GROUP BY 1;
```

The bug in the opening screenshot typically comes from grouping by `date(order_ts)`, that is, by UTC day. An order placed at 23:30 on Friday night in Chicago is already Saturday in UTC. Change one line of `GROUP BY` and revenue returns to the right day.

## The repeated hour when DST ends

One case needs separate treatment. PostgreSQL notes that a time zone's offset changes through the year because of DST, so a time without a date is almost meaningless. On the night DST ends, a stretch of local time occurs twice. Two orders both recorded at `01:30` may be an hour apart in reality.

Python handles this with the `fold` attribute: `fold=0` uses the offset before the transition, `fold=1` the offset after. The `strptime` function above always yields `fold=0`.

If the client's file has transaction sequence numbers or increasing log entries, you can use them to decide which rows belong to the second occurrence and set `naive.replace(fold=1, ...)`.

If it does not, at least flag the rows that fall in that window and tell the client.

DST also affects working schedules. Vietnam uses UTC+7 all year round, with no DST.

A team working on Vietnam time with a client in the US or Europe will see the time difference shift with the seasons, so a cron job that "runs at 8am client time" must be configured with the client's time zone name, not an offset calculated once and left alone.

## Five questions to ask in the first discovery session

The two questions in the Chicago example are only the start. Every ambiguity above comes down to five questions, and asking them early is far cheaper than fixing a dashboard after the client has taken a screenshot.

| Question for the client | Why it matters |
|---|---|
| Is the date order in the file DMY, MDY or YMD? | `01/11/06` has three readings, and no library can guess for you |
| Is the time in the file store time, server time or UTC? | A naive datetime does not say which time zone it belongs to |
| Which IANA time zone does each location use? | Abbreviations such as CST are ambiguous; fixed offsets go wrong when rules change |
| When does "a day" in the report end, and on whose clock? | UTC day and store day differ for late-night orders |
| On the night DST ends, does the data have sequence numbers to tell repeated hours apart? | The same local time can correspond to two real instants |

Record the answers in the handover document, next to the column names. Whoever takes over the project after you will not have to ask again.

## Common mistakes, and how to turn them into a story to tell

A few mistakes come up again and again. Letting a library guess the day/month order. Calling `datetime.now()` or `utcnow()` and assuming you have UTC. Storing `CST` or `-06:00` in the time zone column. Passing a `YYYY-MM-DD` string straight into `new Date()` on the frontend.

Grouping reports by UTC day while the client reads figures by store day. Code with these mistakes runs perfectly on your machine and only breaks when it meets real data in another time zone.

The fastest way to learn is to reproduce the opening screenshot yourself. Create an `orders` table with a few dozen fake orders in `America/Chicago`, including some after 23:00 on Friday night and some on the night DST ends. Run the report with `date(order_ts)`, note how much Friday falls short, then switch to `AT TIME ZONE source_tz` and compare again.

That exercise gives you a complete story: the symptom, the hypothesis, the questions put to the client, the line of code that was fixed.

Once you have done it on real data, put a specific line on your CV, such as "normalised timestamp columns from multiple time zones to UTC with IANA time zone names, fixing reports that were off by a day", and tell it in exactly that sequence in interviews.

Next time a client asks why a figure is off by exactly one day, do not open the formula first. Ask where that timestamp was created, and on whose clock.

**Try this week:**

- Open a real client CSV, list every time column and note beside each one its format, time zone and source system. Any column you cannot answer for goes on the list of questions for the client.
- Grep the codebase for `new Date("`, `utcnow()` and `datetime.now()` without a tz argument, and change each one to an explicit time zone-aware form.
- Write a test that ingests a row at 23:30 in the client's local time and asserts that the report places it on that same day.

## Sources

- [Date - JavaScript | MDN](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date)

- [datetime — Basic date and time types (Python 3.15.1 documentation)](https://docs.python.org/3/library/datetime.html)

- [PostgreSQL: Documentation: 18: 8.5. Date/Time Types](https://www.postgresql.org/docs/current/datatype-datetime.html)

- [zoneinfo — IANA time zone support (Python 3.15.1 documentation)](https://docs.python.org/3/library/zoneinfo.html)

- [Theory and pragmatics of the tz code and data](https://data.iana.org/time-zones/tzdb/theory.html)

- [List of date formats by country - Wikipedia](https://en.wikipedia.org/wiki/Date_format_by_country)

- [Time in Vietnam](https://en.wikipedia.org/wiki/Time_in_Vietnam)

- [ISO 8601 - Wikipedia](https://en.wikipedia.org/wiki/ISO_8601)