“What is revenue today?” sounds like an easy database question.
It is not.
Which timezone defines today?
Does the reporting day close at midnight or after a settlement batch?
Do refunds belong to the original sale date or the refund posting date?
What happens when yesterday’s event arrives tomorrow?
SQL can run successfully without answering any of those questions.
That is how an AI returns a precise number with the wrong meaning.
CURRENT_DATE is not a business definition
Imagine a payments table with UTC timestamps. A user in Copenhagen asks at 00:15 local time for today’s sales.
A naive query uses:
WHERE occurred_at::date = CURRENT_DATE
The result depends on the database session timezone. A developer laptop, a UTC connection pool, and a regional reporting service can return different totals for the same sentence.
The model did not hallucinate.
The system never defined the reporting boundary.
Define local boundaries, then query in UTC
The safe pattern is:
- Choose the business timezone as an IANA identifier, such as
Europe/Copenhagen. - Calculate the local start and end boundary.
- Convert both boundaries to UTC.
- Query with a half-open interval.
WHERE occurred_at >= :start_utc
AND occurred_at < :end_utc
Do not calculate the end by adding 24 hours to the UTC start. A local day can be 23 or 25 hours during daylight-saving transitions.
Every time-based metric needs a contract
At minimum, define:
- business timezone
- local reporting cutoff
- timestamp that assigns an event to a period
- half-open boundary rule
[start, end) - late-arrival policy
- freshness watermark
- comparison-window rule
“Today versus yesterday” needs symmetry too.
At 10:00, do you compare today-to-now with all of yesterday? The same elapsed local window yesterday? The last two completed business days?
Each is defensible. They are not interchangeable.
A trustworthy answer should expose the choice:
Captured sales from 00:00–10:00 Europe/Copenhagen are X, compared with the same elapsed window yesterday. Source data is complete through 09:56. The period is provisional.
The timezone, boundary, comparison shape, and freshness are part of the answer—not hidden implementation details.
Late data changes the truth
A report run at 00:05 and the same report run at 08:00 may legitimately disagree. Payment processors retry webhooks. Warehouses finish batches late. Mobile devices reconnect.
Pick an honest policy:
- historical periods can be restated, with an as-of timestamp and revision
- periods remain provisional until a closing window expires
- changes are assigned to the ledger posting period so closed reports remain stable
The AI should not silently choose among them.
Test the calendar, not only the SQL
Seed fixtures for:
- exactly midnight
- one microsecond before the cutoff
- both daylight-saving transitions
- two tenants with different timezones
- late-arriving events
- refunds posted in a later period
- month-end and leap day
- mixed currencies
Freeze the clock and assert the exact UTC boundaries plus the included row set.
Testing only the final total can hide an off-by-one boundary until the rare day it matters most.
Natural language is a useful interface. But words like “today,” “this week,” and “last month” are business rules. The model should receive those rules from a governed database tool instead of inventing them from a prompt.
The full contract, PostgreSQL examples, result shape, and test matrix are here:
ChatGPT database query: define timezone and cutoff contracts before reporting
Top comments (0)