SQL dates: choose inclusive and exclusive boundaries correctly

For adjacent reporting periods, a dependable convention is to include the start and exclude the next period's start: timestamp >= start AND timestamp < next_start. This is called a half-open interval.

It avoids two common defects: guessing the final representable instant of a day, and counting a boundary event in both neighbouring periods. It does not solve timezone or business-calendar questions by itself; those still need explicit definitions.

The commerce SQL lab includes O1009 at 2026-01-31T23:59:59 and O1010 at 2026-02-01T00:00:00. They make the month boundary visible without needing a large dataset.

Compare the reporting questions

“Orders in January” can mean order creation, payment capture, shipment or settlement during January. Pick the timestamp that corresponds to the question. Filtering an order-created timestamp cannot establish when cash arrived.

Our example uses ordered_at in one fixed hypothetical business timezone. The fixture stores consistently formatted ISO text values. This makes the teaching comparison simple, but a production schema should use an appropriate timestamp type and retain its timezone semantics.

The correct January interval is:

SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= '2026-01-01T00:00:00'
  AND ordered_at <  '2026-02-01T00:00:00'
ORDER BY ordered_at, order_id;

It returns nine orders: O1001 through O1009. O1010 belongs to February. Status eligibility is separate; adding status = 'completed' reduces January to eight completed orders.

Why BETWEEN can surprise you

SQL BETWEEN includes both endpoints. A condition ending at the beginning of February therefore includes that instant:

SELECT order_id
FROM orders
WHERE ordered_at BETWEEN '2026-01-01T00:00:00'
                     AND '2026-02-01T00:00:00'
ORDER BY order_id;

This returns all ten fixture orders, including O1010. BETWEEN is not wrong; it expresses a closed interval. It is just not the interval intended for this monthly extract. SQLite BETWEEN semantics.

Ending at 2026-01-31T23:59:59 seems to repair this specific fixture, but a real timestamp may contain fractional seconds. An event at 23:59:59.500 would be missed. Ending at the next period's start and using < avoids that precision-dependent guess.

Adjacent periods should reconcile

If January is [Jan 1, Feb 1) and February is [Feb 1, Mar 1), an event at exactly February 1 belongs to February only. The periods meet without overlapping.

Test that property directly with boundary fixtures: one instant before the boundary, the boundary itself and one instant after it. Also test a missing timestamp. A NULL timestamp does not satisfy the comparison and should be counted in a separate quality exception if completeness matters.

For a complete source snapshot, the count across adjacent periods should equal the count over their combined interval. If it does not, check inconsistent filters, duplicated joins or timezone conversion before blaming the aggregation.

Timezone is part of the metric definition

A calendar day in a business timezone may begin on a different UTC date. Calculate its start and end as actual instants, then filter stored instants against those boundaries. Do not simply remove timezone information from a timestamp to make it look like a local date.

Daylight-saving transitions also mean that a local calendar day is not always 24 elapsed hours. Even if your current business timezone does not use daylight saving, imported data may come from regions that do. A global pipeline needs to state whether a report follows local business days or fixed UTC intervals.

The small fixture does not exercise those conversions. Treat it as proof of interval logic, not as a complete timezone implementation.

Reporting cutoff differs from event time

A late-arriving January order might be loaded in February. If you rerun January after the new record arrives, the total changes even though the date filter did not.

Decide whether the report is a restated view of current knowledge or a frozen view as known at an earlier cutoff. For the latter, you need ingestion/version information or a source snapshot, not only an event-date condition. Record both the business period and the extraction cutoff in the report manifest.

This distinction helps explain why two reports can legitimately disagree: they may use the same event period but different source completeness cutoffs.

Keep the filter easy to inspect

Prefer explicit start and end parameters over a collection of month/day string operations. Applying a function to every timestamp may also affect index use, depending on the engine and available indexes. Inspect the execution plan before making a performance claim.

Exercise: create January and February extracts from the fixture. Verify that their order IDs do not overlap and that their union contains all ten orders. Then add a second filter for completed status and explain why the total population becomes eight rather than ten.

For an end-to-end extract specification, read SELECT and WHERE. NeuraPath's Data Analytics with Generative AI course develops the SQL and pipeline skills needed to carry these time definitions consistently into dashboards and automated reporting.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in CASE WHEN: classify orders without overlapping buckets.
• Continue with Calculate weighted average order value in SQL.