The shop's day is not the server's day

Everything below is one bug in a live retail billing system: shipped on 9 September 2026, fixed on 14 September 2026, and read back out of the repository on 21 September 2026 for this note. The search results for this question are almost entirely people asking it — the oldest of them in 2008 — so what follows is the part none of those threads has: the query that was wrong, the query that replaced it, and the reasoning written down at the time.

The query shipped with the bug written above it

The sales report landed on 9 September 2026 grouping bills by the database's own idea of a calendar day:

to_char(bills.created_at, 'YYYY-MM-DD')

Above that function, in the same commit, was this comment:

Days are UTC, both in the range bounds and in the trend's to_char grouping. For a shop in IST that means the day boundary falls at 05:30 local, so an early-morning bill is counted against the previous day. Fixing it needs a per-shop timezone to convert with — there is no such setting yet, and guessing one from the server's clock would be worse than a known offset.

So the defect was known on the day the feature shipped, described correctly, and deferred with a stated reason. Worth being exact about what that record is and is not: there is no support ticket behind it and no shop reported a wrong total. A developer read the query, saw the boundary, and wrote down what it would do.

Five and a half hours of every day

India Standard Time is UTC+05:30 with no offset to look up, so the UTC calendar date lags the Indian one for the first five and a half hours of every Indian day. Computed in Node 22.14 for this note rather than quoted from anywhere:

The window is 5.5 hours out of 24, or 22.9% of every day. Which shops that actually bites is a fact about opening hours rather than about the code, and no figure for it exists here: nothing in this system records how many bills were written before dawn, so the honest claim is the size of the window and not the size of the damage.

The last row is the expensive one. A bill at 02:00 on the first of the month lands in the previous month on a UTC clock, and under Section 39 of the CGST Act 2017 a registered person furnishes a return "for every calendar month or part thereof". The Act names no time zone, and no reading of an Indian statute puts its months on UTC.

Where a timestamptz keeps its zone

A timestamptz column keeps no zone at all, which is the fact the whole problem rests on. PostgreSQL's documentation states it without qualification: all timezone-aware dates and times "are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client."

Cockroach Labs puts the consequence more bluntly in a note on the same types: neither type stores zone information, so "where does TIMESTAMPTZ get the 'time zone' display from? The session time zone!"

The session, then, is the thing that decides. And the TimeZone parameter takes its value from the machine the database was initialised on — "initdb will install a setting there corresponding to its system environment". On a managed database that environment is a data centre, not a shop. A hosted Postgres answers SHOW TimeZone with UTC, and every bare to_char or date_trunc in the application silently means UTC's day.

The query after the fix

The fix, in a commit dated 14 September 2026, converts before it formats:

to_char(bills.created_at at time zone 'Asia/Kolkata', 'YYYY-MM-DD')

AT TIME ZONE is doing the work, and PostgreSQL's function reference defines both directions of it. Applied to a timestamp with time zone it returns a timestamp without time zone — "the time as it would appear in that zone", which is exactly the wall-clock reading to_char should be formatting. Applied to a timestamp without time zone it goes the other way and builds an instant, "assuming the given value is in the named time zone".

Both directions appear in the fix, in one file, lib/shop-time.ts. The first is shopDay(), used by the report's GROUP BY. The second is shopDayStart() and shopDayEnd(), used by the date range.

date_trunc with a time zone argument

date_trunc has a third parameter that solves the same problem in one call, and it is the less widely known half of the answer. The signature in the PostgreSQL docs is date_trunc(field, source [, time_zone]), and the behaviour is spelled out: truncating a timestamp with time zone "is performed with respect to a particular time zone; for example, truncation to day produces a value that is midnight in that zone. By default, truncation is done with respect to the current TimeZone setting, but the optional time_zone argument can be provided to specify a different time zone."

Easinvy Retail's monthly quota predates that parameter in this codebase and uses the two-step form instead, in lib/entitlements.ts:

date_trunc('month', now() at time zone 'Asia/Kolkata') at time zone 'Asia/Kolkata'

Convert the instant to local wall-clock, truncate the wall-clock, convert the result back to an instant. Both forms are correct on PostgreSQL 12 and later; the three-argument one is shorter, and this code has not been changed to use it. The comment above the two-step version gives the reason it exists at all:

Without the round trip the boundary lands 5h30m out, and a shop billing late on the last evening of the month would have those bills counted against the next one.

Worth noting where that leaves the two features: the plan's "bills this month" was counted in Indian time before the sales report was. The quota had the right clock and the report did not, in the same product, for five days.

The range bounds ran into tomorrow

The GROUP BY was only half of it. The report's date range was parsed in JavaScript, and a request for to=2026-09-15 became a bound of 2026-09-15T23:59:59.999Z — which is 05:29:59 on the 16th in Indian time. A report asked for "up to today" ran five and a half hours into tomorrow, while a report asked for "from the 1st" began at 05:30 on the 1st: hours missing from one end of the range and borrowed at the other. After the fix the same bound carries +05:30 instead of Z, from a named constant.

The offset is written as a literal, and that is a claim about India specifically rather than a general technique. The IANA time zone database is where it gets checked: the Zone Asia/Kolkata record ends with a plain 5:30 - IST rule effective from 15 October 1945, with no daylight saving rule after it. A literal offset is safe here because that line exists. In a zone with a DST rule the same literal is a bug waiting for a Sunday in March — which is why shopDayStart() builds its bound in SQL rather than in JavaScript, so the conversion goes through the zone database instead of through a number.

A constant, not a per-shop setting

Six of the eight results Google returns for this question are forum threads, and the ones that reach an answer mostly reach the same one: set the time zone in your profile, your store settings, your account. Easinvy Retail deliberately did not build that setting, and wrote down why:

A constant rather than a per-shop setting, deliberately. A setting is the right answer the day there is a shop outside IST, and it is a column, a form field, a migration and a default for every existing row — all to express what is currently one value. Named here so that day is a change to one file rather than a search for every date_trunc.

Being honest about how well that worked requires counting. A search across the backend source on 21 September 2026 found six files still writing the zone inline instead of reading the constant: the monthly quota in lib/entitlements.ts, the matching payments query, an attendance module with its own INDIA_TIMEZONE, two PDF routes falling back to the literal, and an admin route that writes the raw offset as T12:00:00+05:30. None of them is wrong today. All of them are places the promised one-file change is not.

A note on what this pattern costs and what it does not: the constant is not a substitute for the setting, it is a decision to defer the setting with the deferral recorded. The threads asking vendors for a per-user zone are asking the right question of products that have customers in more than one country. Easinvy Retail has customers in one, and says so in the file.

The comment gets its own example wrong

The fix is correct and its explanation is not, which is worth publishing rather than quietly repairing. The commit message, the header of lib/shop-time.ts and the comment in db/queries/sales.ts all illustrate the bug the same way: "a bill written at 6am on Tuesday counted against Monday."

A 6am bill was always counted on Tuesday. 06:00 IST is 00:30Z, which is already the new UTC day — the first row of the table above. The bills that landed under Monday were the ones written between 00:00 and 05:29. Three pieces of prose are wrong by the same half-hour while the SQL they describe is right, and the one comment that has it exactly right is the least prominent of them, in the route: "'today' began at 05:30 this morning and a range ending today stopped at 05:29 tomorrow."

The general point underneath the specific one: a comment explaining a boundary is itself a boundary calculation, and nothing checks it. The tests run against the code.

Four clocks in one shop

Four different clocks format a time somewhere in this product, and knowing which screen reads which is the whole of the subject:

The fix moved the report from the first clock to the third. The bill lists still read the fourth, and on an Indian phone set to Indian time all four agree — which is the reason a bug like this survives a demo. The question to ask of any screen showing a date is not whether the data is right but which of the four clocks formatted it.

What this does not check

No automated test covers any of it. The backend has twelve self-test suites and not one of them touches shop-time.ts, shopDay() or the report's grouping — the 37 checks the fixing commit reports are the password-reset suite that shipped in the same commit. The correctness argument for the day boundary is a code review and the table in this note, which is weaker than a test and is being described accurately rather than upgraded.

Two more limits worth stating. The daylight-saving path has never run: shopDayStart() is built to survive a DST change and has never been executed in a zone that has one, so it is untested rather than proven. And a comparison against other products is not available here — of sixteen pages read while researching this note, two could not be reached, and the ones that were are quoted for what they say rather than extended.

More on how this system stores and reads its data in database solutions, and the billing product whose report this is at Easinvy Retail.