Half-Open Intervals vs BETWEEN

Concepts covered: Range Filtering, Filtering, Timestamp Types, Date vs Timestamp, Conditional Logic

x BETWEEN a AND b means x >= a AND x <= b, both ends included. On a DATE column that matches the request: txn_date BETWEEN '2024-01-01' AND '2024-01-31' holds every January day. On a TIMESTAMP column the same literal '2024-01-31' becomes the instant 2024-01-31 00:00:00, so the range ends at the first moment of January 31 and everything ordered later that day falls outside. Nothing errors and the total looks reasonable, which is why this bug survives code review.

• 23h 59m 59.999999s

The animation tests 6 orders against both filters at once, a CASE for each form, so every row shows its 2 verdicts side by side. The orders sit on the edges of January, where the forms disagree.

SELECT
  order_id,
  ordered_at,
  CASE WHEN ordered_at BETWEEN '2024-01-01' AND '2024-01-31'
       THEN 'in' ELSE 'out' END AS between_jan,
  CASE WHEN ordered_at >= '2024-01-01' AND ordered_at < '2024-02-01'
       THEN 'in' ELSE 'out' END AS half_open_jan
FROM orders

Order 101 at 2023-12-31 23:59:00 is out under both forms, and order 102 at 2024-01-01 00:00:00 is in under both, since each includes its start. Order 103 in mid-January agrees too. The split is on January 31: order 104 at 09:15 and order 105 at 18:45 are in under the half-open range and out under BETWEEN. Order 106 at 2024-02-01 00:00:00 is out under both, because the half-open range excludes exactly the instant February begins. Summed, the BETWEEN version reports January revenue of 165 and the half-open version 425. The gap is the 260 ordered on the last day of the month, the kind of last-day spike a month-end promotion produces.

Why the patches fall short

-- Patch 1: end at the last second of the month
WHERE ordered_at BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'
-- misses an order at 2024-01-31 23:59:59.400

-- Patch 2: cast the column to a date first
WHERE CAST(ordered_at AS DATE) BETWEEN '2024-01-01' AND '2024-01-31'
-- correct rows, but the column is wrapped in a cast

-- The form that needs no patch
WHERE ordered_at >= '2024-01-01'
  AND ordered_at <  '2024-02-01'

The first patch fails as soon as the column stores fractions of a second. Timestamp columns in Postgres do, to the microsecond or finer, and so do the ones in Snowflake and BigQuery. An order at 23:59:59.4 is later than 23:59:59 and outside the range. The second patch returns the right rows, but the filter now compares a computed value instead of the stored one, and it only works for whole days. The half-open form needs neither fix: it names the start of the period and the start of the next, and every instant of January, at any precision, sits between them.

Half-open ranges also tile. [2024-01-01, 2024-02-01) and [2024-02-01, 2024-03-01) share a boundary, and every instant falls in exactly 1 of them. Closed ranges cannot do that on timestamps: BETWEEN '2024-01-01' AND '2024-02-01' counts the midnight that opens February in both January and February, and stopping at 23:59:59 leaves a sliver unclaimed. The tiling is the same rule DATE_TRUNC follows, which is why the 2 tools fit together: a row belongs to month bucket M exactly when M is at or before the row's time and M plus 1 month is after it.

So the upper bound of a calendar range is always the start of the next period, and it comes from the same truncate-then-shift move as the rollup's anchor. The last-full-month filter from the list of windows already has this shape: it opens at DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' and closes, exclusive, at the truncated start of this month. February 2024 has 29 days, and that number never appears in the query.

Saying it while you write it

Write the filter and narrate it in the same sentence: "Greater than or equal to January 1, strictly less than February 1, so it holds whether ordered_at is a date or a timestamp at any precision." The common follow-up is "isn't BETWEEN fine here?" The answer that scores is specific: on a DATE column, yes, a BETWEEN that runs to January 31 covers January; on a timestamp it ends at midnight on the 31st; the half-open form is right on both, so it stays right if the column type changes later. Then offer the check that proves it: count the rows on January 31 after 00:00:00, and that count is exactly what BETWEEN would have lost.

Do

• Restate each time phrase as one of the 5 operations before touching SQL
• Group trends on DATE_TRUNC and keep the bucket a date until the final display
• Anchor relative windows on CURRENT_DATE, truncated first for calendar-month windows
• Close every range with a strict less-than on the start of the next period
• Say out loud which day is day 0, and whether today and the last day of the range are included

Avoid

• Ending a BETWEEN on a timestamp column at a bare date like '2024-01-31'
• Grouping a multi-year trend by EXTRACT(MONTH ...) alone
• Hardcoding dates into a query that runs on a schedule
• Testing a 30-day threshold with DATE_DIFF in month units
• Using an inner join when customers with no orders belong in the rate

About this lesson

This section is part of the Date Arithmetic: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.