Data Modeling

Half-open time intervals solve a common classification bug: when two adjacent periods both include their boundary, an event exactly on that boundary can be counted twice. The practical rule is simple: include the start, exclude the end. An event belongs to an interval when its timestamp is greater than or equal to the interval start and strictly less than the interval end.

Why inclusive boundaries overlap

Inclusive time ranges feel natural in conversation: “from 10:00 through 10:15” or “January 1 through January 31.” They become dangerous in data models when adjacent intervals share the same endpoint.

Suppose one interval is 10:00 to 10:15 and the next is 10:15 to 10:30. If both intervals use inclusive start and inclusive end logic, an event at exactly 10:15:00 matches both. That may double-count revenue, duplicate sessions, assign an order to two campaigns, or put a status transition in two lifecycle buckets.

The bug often hides because most events do not land exactly on boundaries. It appears later when batches, APIs, scheduled jobs, or manually entered records produce clean boundary timestamps such as midnight, the top of the hour, or the first day of a month.

The half-open interval rule

A half-open time interval includes its start and excludes its end. It is commonly written as [start, end). In plain English: the interval begins at start and continues until, but not including, end.

The classification predicate is: event_time >= interval_start and event_time < interval_end.

This creates a clean handoff between adjacent intervals. The end of one interval can equal the start of the next interval without overlap. An event exactly at the boundary belongs to the later interval, not both.

For example, if the intervals are [10:00, 10:15) and [10:15, 10:30), an event at 10:14:59.999 belongs to the first interval, while an event at 10:15:00 belongs to the second.

Operator rule

Use [start, end) for adjacent time periods. The start belongs to the interval; the end belongs to the next interval.

Model intervals explicitly instead of hiding boundaries in labels

Do not store only a label such as “morning,” “week 42,” or “pricing period A” and rely on downstream tools to infer the time range. Store the boundary columns that define membership.

A practical interval table should usually include:

  • interval_id, a stable identifier for the period or classification bucket.
  • interval_start, the inclusive timestamp where the interval begins.
  • interval_end, the exclusive timestamp where the interval stops.
  • classification attributes, such as plan name, campaign id, tariff version, lifecycle stage, or reporting period.
  • timezone or timestamp convention, if your organization uses both local wall-clock time and UTC-based event time.

The event table should also be clear about which timestamp is being classified. For most analytical classification, this should be event time: when the business event happened. Processing time, meaning when the pipeline observed or loaded the event, is a different concept and should not be substituted silently.

A timestamped fixture that catches boundary bugs

The fastest way to expose interval mistakes is to create a small fixture with events immediately before, exactly at, and immediately after important boundaries. The fixture should be boring, named, and reused in model tests or review examples.

Here is a hypothetical fixture for fifteen-minute buckets. It intentionally includes troublesome points: the start of the first interval, the shared boundary at 10:15, the boundary at 10:30, and a midnight-style day boundary in the same pattern.

The important part is not the exact timestamps. The important part is that every shared boundary has at least one event exactly on it.

Fixture event Event timestamp Why it is included
E1 2026-01-01 10:00:00.000 Exactly at the first interval start
E2 2026-01-01 10:14:59.999 Immediately before the 10:15 boundary
E3 2026-01-01 10:15:00.000 Exactly at a shared boundary
E4 2026-01-01 10:29:59.999 Immediately before the 10:30 boundary
E5 2026-01-01 10:30:00.000 Exactly at the next shared boundary
E6 2026-01-02 00:00:00.000 Exactly at a day boundary

Expected classification with half-open intervals

When the fixture is classified using half-open intervals, every event maps to one interval or none. Boundary events move forward into the interval that starts at that timestamp.

This behavior is predictable:

  • An event at the interval start is included.
  • An event just before the interval end is included.
  • An event exactly at the interval end is excluded from that interval.
  • If another interval starts at that same timestamp, the event belongs there.

This is why half-open intervals are useful for event classification. They do not require tiny artificial adjustments such as subtracting one millisecond, one microsecond, or one second from the end timestamp. Those adjustments are fragile because different systems store timestamp precision differently.

Interval Half-open definition Matching fixture events
Bucket A [2026-01-01 10:00:00.000, 2026-01-01 10:15:00.000) E1, E2
Bucket B [2026-01-01 10:15:00.000, 2026-01-01 10:30:00.000) E3, E4
Bucket C [2026-01-01 10:30:00.000, 2026-01-01 10:45:00.000) E5
Next day bucket [2026-01-02 00:00:00.000, 2026-01-02 00:15:00.000) E6

SQL patterns to prefer and avoid

The durable pattern is to join or filter with an inclusive lower bound and an exclusive upper bound: event_time >= interval_start and event_time < interval_end.

Avoid inclusive upper bounds for adjacent intervals. In many SQL dialects, a range expression such as “between start and end” is inclusive at both ends. That makes it a poor default for adjacent time buckets unless you have deliberately adjusted the end values and documented why.

Also avoid turning timestamps into dates too early. Casting an event timestamp to a date can hide the exact boundary behavior. It may be acceptable for a final daily report label, but the classification logic should operate on the timestamp semantics you actually intend.

Window functions can help derive interval_end from the next interval_start when intervals are naturally ordered. The important rule remains the same after derivation: current start is inclusive, next start is exclusive.

Warning

Be careful with inclusive range syntax such as BETWEEN. For adjacent time intervals, it commonly creates boundary double-counting unless the end values have been deliberately modeled to avoid it.

Pattern Boundary behavior Use when
event_time >= start and event_time < end Includes the start and excludes the end Default for adjacent intervals
event_time between start and end Usually includes both start and end Avoid for adjacent time buckets unless deliberately adjusted
event_date = reporting_date Depends on prior timestamp-to-date conversion Use only after timestamp semantics are already correct
event_time >= start and interval_end is null Handles open-ended current rows when paired with explicit null logic Use for current intervals if null means still active

How to handle the current open-ended interval

Many classification tables have a current row with no known end yet: the active subscription plan, the current account owner, the current price list, or the current status. You still need a consistent interval rule.

There are two common approaches:

  • Use a null end with explicit logic: event_time >= interval_start and either interval_end is null or event_time < interval_end.
  • Use a documented sentinel end: choose a far-future timestamp as interval_end and treat it like any other exclusive end.

Neither approach is universally better. Nulls make the open-ended meaning explicit but require careful predicate logic. Sentinels simplify some joins but can leak into reports if not documented. The modeling decision matters less than using one convention consistently.

Quality checks for interval tables

Half-open intervals work only if the interval table itself is clean. Before trusting event classification, check the interval definitions.

Useful checks include:

  • No negative or zero-length intervals, unless zero-length intervals are deliberately allowed for a special reason.
  • No overlaps within the same classification key, such as the same user, account, device, campaign, or product.
  • Expected adjacency where continuous coverage is required, meaning one interval_end equals the next interval_start.
  • Expected gaps where continuous coverage is not required, so missing coverage is visible rather than accidental.
  • One match per event for domains where every event should classify into at most one interval.
  • Boundary fixture passes, especially events exactly at starts and ends.

Do not rely only on aggregate totals. Boundary bugs can cancel out or appear small in summary counts while still corrupting individual event assignment.

Common failure modes

Most interval bugs come from a small set of repeatable mistakes.

  • Using inclusive end predicates on adjacent intervals, causing duplicate matches at shared boundaries.
  • Mixing date and timestamp types, which can accidentally treat a day as midnight at the start of the day instead of the full day.
  • Rounding event timestamps before classification, which can move events across boundaries.
  • Using processing time instead of event time, which classifies late-arriving events into the wrong business period.
  • Leaving precision unspecified, then assuming that milliseconds, microseconds, and seconds behave the same across systems.
  • Changing time zone interpretation downstream, which can shift apparent boundaries even when the stored timestamp value did not change.

Daylight-saving gaps, repeated local hours, and business holiday calendars require their own modeling choices. The half-open interval rule still helps, but it does not by itself define what local time means or which days count as working days.

Practical checkpoint

A model that passes normal events but has no event exactly on a boundary has not really tested interval correctness.

Operator checklist for event classification

Use this checklist when reviewing a model, migration, or metric definition that classifies events into time periods.

  1. Identify the exact event timestamp being classified.
  2. Confirm whether the timestamp is event time or processing time.
  3. Define every interval with an inclusive start and exclusive end.
  4. Use event_time >= interval_start and event_time < interval_end.
  5. Avoid inclusive upper-bound range predicates for adjacent periods.
  6. Add fixture events exactly on shared boundaries.
  7. Assert that each event has the expected number of matches: zero, one, or many, depending on the business rule.
  8. Document how open-ended current intervals are represented.
  9. Check interval tables for overlaps, gaps, and invalid durations.
  10. Keep timestamp precision and time zone interpretation explicit.

If a reviewer cannot tell what happens to an event exactly at midnight, exactly at the campaign cutover, or exactly at the plan-change timestamp, the model is not yet precise enough.

Key takeaways

  • Half-open time intervals use inclusive starts and exclusive ends: [start, end).
  • The safest event classification predicate is event_time >= interval_start and event_time < interval_end.
  • Inclusive end boundaries can duplicate events that occur exactly at shared boundaries.
  • A small timestamped fixture with events before, at, and after boundaries catches bugs that aggregate checks may miss.
  • Document event time, timestamp precision, time zone interpretation, and open-ended interval conventions before relying on the classification.

Next step

Create a boundary fixture for one real classification model: include an event exactly at each shared boundary, classify it with your current logic, and verify that every event maps to the expected interval count.

Recommended next reads