A daily report needs a calendar date and a named time zone, not just a 24-hour duration. Compute midnight at the start of that local date and midnight at the start of the next date, convert each boundary to UTC, and select events with timestamp >= start and timestamp < end. Verify the selected event IDs as well as the total.

That last check matters. In our synthetic 2026 fixture, a UTC-midnight query selected the wrong events in 3,131 of 3,650 zone-days. In 3,119 of those failing cases, its row count still matched the correct report. A plausible total would have hidden the error.

If you are evaluating an agent that prepares daily operating metrics, open Code Interpreter Tools in Aident Loadout and run the acceptance fixture before granting it a report-sending step. The test uses synthetic timestamps, makes no network requests from its code, and produces a CSV you can inspect. It does not need customer data or access to your analytics account.

What this test establishes

The reader job is specific: decide whether a generated daily-report query selects the intended local day's events. This is separate from deciding when a scheduled task should run, whether an analytics source has finished loading, or whether a message was delivered.

On October 20, 2026, Aident ran three window recipes over every date in 2026 in ten named zones. The fixture was executed through Loadout's Code Interpreter Action using Python 3.13.16 and time-zone database 2026c, and independently on a local Python runtime. Both runs produced the same CSV SHA-256.

These are results for a deliberately selected synthetic fixture. They are not the percentage of real reports that are wrong, a benchmark of an AI model, or evidence of a production outage. The test compares three explicit recipes, not competing agents.

The ten zones are UTC, America/Los_Angeles, America/New_York, Europe/London, Europe/Berlin, Australia/Adelaide, Australia/Lord_Howe, Asia/Kolkata, Asia/Kathmandu, and Asia/Tokyo. They cover ordinary UTC days, seasonal offsets, half-hour and quarter-hour offsets, and a half-hour daylight-saving transition. They are not a representative sample of all IANA zones or historical rule changes.

Why the right count can belong to the wrong day

Consider a synthetic event every 15 minutes. On an ordinary Los Angeles day, both a UTC-midnight window and the correct local-day window contain 96 events. They contain different events: some late-local-day events are omitted and some from the preceding local date are included.

Checking only count(*) = 96 passes both queries. Comparing event IDs to the intended local date exposes the substitution.

This is a recurring operator problem rather than a new timezone discovery. A PostgreSQL community question about true calendar days describes a series drifting across daylight saving. A separate daily aggregation question describes UTC-stored events landing on the wrong displayed day. Our contribution is a bounded, reproducible acceptance matrix with a count-only false-pass check.

For a real report, write down the business rule first. A UTC billing day, a customer's local calendar day, and the last 24 elapsed hours are all valid reporting periods. They answer different questions. Do not replace an explicitly UTC-based contract with local dates merely because a dashboard displays local time.

Two daylight-saving cases that catch shortcuts

For Los Angeles on November 1, 2026, the fixture resolves the local day to:

start inclusive: 2026-11-01T07:00:00Z
end exclusive:   2026-11-02T08:00:00Z
elapsed:         25 hours
synthetic events: 100

Starting at the correct instant and adding 24 elapsed hours ends one hour early. The fixture returns 96 events instead of 100. On March 8, the correct local day lasts 23 hours; the same shortcut includes four synthetic events from the following day.

Do not repair this by hard-coding a one-hour adjustment. On Australia/Lord_Howe's October 4, 2026 transition, the fixture's local day lasts 23.5 hours and contains 94 quarter-hour events. Its April 5 day lasts 24.5 hours and contains 98. Named-zone rules, rather than a fixed offset or assumed transition size, define the boundaries.

Python's ZoneInfo documentation explains its IANA time-zone support and use of system data or the first-party tzdata package. Keep the time-zone database version with your test result: governments can change rules, and a future run should not silently inherit today's expectations.

Reproduce the full matrix

Use Python 3.9 or later with IANA zone data available. If ZoneInfo raises ZoneInfoNotFoundError, follow the Python documentation to supply system zone data or the tzdata package. Do not silently substitute a fixed UTC offset.

The fixture below creates 35,232 synthetic UTC event IDs at a 15-minute cadence, including a buffer around 2026. Each event's converted local date supplies the expected membership. The tested recipes then select IDs by UTC intervals. A further 14,600 assertions check the exact interval edges at microsecond precision.

import bisect, csv, hashlib, io, json, platform, tempfile
from collections import defaultdict
from datetime import date, datetime, time, timedelta, timezone
from pathlib import Path
from zoneinfo import ZoneInfo, TZPATH

UTC = timezone.utc
ZONES = ['UTC', 'America/Los_Angeles', 'America/New_York', 'Europe/London',
         'Europe/Berlin', 'Australia/Adelaide', 'Australia/Lord_Howe',
         'Asia/Kolkata', 'Asia/Kathmandu', 'Asia/Tokyo']
# Synthetic event IDs are distinct UTC quarter-hour instants, not customer data.
events = []
t = datetime(2025, 12, 31, tzinfo=UTC)
while t < datetime(2027, 1, 2, tzinfo=UTC):
    events.append(t)
    t += timedelta(minutes=15)

def selected(start, end):
    return set(range(bisect.bisect_left(events, start), bisect.bisect_left(events, end)))

rows, transitions = [], []
summary = {m: {'windows': 0, 'wrong_membership': 0, 'same_count_but_wrong_membership': 0}
           for m in ['utc_midnight', 'local_start_plus_24h', 'local_calendar_bounds']}
boundary_assertions = 0
for name in ZONES:
    zone = ZoneInfo(name)
    oracle = defaultdict(set)
    for event_id, instant in enumerate(events):
        oracle[instant.astimezone(zone).date()].add(event_id)
    day = date(2026, 1, 1)
    while day < date(2027, 1, 1):
        start = datetime.combine(day, time.min, tzinfo=zone).astimezone(UTC)
        end = datetime.combine(day + timedelta(days=1), time.min, tzinfo=zone).astimezone(UTC)
        expected = oracle[day]
        windows = {'utc_midnight': (datetime.combine(day, time.min, tzinfo=UTC),
                                     datetime.combine(day + timedelta(days=1), time.min, tzinfo=UTC)),
                   'local_start_plus_24h': (start, start + timedelta(hours=24)),
                   'local_calendar_bounds': (start, end)}
        hours = (end - start).total_seconds() / 3600
        if hours != 24:
            transitions.append({'zone': name, 'date': str(day), 'hours': hours,
                                'utc_start': start.isoformat(), 'utc_end': end.isoformat(),
                                'expected_events': len(expected)})
        row = {'zone': name, 'date': str(day), 'hours': hours, 'expected_events': len(expected),
               'utc_start': start.isoformat(), 'utc_end': end.isoformat()}
        for method, (a, b) in windows.items():
            actual = selected(a, b)
            bad = actual != expected
            s = summary[method]
            s['windows'] += 1
            s['wrong_membership'] += int(bad)
            s['same_count_but_wrong_membership'] += int(bad and len(actual) == len(expected))
            row[method + '_events'] = len(actual)
            row[method + '_wrong_ids'] = len(actual.symmetric_difference(expected))
            if method == 'local_calendar_bounds':
                assert not bad, (name, day)
        # Membership at the exact edges, independent of quarter-hour sampling.
        for instant in [start - timedelta(microseconds=1), start,
                        end - timedelta(microseconds=1), end]:
            assert (start <= instant < end) == (instant.astimezone(zone).date() == day)
            boundary_assertions += 1
        rows.append(row)
        day += timedelta(days=1)
buf = io.StringIO(newline='')
w = csv.DictWriter(buf, fieldnames=list(rows[0]), lineterminator='\n')
w.writeheader(); w.writerows(rows)
raw = buf.getvalue().encode()
output = Path('/home/user/report-window-matrix.csv') if Path('/home/user').is_dir() else Path(tempfile.gettempdir()) / 'report-window-matrix.csv'
output.write_bytes(raw)
tz_version = 'not identified'
for folder in TZPATH:
    candidate = Path(folder) / 'tzdata.zi'
    if candidate.is_file():
        tz_version = candidate.read_text().splitlines()[0]
        break
print(json.dumps({'python': platform.python_version(), 'tz_database': tz_version,
                  'year': 2026, 'zone_count': len(ZONES), 'zone_days': len(rows),
                  'synthetic_event_count': len(events), 'cadence_minutes': 15,
                  'boundary_assertions': boundary_assertions,
                  'summary': summary, 'transitions': transitions,
                  'csv_sha256': hashlib.sha256(raw).hexdigest(), 'csv_bytes': len(raw),
                  'csv_path': str(output)}, sort_keys=True))

Expect the three rows in the results table, zone_days: 3650, and boundary_assertions: 14600. With the tested rules, the CSV is 370,544 bytes and has SHA-256:

4358aa64a92f9d7b03f8dea3107a82341d5dbf8bb8ce9504969822b6973ac96e

The printed csv_path tells you where the file was written. Preserve it before a temporary execution environment expires. Different timezone rules, zone order, event cadence, or CSV formatting can change the hash; inspect those differences before concluding the algorithm failed. For a runtime decision beyond this bounded fixture, see code interpreter versus agent sandbox.

Turn the result into a report acceptance gate

Give your agent this bounded task alongside the fixture:

Run this daily-window acceptance fixture verbatim using Code Interpreter
Tools in Aident Loadout. Use synthetic data only. Do not query an analytics
account, install packages, call the network from the code, or send a report.
Return the runtime, timezone-data version, all three method summaries,
the 12 non-24-hour zone-days, the CSV path, and its SHA-256. If any expected
result differs, stop and explain the difference rather than changing the
expected values. Then propose, without executing, the UTC start-inclusive
and end-exclusive bounds for my requested local date and IANA timezone

If your agent does not yet have Loadout, use the exact setup instruction Follow https://aident.ai/SETUP.md, then return to the specific Code Interpreter task above. Setup is a prerequisite, not evidence that the fixture or a live report succeeded. The current integration page exposes the execution contract; inspect and preflight it before running code in your own account.

For your actual query, carry the two validated UTC instants as parameters. The selection contract is:

WHERE event_timestamp >= :start_utc
  AND event_timestamp <  :end_utc

This is parameterized SQL notation, not a tested query for every database or client. Confirm your column type and adapter bind the parameters as instants. Avoid an inclusive end that assigns the next midnight to both reports. PostgreSQL's date/time reference documents the distinction between calendar-day intervals and 24-hour intervals around daylight saving; other backends require their own type and timezone checks.

Before allowing delivery, require a receipt with the requested local date, IANA zone, UTC bounds, source data's completeness watermark, event-ID comparison result, and final output path. A scheduler firing at the right hour does not establish any of these. For that separate scheduler diagnosis, use the Codex scheduled-time guide.

What still needs a live-system test

A passing synthetic fixture proves only this window-selection behavior under the stated rules. It does not prove that your provider accepts those bounds, returns every page, has finished ingesting the day, deduplicates events correctly, or sends a report exactly once. Test those boundaries separately using an authorized non-sensitive sample before connecting a live reporting job.

Likewise, a correct daily count does not establish product attribution. To measure progression, preserve the exact event definition, source, identity coverage, and observation window described in the blog-to-product attribution guide. Keep missing attribution unknown rather than interpreting an unavailable join as zero conversions.

Refresh this fixture when your runtime or timezone data changes, before a reporting-region change, and after a query adapter starts interpreting date parameters differently. Re-run the edge assertions as well as the quarter-hour sample; the synthetic cadence does not exercise every possible timestamp or historical midnight transition.

Sources and method

• Python: ZoneInfo and aware datetime behavior. These define the runtime's zone and instant conversion behavior.

• PostgreSQL: date/time functions and operators. This supports the calendar-versus-elapsed-time distinction; the fixture itself is Python, not a PostgreSQL execution test.

• Community problem evidence: the two independently asked PostgreSQL questions linked above. They establish recurring wording and use cases, not measured search volume or the prevalence of reporting bugs.

• First-party execution, October 20, 2026: ten zones, all 365 dates in 2026, three recipes, synthetic quarter-hour events, and exact-boundary assertions. Aident Loadout Code Interpreter returned a clean execution with Python 3.13.16 and timezone database 2026c; the local rerun produced the same CSV. No customer records, live analytics queries, email, or message delivery were used.