PostgreSQL BETWEEN: Inclusive Range Queries, Indexes, and Date Traps

Q: Is BETWEEN inclusive in MySQL?

Yes. BETWEEN is inclusive in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. The SQL standard defines it as inclusive. The wrong answer is to assume one database is different.

Q: What is the difference between BETWEEN and >= AND <=?

There is no difference. `x BETWEEN a AND b` is exactly equivalent to `x >= a AND x <= b`. The right answer is to use `BETWEEN` for readability and the explicit comparison when you need to mix inclusive and exclusive bounds.

Q: Can BETWEEN use an index on text columns?

Yes. B-tree indexes work for text range queries, including `LIKE 'prefix%'` and `BETWEEN 'a' AND 'z'`. The right answer for a text column with a range query is to add a B-tree index. The wrong answer is to think that text indexes only work for equality.

Q: Why is my BETWEEN query slow?

The most common cause is a missing index. The right answer is to add an index on the BETWEEN column. The second most common cause is a function on the column: ```sql WHERE date_trunc('day', date_col) BETWEEN ... ``` This prevents the index from being used. The right answer is to rewrite the query so the function is on the bound, not the column: ```sql WHERE date_col BETWEEN date_trunc('day', '2026-01-01') AND ... ```

Q: Can I use BETWEEN with JSONB?

Not directly. JSONB values are not naturally ordered for range queries. The right answer is to extract a scalar value and use BETWEEN on that: ```sql WHERE (data->>'created_at')::timestamptz BETWEEN '2026-01-01' AND '2026-01-31' ``` The wrong answer is to use a GIN index with jsonb_path_ops, which works for containment but not for range queries.

Q: What is the performance of BETWEEN vs IN?

BETWEEN on an indexed column is faster than IN for ranges. IN on a small list (a few values) is similar to multiple equality lookups, which are also fast with an index. The right answer is to use BETWEEN for ranges and IN for a small list of specific values. The wrong answer is to use IN for a range, which forces a sequential scan.

Q: Is BETWEEN case-insensitive for text?

Yes, if the column uses a case-insensitive collation. The right answer for a case-insensitive text search is `COLLATE "C"` or `ILIKE` (PostgreSQL-specific). The right answer for an index-friendly case-insensitive range query is to create the index on `LOWER(column)` and query `LOWER(column) BETWEEN ...`.

Q: Why does BETWEEN with timestamps not match my data?

The most common cause is the time zone. `TIMESTAMP WITHOUT TIME ZONE` (the default) is interpreted in the server's time zone. `TIMESTAMP WITH TIME ZONE` (a.k.a. `timestamptz`) is stored in UTC. The right answer is to use `timestamptz` for any time-sensitive data and to make sure the query literals match the column's type. The wrong answer is to mix `timestamp` and `timestamptz` in the same query.

BETWEEN x AND y in PostgreSQL is inclusive — both x and y are included in the result. The right syntax for date ranges is WHERE date_col BETWEEN '2026-01-01' AND '2026-01-31', which includes both endpoints. The trap is with timestamps: a literal '2026-01-31' is midnight, so it excludes events on the 31st after midnight. The team that has the right pattern has the right answer.

The inclusive semantics of BETWEEN
Date ranges and the timestamp trap
Index usage with BETWEEN
The range types: int4range, int8range, daterange, tsrange
The BETWEEN SYMMETRIC variant
NULL handling in BETWEEN
FAQ

The inclusive semantics of BETWEEN

The SQL standard defines BETWEEN as inclusive. PostgreSQL implements this — x BETWEEN a AND b is equivalent to x >= a AND x <= b. The wrong answer is to assume BETWEEN is exclusive on the upper bound. The right answer is to test with a known value:

SELECT 5 BETWEEN 1 AND 5;  -- returns true
SELECT 5 BETWEEN 1 AND 4;  -- returns false

This behavior is consistent across all SQL databases. The wrong answer is to assume one database is different — the SQL standard is clear.

The right answer for a range query that should be exclusive on one end is to use explicit comparisons:

WHERE date_col >= '2026-01-01' AND date_col < '2026-02-01'

The half-open interval [start, end) is the right pattern for date ranges that should not double-count the boundary day. The right answer for a query that should be inclusive on both ends is BETWEEN.

Date ranges and the timestamp trap

The most common mistake with BETWEEN on dates is the timestamp interpretation. When you write '2026-01-31', PostgreSQL interprets it as '2026-01-31 00:00:00'. A row with date_col = '2026-01-31 14:23:00' will NOT be in the result of:

WHERE date_col BETWEEN '2026-01-01' AND '2026-01-31'

because 2026-01-31 14:23:00 is greater than 2026-01-31 00:00:00. The query only includes events at exactly midnight on the 31st (or before).

The right answer is to use the next-day half-open interval:

WHERE date_col >= '2026-01-01' AND date_col < '2026-02-01'

This includes all of January, including the 31st at any time of day. The wrong answer is to add '23:59:59' to the upper bound — this still misses microseconds past 23:59:59.000000, and the next-day pattern is more readable.

A more general pattern is to use a function:

WHERE date_col >= date_trunc('month', '2026-01-01'::date)
  AND date_col <  date_trunc('month', '2026-01-01'::date) + interval '1 month'

This always means “all of January 2026” and is robust to the time component.

Index usage with BETWEEN

The right answer for performance is to make sure the BETWEEN column is indexed. PostgreSQL can use a B-tree index for range queries, including BETWEEN. The right verification is EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM events WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

The plan should show Index Scan on the created_at index. If it shows Seq Scan, the index is missing, the index is not selective enough, or the table is small enough that the planner prefers a sequential scan.

The right answer for a large table that is scanned sequentially is to add the index:

CREATE INDEX idx_events_created_at ON events (created_at);

The wrong answer is to assume the planner is wrong — the planner is usually right. If it is doing a sequential scan, the table is small or the query is unselective, and a sequential scan is actually faster than the index scan.

The right answer for very large tables and time-range queries is to use a BRIN (Block Range Index) instead of a B-tree. BRIN is much smaller and works well for monotonically increasing values like timestamps.

The range types: int4range, int8range, daterange, tsrange

PostgreSQL has dedicated range types that are the right answer for any column that stores a range. The types are int4range, int8range, numrange, daterange, tsrange (timestamp without time zone), tstzrange (timestamp with time zone). The right syntax:

CREATE TABLE bookings (
  id serial PRIMARY KEY,
  room_id integer NOT NULL,
  during tsrange NOT NULL,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

The && operator on a range means “overlaps.” The EXCLUDE constraint ensures no two bookings for the same room overlap. The right answer for a booking system is to use a range type with an exclusion constraint, which is the database-level enforcement of no-double-booking.

The right answer for a query against a range column:

SELECT * FROM bookings
WHERE during && tsrange('2026-07-06 14:00', '2026-07-06 16:00', '[)');

The '[)' syntax means inclusive on the left, exclusive on the right. The right answer for booking systems is to use half-open intervals, which is the standard way to model a continuous reservation.

The BETWEEN SYMMETRIC variant

PostgreSQL has a BETWEEN SYMMETRIC variant that swaps the bounds if they are out of order. The right answer for queries where the user might enter the bounds in either order is BETWEEN SYMMETRIC:

SELECT * FROM events
WHERE event_date BETWEEN SYMMETRIC '2026-01-31' AND '2026-01-01';
-- Equivalent to: event_date BETWEEN '2026-01-01' AND '2026-01-31'

The wrong answer is to use BETWEEN SYMMETRIC when the bounds are always in order. The right answer is to use plain BETWEEN for the common case and BETWEEN SYMMETRIC for the rare case where the order is unknown.

NULL handling in BETWEEN

The wrong answer is to assume BETWEEN matches NULL. The right answer is that BETWEEN returns NULL for NULL inputs, which excludes the row from the result. A row with date_col = NULL is not in the result of:

WHERE date_col BETWEEN '2026-01-01' AND '2026-01-31'

because NULL BETWEEN anything AND anything is NULL, and WHERE NULL excludes the row. The right answer is to use IS NOT NULL if you want to include or exclude NULLs explicitly:

WHERE date_col BETWEEN '2026-01-01' AND '2026-01-31'
  AND date_col IS NOT NULL

Or to use COALESCE if you want to treat NULLs as a specific value:

WHERE COALESCE(date_col, '1900-01-01') BETWEEN '2026-01-01' AND '2026-01-31'

The right answer for most queries is to add the IS NOT NULL clause explicitly, because the NULL behavior is one of the most common causes of “the query returned fewer rows than expected.”

FAQ

Is BETWEEN inclusive in MySQL?

Yes. BETWEEN is inclusive in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. The SQL standard defines it as inclusive. The wrong answer is to assume one database is different.

What is the difference between BETWEEN and >= AND <=?

There is no difference. x BETWEEN a AND b is exactly equivalent to x >= a AND x <= b. The right answer is to use BETWEEN for readability and the explicit comparison when you need to mix inclusive and exclusive bounds.

Can BETWEEN use an index on text columns?

Yes. B-tree indexes work for text range queries, including LIKE 'prefix%' and BETWEEN 'a' AND 'z'. The right answer for a text column with a range query is to add a B-tree index. The wrong answer is to think that text indexes only work for equality.

Why is my BETWEEN query slow?

The most common cause is a missing index. The right answer is to add an index on the BETWEEN column. The second most common cause is a function on the column:

WHERE date_trunc('day', date_col) BETWEEN ...

This prevents the index from being used. The right answer is to rewrite the query so the function is on the bound, not the column:

WHERE date_col BETWEEN date_trunc('day', '2026-01-01') AND ...

Can I use BETWEEN with JSONB?

Not directly. JSONB values are not naturally ordered for range queries. The right answer is to extract a scalar value and use BETWEEN on that:

WHERE (data->>'created_at')::timestamptz BETWEEN '2026-01-01' AND '2026-01-31'

The wrong answer is to use a GIN index with jsonb_path_ops, which works for containment but not for range queries.

What is the performance of BETWEEN vs IN?

BETWEEN on an indexed column is faster than IN for ranges. IN on a small list (a few values) is similar to multiple equality lookups, which are also fast with an index. The right answer is to use BETWEEN for ranges and IN for a small list of specific values. The wrong answer is to use IN for a range, which forces a sequential scan.

Is BETWEEN case-insensitive for text?

Yes, if the column uses a case-insensitive collation. The right answer for a case-insensitive text search is COLLATE "C" or ILIKE (PostgreSQL-specific). The right answer for an index-friendly case-insensitive range query is to create the index on LOWER(column) and query LOWER(column) BETWEEN ....

Why does BETWEEN with timestamps not match my data?

The most common cause is the time zone. TIMESTAMP WITHOUT TIME ZONE (the default) is interpreted in the server’s time zone. TIMESTAMP WITH TIME ZONE (a.k.a. timestamptz) is stored in UTC. The right answer is to use timestamptz for any time-sensitive data and to make sure the query literals match the column’s type. The wrong answer is to mix timestamp and timestamptz in the same query.

If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.

Useful related references: