Last Week Is Not the Last Seven Days: Defining Periodic Export Windows

For a weekly postmortem export, I needed the period from 00:00 on the previous Monday to 00:00 on the current Monday. “The last seven days” does not describe that period: it ties the exported records to the execution time.

Below is a standalone example of calculating and checking the period, rather than a description of the actual exporter’s implementation.

The schedule does not define the data period

Suppose a job needs to export records for the week from October 5 to October 12, 2026. The right boundary is midnight on October 12, which is excluded from the period.

If the job starts at 09:00 on Monday and calculates now - 7 days, it gets a different interval: October 5 at 09:00 to October 12 at 09:00. It misses the first nine hours of the intended week and adds nine hours of the next one.

A retry on Tuesday shifts both boundaries by another day. The job has technically repeated, but it now requests a different set of data.

The schedule therefore answers “when should execution begin?” while export parameters answer “which period should the records cover?” A retry needs the original parameters, rather than a new calculation relative to the current time.

First decide which week a record belongs to

A document can have several dates. The incident time answers “what happened during the week?” The first publication time answers “which analyses became available during the week?” The modification time answers “which documents were updated during the week?”

These are three different reports. Choosing a time zone cannot fix choosing the wrong field.

In this example, we export documents published for the first time. A hypothetical first_published_at field stores the immutable time of first publication. Its availability and semantics need to be checked in the specific source: the name published_at alone guarantees nothing.

The selection contract becomes:

field: first_published_at
calendar zone: Europe/Berlin
start: Monday 00:00, inclusive
end: next Monday 00:00, exclusive

A record missing the chosen date must not silently fall back to the incident or last modification date. That is a separate data error that needs to appear in the export result.

Use an inclusive start and an exclusive end

The selection condition is:

start <= first_published_at < end

This is the half-open interval [start, end). A record exactly at start is included. A record exactly at end belongs to the next week.

For adjacent periods, the right boundary of one matches the left boundary of the next. This avoids gaps and double inclusion at the shared boundary. It is a property of the intervals, rather than a guarantee of API response completeness.

For example, the condition for an illustrative PostgreSQL table with a timestamptz field looks like this. $1 and $2 are bound parameters, rather than string substitutions:

SELECT id, first_published_at
FROM documents
WHERE first_published_at >= $1
  AND first_published_at < $2
ORDER BY first_published_at, id;

BETWEEN is unsuitable here because PostgreSQL includes both boundaries.¹ Subtracting a second or microsecond from end only adds a dependency on timestamp storage precision.

If an API only supports an inclusive upper boundary, we can fetch a wider result and apply the strict < end filter locally. We still need to read every response page and verify which field the API actually filters.

Calculate calendar boundaries before converting to UTC

A week in the chosen zone is defined by two local midnights. It is not necessarily 168 hours in UTC. When clocks change, the offsets at the boundaries can differ. Python’s ZoneInfo uses IANA rules, including offset transitions.²

First determine the two calendar dates in the required zone. Then convert each midnight to UTC independently. Do not calculate the left UTC boundary by subtracting 168 hours from the right one.

The function below accepts the Monday date that closes the week being exported. It does not read the clock or use the system time zone. It requires Python 3.9+ and a time zone database, either provided by the system or by the tzdata package.²

from datetime import date, datetime, time, timedelta, timezone
from zoneinfo import ZoneInfo


def midnight_utc(day: date, zone: ZoneInfo) -> datetime:
    wall_time = datetime.combine(day, time.min)
    local = wall_time.replace(tzinfo=zone)
    utc = local.astimezone(timezone.utc)
    round_trip = utc.astimezone(zone).replace(tzinfo=None)

    # Do not automatically resolve a missing or ambiguous local midnight.
    if (round_trip != wall_time
            or local.utcoffset() != local.replace(fold=1).utcoffset()):
        raise ValueError(f"Midnight is missing or ambiguous: {day} {zone.key}")
    return utc


def previous_week(end_monday: date, zone_name: str) -> tuple[datetime, datetime]:
    if type(end_monday) is not date:
        raise TypeError("end_monday must be a date, not a datetime")
    if end_monday.weekday() != 0:
        raise ValueError("end_monday must be a Monday")

    zone = ZoneInfo(zone_name)
    start_monday = end_monday - timedelta(days=7)
    return midnight_utc(start_monday, zone), midnight_utc(end_monday, zone)


start, end = previous_week(date(2026, 10, 12), "Europe/Berlin")
print(start.isoformat(), end.isoformat(), sep="\n")

Output:

2026-10-04T22:00:00+00:00
2026-10-11T22:00:00+00:00

The date shift in UTC is expected: these are the same local midnights on October 5 and 12. astimezone() changes how an instant is represented while preserving the instant itself.³

The check in midnight_utc() handles zones and dates where local midnight is missing or occurs twice. Such a period needs an explicit decision rather than a silent choice of offset. An unknown zone also causes the calculation to fail.

Persist the period before the first request

The scheduler or operator chooses the closing Monday once. Even if execution is postponed until the following week, the original date stays the same. Missed weeks become separate periods.

Before making requests, persist the parameters alongside the result. For this example:

time_field: first_published_at
timezone: Europe/Berlin
end_monday: "2026-10-12"
start_utc: "2026-10-04T22:00:00+00:00"
end_utc: "2026-10-11T22:00:00+00:00"
interval: "[start, end)"

A retry reads the saved UTC boundaries. They do not need to be recalculated using the clock, the runner’s zone, or updated time zone rules. The actual execution time is recorded separately for diagnostics.

Identical boundaries make the request repeatable, but they do not make the data immutable. A document may have been corrected, deleted, or made visible later. Reproducing the exact previous result requires a saved snapshot of the source data or equivalent support from the source.

Late records need a separate policy

Consider an illustrative case: a document’s first publication time is on Sunday, but the API only starts returning it on Tuesday. The first export of the previous week will miss it. The next week’s export will not select it by first_published_at either.

For a small export, we can reconcile several recent periods again. The lookback depth should follow the source’s acceptable delay. If the maximum delay is unknown, revisiting a fixed number of recent weeks cannot guarantee completeness: older periods need reconciliation too, or the source needs to provide a change log.

A rediscovered document is matched to the saved record by a stable ID, rather than its title, filename, or execution number. A new version should update the existing record or produce an explicit diff instead of creating a second document. Manual edits at the destination must not be silently overwritten.

Selecting by updated_at can help detect changes to older documents if the source guarantees the required field semantics. But it does not replace the first publication date: detecting a change and assigning a document to a reporting week serve different purposes.

Check boundaries, not just an ordinary Monday

The following block can be appended to the code above and run with plain python3, without -O. It checks specific expected instants rather than repeating the calculation algorithm:

cases = [
    ("2026-10-12", "2026-10-04T22:00:00+00:00", "2026-10-11T22:00:00+00:00", 168),
    ("2026-03-02", "2026-02-22T23:00:00+00:00", "2026-03-01T23:00:00+00:00", 168),
    ("2026-01-05", "2025-12-28T23:00:00+00:00", "2026-01-04T23:00:00+00:00", 168),
    ("2025-03-31", "2025-03-23T23:00:00+00:00", "2025-03-30T22:00:00+00:00", 167),
    ("2025-10-27", "2025-10-19T22:00:00+00:00", "2025-10-26T23:00:00+00:00", 169),
]

for closing_day, expected_start, expected_end, hours in cases:
    window = previous_week(date.fromisoformat(closing_day), "Europe/Berlin")
    assert tuple(value.isoformat() for value in window) == (expected_start, expected_end)
    assert (window[1] - window[0]).total_seconds() == hours * 3600

start, end = previous_week(date(2026, 10, 12), "Europe/Berlin")
next_start, next_end = previous_week(date(2026, 10, 19), "Europe/Berlin")
assert end == next_start

for timestamp, belongs in [
    (start - timedelta(microseconds=1), False),
    (start, True),
    (end - timedelta(microseconds=1), True),
    (end, False),
]:
    assert (start <= timestamp < end) == belongs

assert next_start <= end < next_end
print("Window checks passed")

Duration is calculated after converting to UTC. Subtracting two Python datetime objects with the same tzinfo does not account for offset changes in the way needed to measure elapsed hours.³

The exporter itself also needs behavioral checks:

Half-open intervals remove overlaps and gaps at the boundaries of adjacent weeks. They do not remove pagination errors, visibility delays, or changes to source data.

Conclusion

For a weekly export, fix the timestamp field, calendar zone, and both period boundaries. The execution time should not silently change these parameters.

A retry then means “process the same week again” rather than “select a different seven days.” Data completeness and updates can be checked separately, without mixing them with calendar arithmetic.