Practical technology learning
SQL Time Windows: Include the Start and Exclude the Next Boundary
A reporting period is easier to review when its boundaries are written as data. This SQL exercise uses fictional timestamps on one declared time basis to compare inclusive endpoints with a half-open window. It does not assume that a displayed calendar date defines a storage or time-zone policy.
Declare the teaching fixture
For a timestamp-without-time-zone practice column, use four records: just before 12 October 2026 begins, exactly at its midnight, at 23:59:59.999999 that day, and at 13 October midnight. These are invented events. State that the exercise uses one consistent clock basis before predicting membership.
Write two separate comparisons
For the 12 October window, use event_time >= TIMESTAMP '2026-10-12 00:00:00' AND event_time < TIMESTAMP '2026-10-13 00:00:00'. The two middle records belong; the record before the start and the next midnight do not. Save the expected record IDs beside the predicate.
Compare the inclusive alternative
BETWEEN includes both endpoints. If the upper endpoint is the next midnight, that boundary record is included too. A following inclusive window starting at the same value could count it again. The half-open definition assigns the boundary to the following window while retaining all earlier instants within the first.
Avoid a fabricated last instant
A hard-coded end value ending in 23:59:59 does not cover fractional timestamps later in that second. Precision depends on the actual type and system. Using the next agreed boundary avoids having to guess the last representable instant of a period.
Review the time contract
- Column type and whether it represents a date or timestamp.
- Reporting time basis and how real local boundaries are obtained.
- Expected inclusion at the start and exclusion at the next boundary.
- Behavior of null timestamps, if they are permitted in the real dataset.
Preserve the question with the query
The same boundary method can support other adjacent periods once their meaning is defined. It is not a performance guarantee or a substitute for correct zone handling. Keep the expected rows, storage type and boundary-generation rule in the report notes before applying the filter to real data.