ChatGPT database query: define timezone and cutoff contracts before reporting

“What is revenue today?” sounds like a simple ChatGPT database query.

It is not simple.

Which timezone defines today? Does midnight mean the customer’s timezone, the company’s headquarters, or UTC? Are refunds assigned to the purchase date or the refund date? What happens to events that arrive tomorrow with yesterday’s timestamp?

SQL can execute without answering any of those questions. Natural-language reporting becomes reliable only when the time boundary is a data contract instead of an assumption.

“Today” is a business rule

Suppose the application stores payment events in UTC:

payments(
  id,
  occurred_at timestamptz,
  ingested_at timestamptz,
  amount_minor,
  currency,
  status
)

A user in Copenhagen asks at 00:15 local time: “How much did we sell today?” A naive query might use:

WHERE occurred_at::date = CURRENT_DATE

That expression depends on the database session timezone. The same question can return a different total when run by a developer laptop, a connection pool configured for UTC, or a reporting service in another region.

The model did not hallucinate. The system failed to define the reporting boundary.

Use half-open UTC intervals

Define the business timezone first, calculate local boundaries, and convert those boundaries to UTC. Then query with a half-open interval:

WHERE occurred_at >= :start_utc
  AND occurred_at <  :end_utc

The interval [start, end) avoids double-counting events at an exact boundary. It also handles days that are not 24 hours long during daylight-saving transitions, because midnight-to-midnight is calculated in the business timezone before conversion.

Do not create the end boundary by adding 24 hours to the UTC start. On a DST transition, that can describe the wrong local day.

The four fields every time-based answer needs

Attach an explicit time contract to the metric or approved query tool:

1. Business timezone: for example Europe/Copenhagen, stored as an IANA identifier rather than a fixed offset.
2. Reporting cutoff: midnight, 06:00 after batch settlement, end of business, or another defined boundary.
3. Event-time rule: which timestamp assigns a record to a period—creation, payment capture, fulfillment, refund, or ledger posting.
4. Late-arrival policy: whether historical periods are restated, frozen, or shown as provisional until a closing window expires.

Without those fields, “today,” “this week,” and “last month” are prompt vocabulary, not metric definitions.

A concrete answer contract

For a daily sales tool, the contract might be:

metric: captured_sales
business_timezone: Europe/Copenhagen
cutoff_local: 00:00
event_timestamp: captured_at
interval: [start, end)
statuses: [captured, partially_refunded]
refund_policy: subtract on refund_posted_at
late_arrival_window: 48h
currency_policy: group by currency; never sum mixed currencies
freshness: source watermark returned with every result

Now the AI does not need to invent temporal semantics from column names. It selects a governed tool or view whose behavior is reviewable and testable.

This is the same principle behind metric contract tests for natural-language SQL: the important question is not only whether the SQL runs, but whether the result means what the business thinks it means.

Comparison periods need symmetry

“Today versus yesterday” introduces another trap. At 10:00 today, comparing a partial day with all of yesterday will usually make today look worse. Define whether the comparison is:

• today-to-now versus yesterday-to-the-same-local-time;
• the last completed business day versus the previous completed business day;
• a rolling 24-hour window versus the preceding 24 hours;
• current calendar period versus the equivalent elapsed fraction of the previous period.

The answer should display the rule. A useful response says:

Captured sales for 2026-07-30 00:00–10:00 Europe/Copenhagen are DKK X, compared with the same elapsed window yesterday. Source data is complete through 09:56 local time.

The boundary, timezone, comparison shape, and freshness are part of the answer—not implementation trivia.

Late data changes the truth

Operational systems often receive events after the period they belong to. Payment processors retry webhooks. Warehouses finish batches after midnight. Mobile devices reconnect. A report run at 00:05 and the same report run at 08:00 may legitimately disagree.

Choose one of three honest policies:

• Restatable: historical totals can change; return an as-of timestamp and revision identifier.
• Provisional then closed: mark the period provisional until a defined delay expires, then freeze or formally adjust it.
• Ledger-posted: assign changes to the posting period so closed reports remain stable.

Do not let the AI silently choose among them. Store the policy with the metric definition and include the data watermark in the tool result.

Tests that catch temporal bugs

A robust ChatGPT database connector should exercise time semantics with fixtures, not only today’s production data:

• an event exactly at local midnight;
• an event one microsecond before the boundary;
• spring-forward and fall-back DST days;
• two tenants with different reporting timezones;
• a late event whose occurred_at and ingested_at fall in different periods;
• a refund posted after the original sale period;
• a leap day and a month-end cutoff;
• a partial-day comparison at the same local elapsed time;
• mixed currencies that must remain separate.

Freeze the test clock. Assert the exact UTC boundaries and seeded row set. Testing only the final total can hide an off-by-one boundary until a rare calendar transition.

Put time semantics in the tool result

A good result contract can include:

{
  "metric": "captured_sales",
  "value_minor": 1842500,
  "currency": "DKK",
  "period_start": "2026-07-29T22:00:00Z",
  "period_end": "2026-07-30T08:00:00Z",
  "business_timezone": "Europe/Copenhagen",
  "comparison": "previous_day_same_elapsed_window",
  "source_watermark": "2026-07-30T07:56:12Z",
  "status": "provisional"
}

The model can summarize this safely because the database tool supplies the meaning. It should not calculate boundaries from prose when a governed backend can do it deterministically.

Reconcile before sharing

Even a well-defined time window can be wrong because of joins, duplicate events, status mappings, or currency conversion. Apply the ChatGPT database query reconciliation checklist: compare the result with a known control total, expose row counts and watermarks, and investigate unexplained differences before the number reaches a customer or executive report.

For broader design guidance, the natural-language SQL guide explains why schema access alone is not enough. Business context must travel with the query path.

The contract to write down

Every time-based AI database answer must identify the business timezone, local cutoff, event-time field, half-open period boundaries, comparison rule, freshness watermark, and late-arrival status.

That contract turns “today” from a guess into a testable reporting definition.

Before connecting ChatGPT to a live reporting database, pick one high-value daily metric and write its time contract. The first DST test will tell you whether the system understands the business day—or merely knows how to call CURRENT_DATE.