SQL BETWEEN: Inclusive on Both Ends, and the Timestamp Trap That Costs You a Day

BETWEEN is a closed-range test: both endpoints belong to the interval. That is convenient for scores and other discrete values, but the same rule can silently exclude most of an ending date when the column stores timestamps.

Treat every range as a small data contract. Name the column type, decide whether each boundary belongs, state how NULL should behave, and use a half-open interval when adjacent time windows must fit together without gaps or overlap.

BETWEEN includes both endpoints

For numeric values, amount BETWEEN 30.00 AND 55.75 is equivalent to amount >= 30.00 AND amount <= 55.75. Orders exactly at 30.00 and 55.75 therefore remain.

Input: orders

SELECT
  order_id,
  amount
FROM orders
WHERE amount BETWEEN 30.00 AND 55.75
ORDER BY amount, order_id;

Output

BETWEEN does not reorder its arguments. The first expression is the lower bound and the second is the upper bound. A reversed range is tested exactly as written.

Input: orders

SELECT
  COUNT(*) AS row_count
FROM orders
WHERE amount BETWEEN 55.75 AND 30.00;

Output

If user-supplied bounds may arrive in either order, validate them or normalize them explicitly with LEAST and GREATEST. Do not hide a reversed input unless that matches the product's contract.

A date literal can truncate a timestamp range

PostgreSQL interprets TIMESTAMP '2024-12-31' as midnight at the start of December 31. A closed range ending there includes the row exactly at midnight but excludes every later time that day. That is why this query returns only two of the four orders created across December 30 and 31.

Input: orders

SELECT
  order_id,
  created_at
FROM orders
WHERE created_at BETWEEN TIMESTAMP '2024-12-30'
                     AND TIMESTAMP '2024-12-31'
ORDER BY created_at, order_id;

Output

Avoid replacing the upper bound with 23:59:59. Timestamp precision can include fractions of a second, and a hand-written last moment is easy to get wrong.

Half-open intervals cover complete time windows

For adjacent timestamp windows, use an inclusive start and an exclusive next boundary. The interval below covers all of December 30 and 31, including the last displayed second, while excluding every instant on January 1.

Input: orders

SELECT
  order_id,
  created_at
FROM orders
WHERE created_at >= TIMESTAMP '2024-12-30'
  AND created_at <  TIMESTAMP '2025-01-01'
ORDER BY created_at, order_id;

Output

The same shape clarifies a rolling lifecycle label. Define day zero as the signup date, then treat the first 30 calendar dates as [signup_date, signup_date + 30 days). Activity before signup is not labeled new by this query.

Input: users

Input: activity

SELECT
  a.user_id,
  a.activity_date,
  CASE
    WHEN a.activity_date >= u.signup_date
     AND a.activity_date <  u.signup_date + 30
    THEN 'new'
    ELSE 'old'
  END AS lifecycle
FROM activity AS a
JOIN users AS u
  ON u.user_id = a.user_id
ORDER BY a.user_id, a.activity_date;

Output

Use typed boundaries in the same time zone as the business rule. If created_at is timestamptz, define the reporting zone before converting calendar dates into instants. SQL order of operations helps locate range filters within a larger query.

NOT BETWEEN still excludes NULL

NOT BETWEEN negates the closed-range comparison. It does not turn NULL into an outside value: a comparison with NULL is unknown, and WHERE keeps only true rows. When missing timestamps need their own category, test IS NULL first.

Input: orders

SELECT
  order_id,
  delivered_at,
  CASE
    WHEN delivered_at IS NULL THEN 'missing'
    WHEN delivered_at BETWEEN TIMESTAMP '2024-12-30'
                              AND TIMESTAMP '2025-01-01'
    THEN 'inside'
    ELSE 'outside'
  END AS window_status
FROM orders
ORDER BY order_id;

Output

If the requirement is specifically “outside or missing,” write it as value NOT BETWEEN low AND high OR value IS NULL. The same three-valued logic matters for other negative predicates; see SQL NOT EQUAL.

Text ranges depend on collation

Text can be compared with range operators, but the result is lexical rather than a prefix rule. Under PostgreSQL's C collation, 'Cable' and 'Camera' are greater than the one-character value 'C', so a closed 'A' to 'C' range excludes both. A half-open 'A' to 'D' range includes them.

Input: products

SELECT
  product_id,
  name,
  (name COLLATE "C") BETWEEN 'A' AND 'C' AS closed_a_to_c,
  (name COLLATE "C") >= 'A'
    AND (name COLLATE "C") < 'D' AS half_open_a_to_d
FROM products
ORDER BY product_id;

Output

For a literal prefix search, encode a prefix rule instead of assuming a range has one. Review the intended collation, case handling, and character set with sample boundary values.

FAQ

Is SQL BETWEEN inclusive?

Yes. x BETWEEN low AND high includes values equal to low or high. It is equivalent to x >= low AND x <= high when the same comparison rules apply.

Should I use BETWEEN for timestamps?

Use it only when a closed timestamp interval is truly the requirement. Calendar reporting windows are often easier to reason about as timestamp >= start AND timestamp < next_boundary.

Does NOT BETWEEN include NULL?

No. If the tested value or a boundary is NULL, the comparison is unknown. A WHERE clause removes that row unless another condition, such as value IS NULL, makes the full predicate true.

Why does an end date miss rows later that day?

A date converted to a timestamp normally identifies midnight at the start of that date. As an inclusive upper bound, it does not represent the following hours. Use the next date as an exclusive bound for a complete calendar-day window.

Can BETWEEN use expressions instead of constants?

Yes. A boundary can come from a column, parameter, or expression. Compute it in the correct type and time zone, then test boundary and NULL cases explicitly. The SQL CASE guide shows how to turn those tests into labels, and the SQL practice questions provide larger exercises.