Use half-open intervals for incremental extracts
Assign boundary events exactly once with SQLite predicates, compare 23- and 25-hour local days, and separate late-arrival completeness from interval correctness.

A midnight event appears in two daily exports. Each query looks reasonable: include everything between the day’s start and the next midnight. The problem is that both queries include their shared endpoint. A different attempt, stopping at 23:59:59, loses events during the final fractional second.
If you maintain scheduled analytical extracts, this lesson gives you one consistent boundary rule and a way to test it. You need basic SQL comparisons and enough Python to run a short SQLite fixture. The examples use invented events, integer microseconds and two real time-zone transitions. They do not run an orchestrator or measure a production export.
Let the next interval own the shared boundary
A half-open interval includes its lower bound and excludes its upper bound: lower <= event_time < upper. For adjacent windows [a, b) and [b, c), an event exactly at b fails the first window and satisfies the second. Any event inside [a, c) belongs to exactly one of them, provided the windows share the same boundary value and compare the same timestamp representation.
That gives two separate checks: the window results must not overlap, and their union must match the larger interval. Testing only a total count can miss a duplicated event balanced by a missing one. Keep event identities when checking the rule.
Airflow’s data-interval documentation distinguishes a run’s assigned interval from when the task actually executes. A delayed job should still use its assigned bounds. The page also warns that manually triggered intervals depend on the timetable and trigger path; deriving the range blindly from a logical date is not a universal substitute. This example passes bounds directly and does not implement Airflow scheduling.
Compare one exact representation
Run the four Python blocks in sequence in one session. This fixture uses Python 3.12.3, SQLite 3.45.1 and system IANA data 2026c. Each event has an identity and an aware instant. We convert the instant into integer microseconds since the UTC epoch using integer arithmetic, avoiding a floating-point timestamp multiplication.
The supplied strings have at most six fractional digits. This is a fixture converter, not a general timestamp ingestion validator: an importer must define how it handles finer precision, missing offsets and invalid source values. The table rejects non-integer timestamp storage, but it cannot establish that a producer used the promised unit. These ordinary Python timestamps also do not model leap seconds.
from datetime import date, datetime, time, timedelta, timezone
from zoneinfo import ZoneInfo
import sqlite3
UTC = timezone.utc
EPOCH = datetime(1970, 1, 1, tzinfo=UTC)
DAY_US = 86_400_000_000
def epoch_us(instant):
if instant.tzinfo is None or instant.utcoffset() is None:
raise ValueError('an aware instant is required')
delta = instant.astimezone(UTC) - EPOCH
return ((delta.days * 86400 + delta.seconds) * 1_000_000
+ delta.microseconds)
def utc_us(text):
return epoch_us(datetime.fromisoformat(text))
connection = sqlite3.connect(':memory:')
connection.execute('''CREATE TABLE events (
event_id TEXT PRIMARY KEY,
event_us INTEGER NOT NULL CHECK (typeof(event_us) = 'integer')
)''')
source = [
('a', '2026-06-30T23:59:59.999999+00:00'),
('b', '2026-07-01T00:00:00+00:00'),
('c', '2026-07-01T23:59:59.999999+00:00'),
('d', '2026-07-02T00:00:00+00:00'),
('e', '2026-07-02T12:00:00+00:00'),
('f', '2026-07-03T00:00:00+00:00'),
]
connection.executemany('INSERT INTO events VALUES (?, ?)',
[(key, utc_us(text)) for key, text in source])
connection.commit()
start = utc_us('2026-07-01T00:00:00+00:00')
Event a precedes the selected two-day range, and f begins the following day. Within the range, b starts July 1, c is one microsecond before July 2, d starts July 2 and e occurs at noon. The boundary example deliberately places records exactly where inclusive comparisons differ.
Sorting by time and identity makes output stable when several events share a timestamp. Such events can be distinct observations. The boundary rule assigns them together; it does not deduplicate them by timestamp.
Make both failure modes visible
The extraction helper binds bounds as SQL parameters. It accepts nonempty intervals within SQLite’s signed integer range. An empty mathematical interval is valid, but this small job interface treats an equal or reversed pair as a configuration error instead of returning an apparently successful empty export.
def extract(lower, upper):
if type(lower) is not int or type(upper) is not int:
raise ValueError('integer microsecond bounds required')
if not -(1 << 63) <= lower < upper <= (1 << 63) - 1:
raise ValueError('ordered, nonempty SQLite integer bounds required')
return [row[0] for row in connection.execute(
'SELECT event_id FROM events '
'WHERE event_us >= ? AND event_us < ? '
'ORDER BY event_us, event_id', (lower, upper))]
first = extract(start, start + DAY_US)
second = extract(start + DAY_US, start + 2 * DAY_US)
assert first == ['b', 'c']
assert second == ['d', 'e']
assert first + second == extract(start, start + 2 * DAY_US)
print(first, second)
inclusive = lambda lo, hi: [row[0] for row in connection.execute(
'SELECT event_id FROM events WHERE event_us BETWEEN ? AND ? '
'ORDER BY event_us, event_id', (lo, hi))]
assert inclusive(start, start + DAY_US) == ['b', 'c', 'd']
assert inclusive(start + DAY_US, start + 2 * DAY_US) == ['d', 'e', 'f']
# Ending at 23:59:59 discards c, which occurs during the final second.
assert inclusive(start, start + DAY_US - 1_000_000) == ['b']
The printed results are ['b', 'c'] and ['d', 'e']. Concatenating them matches the larger interval in this ordered fixture. The inclusive controls return ['b', 'c', 'd'] and ['d', 'e', 'f']: d is repeated, and f leaks beyond the desired two-day range.
This follows the actual SQLite definition of BETWEEN, which is equivalent to x >= y AND x <= z, apart from evaluating x once. The keyword does not imply a half-open date range.
The 23:59:59 control returns only b, missing c. Subtracting one microsecond from the next midnight could work under this exact discrete representation, but it ties the query to a particular precision. Comparing directly with the exclusive next boundary avoids maintaining a separate “last representable instant” convention when a storage format changes.
Keep the predicate on the original timestamp column and pass normalized bounds. Formatting every row into a calendar string introduces another interpretation step and may change the available access paths. This article establishes selected identities, not an index or performance claim; inspect your engine’s actual plan separately.
A local calendar day can last 23 or 25 hours
A UTC day in this fixture has 86,400 seconds. A business day defined by local midnights can have a different elapsed duration. For New York on March 8, 2026, midnight-to-midnight maps to 05:00 UTC through 04:00 UTC the next day: 23 hours. November 1 maps to 04:00 UTC through 05:00 UTC the next day: 25 hours.
Construct both local calendar boundaries, then convert each to the comparison representation. Python’s datetime documentation explains that subtraction with the same tzinfo ignores zone adjustments. The code therefore measures elapsed duration after UTC conversion, not by subtracting the two local wall-clock values.
def local_day_bounds(day, zone_name):
zone = ZoneInfo(zone_name)
begin = datetime.combine(day, time.min, tzinfo=zone)
end = datetime.combine(day + timedelta(days=1), time.min, tzinfo=zone)
return epoch_us(begin), epoch_us(end)
spring = local_day_bounds(date(2026, 3, 8), 'America/New_York')
autumn = local_day_bounds(date(2026, 11, 1), 'America/New_York')
assert (spring[1] - spring[0]) // 3_600_000_000 == 23
assert (autumn[1] - autumn[0]) // 3_600_000_000 == 25
fall_events = [
('n0', '2026-11-01T03:59:59.999999+00:00'),
('n1', '2026-11-01T04:00:00+00:00'),
('n2', '2026-11-01T05:30:00+00:00'),
('n3', '2026-11-01T06:30:00+00:00'),
('n4', '2026-11-02T04:59:59.999999+00:00'),
('n5', '2026-11-02T05:00:00+00:00'),
]
connection.executemany('INSERT INTO events VALUES (?, ?)',
[(key, utc_us(text)) for key, text in fall_events])
connection.commit()
assert extract(*autumn) == ['n1', 'n2', 'n3', 'n4']
assert extract(autumn[0], autumn[0] + DAY_US) == ['n1', 'n2', 'n3']
for _, text in fall_events[2:4]:
print(datetime.fromisoformat(text).astimezone(ZoneInfo('America/New_York')).isoformat())
The autumn query returns n1, n2, n3 and n4. Adding a fixed 24-hour duration to the lower bound misses n4, which occurs in the final hour of the local day. The two printed 01:30 values have offsets -04:00 and -05:00. They represent different instants, both inside November 1. ZoneInfo documents this repeated-hour behavior and its use of the system database or the tzdata package.
The helper is demonstrated for these New York dates, whose midnight boundaries are valid and unambiguous. It is not a universal policy for jurisdictions that move clocks at midnight or skip a calendar date. Validate the calendar boundaries for the zones you support. Here the event inputs already identify UTC instants; deciding which occurrence an ambiguous offset-free local timestamp meant is a separate ingestion problem.
Correct boundaries do not establish completeness
Suppose a row becomes visible only after the first July 1 extraction. Its event time still falls inside July 1. A correct interval predicate cannot select a row that was absent from the source view it read.
# A newly visible row belongs to the old event-time interval.
connection.execute('INSERT INTO events VALUES (?, ?)',
('late', utc_us('2026-07-01T12:00:00+00:00')))
connection.commit()
assert first == ['b', 'c']
assert extract(start, start + DAY_US) == ['b', 'late', 'c']
assert extract(start + DAY_US, start + 2 * DAY_US) == ['d', 'e']
print('saved:', first, 'repeated:', extract(start, start + DAY_US))
The saved result stays ['b', 'c'], while repeating the same bounds returns ['b', 'late', 'c']. July 2 still returns ['d', 'e']. No boundary overlap caused this difference; the source changed. The partition proof applies to one fixed population. Separate queries against changing data need an explicit snapshot or revision contract if their union is supposed to describe the same population.
A production pipeline must separately decide when a source interval is complete, how to discover late arrivals and how to replace or reconcile an earlier output. Delivery retries and concurrent publication also need their own rules. Half-open bounds solve ownership at the shared instant; they do not make a mutable source reproducible.
Test the boundary with identities
After all four blocks, insert another event g at exactly July 2 midnight. July 1 should still contain b, late and c; July 2 should now contain d, g and e in that order. Then insert h one microsecond before that midnight. It belongs only to July 1, alongside c at the same timestamp. Check identities, not just totals.
For an operational review, record the timestamp unit, time zone used to define each calendar boundary, lower and exclusive upper values, and source snapshot or revision. A new time-zone database or source precision can justify rerunning boundary cases. The useful invariant is concrete: for the same source population, adjacent exports cover the intended larger range exactly once.