A Query Window Is Not a Calendar Day

Software often uses broad time windows for good reasons. A dashboard may need recent history, work that is actionable now, and the next scheduled item. Fetching all of that in one bounded query can be efficient and easy to reason about.

The trouble begins when the retrieval window quietly becomes the business definition of “today.”

This is a small distinction with a very visible failure mode: shortly after midnight, yesterday’s missed item appears under today’s heading. The query is returning exactly what it was asked to return. The selection rule is wrong.

Retrieval and meaning are different jobs

Imagine a “Today” screen that loads items from several hours in the past through the next day. The look-back allows the screen to include work that recently closed or may still be open. The look-ahead allows it to show what comes next.

That range is a retrieval concern. It answers:

Which records could be relevant enough to inspect?

It does not answer:

Which record belongs to the user’s current calendar day?

If selection simply chooses the first item whose state is due or missed, a previous day’s missed item can win after midnight. If display text assumes every future item is later today, tomorrow morning can be described as though it were later tonight.

Both errors come from using temporal proximity as a substitute for calendar meaning.

Define “today” in the relevant time zone

Server time is rarely the correct authority for a human-facing day. UTC is excellent for storing instants and comparing elapsed time. It is not a user’s calendar.

The safer sequence is:

1. Read the current instant from an injected clock.
2. Convert it once into the time zone that owns the workflow.
3. Derive a local date from that value.
4. Use that date when classifying calendar-bound states.

Computing the value once matters. It keeps every decision in one request anchored to the same instant, including the unlikely case where execution crosses midnight.

The relevant time zone also needs to be explicit. It may belong to a person, site, account, or workflow. The correct choice is a domain decision, not an infrastructure default.

Preserve the exception that actually matters

A tempting fix is to discard every record whose local date is not today. That is neat and wrong when an actionable window crosses midnight.

Suppose a task opened before midnight and remains valid until shortly afterwards. A strict date filter would hide it at the exact moment it still needs attention.

This is where explicit domain rules beat blanket filtering:

• missed and completed items count as current only when their local date is today;
• a still-open item remains current while it is actionable, regardless of its start date;
• a scheduled item on another day can still appear as next, but its wording names that day.

The rule is slightly longer because the product meaning is slightly richer. That extra branch is not accidental complexity. It records why two superficially similar records are treated differently.

Make wording part of correctness

Time bugs are not confined to sorting and filtering. Labels can encode false claims too.

“Later today” is only correct if the next item is on the same local day. “Between 08:00 and 09:00” may be ambiguous when viewed the night before. Adding “tomorrow” or a concrete day makes the model visible to the user.

That wording also gives tests a useful public outcome. A test can verify that the screen says tomorrow without reaching into a private helper or asserting a particular LINQ expression.

Test the boundary as a sequence

A single test at noon will not find this class of defect. The useful test moves a controllable clock through the state transition.

One focused integration scenario can prove three behaviours:

1. On the item’s own evening, the missed item is still classified as belonging to today.
2. The next day’s scheduled item is labelled explicitly as tomorrow.
3. After midnight, the old missed item no longer becomes the current card.

Using the real persistence provider is valuable when stored dates, generated occurrences, or query translation participate in the behaviour. The clock is the external boundary worth controlling; internal services do not need to be mocked if the public result proves the rule.

Also test a window that genuinely spans midnight. Without that case, a cleanup that filters everything by date can look correct while introducing a different regression.

The engineering trade-off

Explicit local-date classification costs a few things:

• more branches than a simple state check;
• a time-zone conversion at the application boundary;
• boundary-focused tests and more deliberate copy.

In return, the system becomes predictable at the moment when implicit assumptions fail most often. Retrieval can remain broad for performance and completeness. Domain selection becomes precise. User-facing language reflects the same model.

The broader leadership lesson is to ask two questions whenever a time range appears in code:

1. Is this range finding candidates, or defining business membership?
2. Whose calendar gives words such as today, tomorrow, and missed their meaning?

Keeping those answers separate prevents an implementation convenience from becoming an accidental policy.

Practical checklist

When building a day-oriented view:

• store and compare instants consistently;
• identify the domain-owned time zone;
• derive the local date once per operation;
• keep retrieval windows separate from classification rules;
• preserve intentionally actionable cross-midnight windows;
• make off-day wording explicit;
• test before midnight, after midnight, and a spanning window.

The code may still query eighteen or thirty hours. The user should experience a calendar day.