A timestamp is not a time context

UTC preserves when an event occurred. It does not establish what a user means by “today,” “last night,” or “this week.” Those phrases refer to a calendar owned by a person or business, not to the server clock.

A reliable agent separates three decisions. The model interprets the phrase, deterministic code resolves it against an authoritative timezone, and the data layer uses the resulting UTC interval and local date keys consistently.

Store activity time and receipt time separately

This section preserves the difference between when an action happened and when the backend observed it. Collapsing those values moves delayed or offline events into the wrong reporting period.

A minimal event contract keeps both clocks visible:

export type CapturedEvent = {
  occurredAt: string;
  receivedAt: string;
  deviceTimezone?: string;
  accountTimezone: string;
};

export function assertTimestamp(value: string, field: string): Date {
  const parsed = new Date(value);
  if (Number.isNaN(parsed.getTime())) {
    throw new Error(`${field} must be an ISO-8601 timestamp`);
  }
  return parsed;
}

occurredAt drives analysis because it represents the action. receivedAt remains useful for diagnosing ingestion lag. The device timezone is evidence about capture context, while the account timezone defines the calendar used by the product.

Turn intent into a structured time request

This section limits the model to semantic interpretation. The model does not calculate offsets or produce SQL timestamps.

The model-facing shape can stay deliberately small:

export type TimeQuerySpec =
  | { kind: 'relative_day'; value: 'today' | 'yesterday' }
  | { kind: 'relative_week'; value: 'this_week' | 'last_week' }
  | { kind: 'explicit_dates'; startDate: string; endDate: string };

An agent can map “what happened yesterday evening?” to { kind: 'relative_day', value: 'yesterday' }. The application then resolves that spec using an account setting retrieved from an authoritative service.

Do not ask the model to return startUtc and endUtc. Timezone databases contain daylight-saving transitions and historical changes that should remain in tested code.

Resolve the business calendar deterministically

This section converts a structured request into a half-open UTC range and matching local date keys. The resolver takes the time specification, authoritative timezone, calendar policy, and a controllable “now.” It returns UTC boundaries, local date boundaries, and the timezone that produced them.

The important property is ownership. A timezone library owns offset and daylight-saving arithmetic. Product policy owns concepts such as the first day of the week. The model owns only the interpretation of language into the bounded specification.

The interval should be [startUtc, endUtc). An event exactly at the next midnight belongs to the next period, which prevents adjacent windows from double-counting it. Explicit date requests should use the same convention instead of inventing an inclusive timestamp such as 23:59:59.

Use the same calendar during aggregation

This section keeps batch summaries aligned with agent queries. Resolving the agent correctly is not enough if the warehouse grouped events using a different timezone.

The warehouse transformation should derive its local date from the same authoritative timezone and calendar policy. The agent can use the UTC range for event-level queries and the corresponding local date for precomputed summaries. Both paths must describe the same calendar day.

This is a semantic consistency problem rather than a SQL problem. If a batch job groups by server date while the request path resolves the account calendar, both implementations can be individually correct and still disagree. Store the timezone and calendar version with materialized summaries so the mismatch is visible.

Test the boundaries that ordinary dates hide

This section verifies the resolver around midnight and daylight-saving changes. Tests should assert duration as well as date labels because a local day is not always 24 hours.

Use a fixed instant immediately before and after local midnight, then repeat the case for both directions of a daylight-saving transition. Add month-end, year-end, leap-day, and week-start cases. For each one, assert the local labels, UTC boundaries, and the fact that the end of one window equals the start of the next.

A 23-hour or 25-hour interval is correct when the calendar says it is one day. Hard-coding a 24-hour subtraction would produce the wrong boundary even though every stored timestamp remained valid UTC.

The model should interpret temporal language, not own temporal arithmetic. One authoritative timezone and one tested resolver keep event queries, summaries, dashboards, and agent answers on the same day.