Does a range query require two bounds?

No. Conditions such as a timestamp after a specified instant are one-sided range conditions.

Will the results arrive sorted?

Do not assume so. Request the required ordering explicitly.

Is a range data type necessary?

No. Ordinary ordered columns can be filtered with comparisons; interval-valued data types serve additional purposes.

AI Glossary / Data engineering and architecture / Intermediate

Range query (database)

Definition

A range query retrieves database records whose values fall within specified boundaries. Examples include orders placed during a month, products within a price band and measurements above a threshold. The boundaries may include or exclude their endpoint values, and that choice changes the result.

What it means

An equality query asks whether a value matches a particular target. A range query asks where a value lies in an ordering. Prices can be ordered numerically, dates chronologically and text according to the database's comparison rules.

A range may have two limits or only one. A temperature above 30 is a one-sided condition; a temperature at least 20 and below 30 has two boundaries. Neither automatically implies a particular ordering of the returned rows.

The term describes a request. A table containing ordinary timestamp or numeric columns can support range queries. A database range data type is a separate feature that stores an interval as a value and can support operations such as testing overlap.

How it works

The query specifies a column or expression, suitable boundary values and comparison operators. For example, price >= 20 AND price < 50 selects prices from 20 inclusive to 50 exclusive. In SQL, BETWEEN includes both endpoints; this differs from a half-open interval that excludes its upper boundary.

The database evaluates the condition using the relevant data types and comparison semantics. Missing values require a deliberate policy: an ordinary comparison with SQL NULL does not establish that the value falls within the range.

An ordered index may help locate the start of a range and traverse matching entries. PostgreSQL B-tree indexes support ordered comparisons; its hash indexes support equality comparisons instead. The query planner still chooses an execution strategy. A broad condition matching most rows may make a table scan appropriate.

The query must also express any required sorting, grouping or access restrictions. Selecting the right interval does not automatically supply those other requirements.

Three practical examples

A sales analyst selects orders from the start of one month up to, but excluding, the start of the next month.
A product catalog filters items priced between two chosen amounts, with a clear decision about whether the maximum price is included.
An AI evaluation process selects examples with confidence scores in a defined band to examine errors near a decision threshold.

Think of it this way

Think of finding books on numbered shelves from shelf 20 through shelf 29. An ordered catalog can help you begin in the relevant place, while the shelf numbers specify what belongs in the selection.

The analogy has limits. Database comparisons follow data-type rules, records may have missing values, and a query can use several conditions together. The existence of a catalog also does not guarantee that consulting it is faster than inspecting a small collection directly.

Worked example

A month without a boundary overlap

Suppose an invented event table stores timestamps in UTC. We want May events. Define the lower boundary as May 1 at 00:00:00 UTC and the upper boundary as June 1 at 00:00:00 UTC. Select timestamps greater than or equal to the lower boundary and strictly less than the upper boundary.

An event exactly at the start of May qualifies. An event at May 31, 23:59:59.500 qualifies. An event exactly at the start of June does not. The June query can begin at that same June boundary, including it once.

Using an inclusive upper boundary of May 31, 23:59:59 would miss the fractional-second event. Using BETWEEN with June 1 as the upper boundary would include the first June instant in May as well.

This example assumes UTC reporting. A report based on local calendar days must establish the relevant time zone and translate its boundaries consistently; the word month alone is not a complete timestamp specification.

Illustrative case study

A fictional learning platform finds that the sum of daily activity reports exceeds the distinct events in its monthly extract. Each daily query includes both midnight boundaries, so an event exactly at midnight appears in two adjacent reports.

The team adopts intervals that include their start and exclude their end. It creates test events just before, exactly at and just after midnight, then checks that each event belongs to one daily bucket. It also confirms that the dashboard and export use the same reporting time zone.

The correction changes selection, preserving the source events. The case illustrates why boundary tests can be more informative than checking only ordinary midday records.

Common misconceptions and mistakes

BETWEEN excludes the upper endpoint. In SQL it includes both endpoints; use explicit comparisons when a different convention is required.
A range query always uses an index. Index suitability, selectivity and the planner's cost estimates affect the chosen execution strategy.
Numeric-looking text necessarily sorts numerically. Stored types and comparison rules determine ordering; text values can produce an unintended selection.

Business use

Range queries underpin reporting periods, price filters, operational monitoring and dataset selection. Their business meaning depends on precisely defining the field, units and endpoints. Revenue by payment time is different from revenue by order time even when the dates match.

For an AI dataset, preserve the selection rule alongside the extracted records. A training window, score interval or age band should be reproducible, including its treatment of missing values and boundary cases.

For performance, measure representative narrow and broad ranges rather than assuming one index will improve every request. Inspect the execution plan and actual workload. For correctness, test endpoint values and adjacent intervals before relying on aggregate totals. These checks help separate a faulty definition of the requested population from a slow implementation of an otherwise correct query.

Check your understanding

For the condition score >= 0.5 AND score < 0.8, does a score of exactly 0.8 qualify?

Answer: No. The upper boundary is exclusive. A score of exactly 0.5 qualifies because the lower comparison includes equality.

Related terms

Query language
SQL
B-tree index
comparison operator
interval
timestamp
query planner
selectivity
range data type

Learn next

Read inclusive and exclusive intervals. Create five sample timestamps around a reporting boundary, predict their membership in adjacent periods and compare that prediction with the query results.

Sources

PostgreSQL, Comparison Functions and Operators: https://www.postgresql.org/docs/current/functions-comparison.html. Primary documentation of comparisons and inclusive BETWEEN semantics.

PostgreSQL, Index Types: https://www.postgresql.org/docs/current/indexes-types.html. Primary documentation of B-tree and hash query support.

PostgreSQL, Range Types: https://www.postgresql.org/docs/18/rangetypes.html. Primary documentation distinguishing interval-valued data from ordinary scalar comparisons. The event example and platform case are original illustrations.

Previous: Range minimum query Next: Range restrictions