Date Range Filtering — Filter by date ranges and time windows (conceptual) - SQL

SECTION 1

Historical Context & Motivation

The need to filter records by date is as old as record-keeping itself, but the formalization of date range filtering in relational databases arose from the convergence of temporal data modeling and the structured query paradigm. Early hierarchical and network databases of the 1960s stored dates as raw integers or packed character strings, requiring application-layer logic to interpret and compare temporal values. It was not until E.F. Codd's relational model gained traction that the notion of declarative filtering — specifying what data to retrieve rather than how to navigate to it — made temporal predicates a first-class concern in query language design.

1970 • Codd's Relational Model

E.F. Codd publishes his seminal paper at IBM, establishing the theoretical foundation for relational databases. Selection predicates — including those over temporal columns — are now expressible in relational algebra.

1986 • SQL-86 Standard

ANSI adopts the first SQL standard. The DATE data type is recognized, but implementations vary widely across vendors. Range comparisons use standard comparison operators (<, >, BETWEEN).

1992 • SQL-92 and TIMESTAMP

SQL-92 introduces the TIMESTAMP and INTERVAL types, along with EXTRACT and CAST functions, giving developers portable tools for sub-day precision filtering and date arithmetic.

2003 • SQL:2003 and Window Functions

The introduction of window functions (OVER, PARTITION BY, RANGE BETWEEN) enables sliding-window computations over date-ordered result sets without self-joins.

2011–present • Temporal Tables & Modern Extensions

SQL:2011 introduces system-versioned temporal tables (FOR SYSTEM_TIME). Modern engines like PostgreSQL, BigQuery, and Snowflake add rich date/time function libraries and time-zone-aware types.

Throughout this evolution, a central question persisted: how should a query engine let analysts express temporal boundaries — ranging from simple calendar-date comparisons to complex sliding windows — with both precision and clarity? Date range filtering addresses exactly this question, and understanding its conceptual underpinnings is essential before diving into vendor-specific syntax.

SECTION 2

Core Principles & Definitions

Date range filtering rests on a small set of foundational ideas that generalize across all SQL dialects. Understanding these principles lets you reason about temporal queries abstractly before worrying about whether your engine supports DATE_TRUNC or DATEADD. The concepts below form the backbone of every temporal filter you will ever write.

Closed vs. Half-Open Intervals

A closed interval [a, b] includes both endpoints. A half-open interval [a, b) includes the start but excludes the end. Half-open intervals avoid off-by-one bugs and partition timelines without gaps or overlaps.

Granularity & Precision

Granularity is the smallest unit at which you inspect time (day, hour, second, microsecond). Precision mismatches — for example, filtering a TIMESTAMP column with a DATE literal — are a leading source of subtle bugs in temporal queries.

Inclusive BETWEEN Semantics

SQL's BETWEEN operator is inclusive on both ends (a closed interval). When used with TIMESTAMP columns, this can unintentionally include an entire extra day if the upper bound is a midnight boundary.

Sargability

A predicate is sargable (Search ARGument ABLE) if the query optimizer can leverage an index to satisfy it. Wrapping a column in a function (e.g., YEAR(col) = 2024) destroys sargability; expressing the same filter as a range predicate on the raw column preserves it.

Time Zones & UTC Normalization

Storing timestamps in UTC and converting at query time is the standard practice. A date range that spans midnight in one time zone may map to a different calendar date in another, making explicit zone handling critical for correctness.

✦

KEY TAKEAWAY

Think of date range filtering like selecting a segment on a ruler. A half-open interval is like cutting the ruler at two marks and keeping everything from the left mark up to — but not including — the right mark. This convention ensures that consecutive cuts tile the ruler perfectly with no gaps and no overlaps, exactly the property you want when partitioning a timeline into days, weeks, or months.

SECTION 3

Visual Explanation — The Timeline Model

The diagram below illustrates how different SQL predicates map onto a continuous timeline. Each row represents a different filtering technique, and the shaded region shows which portion of the timeline is selected. Notice how the half-open interval pattern (row 3) avoids the boundary ambiguity present in the inclusive BETWEEN approach (row 2).

Row 1 shows point-in-time equality. Row 2 illustrates the inclusive BETWEEN, where both endpoints (solid circles) are selected. Row 3 demonstrates the preferred half-open pattern — the left boundary is included (solid) while the right is excluded (open circle). Row 4 depicts a rolling window anchored to the current date.

The visual distinction between the closed circle and the open circle in rows 2 and 3 encodes a concept that has real consequences in production SQL. When a TIMESTAMP column stores values like 2024-02-15 00:00:00, an inclusive upper bound (BETWEEN) will match records at exactly midnight on the boundary date, while a half-open predicate (< '2024-02-15') will exclude them. This seemingly minor detail can shift aggregation results by an entire day's worth of transactions.

SECTION 4

How It Works — Predicate Patterns & Date Arithmetic

At the execution level, every date range filter reduces to one or two comparison predicates that the query optimizer evaluates against an index or during a sequential scan. The key is to express your intent using patterns that the optimizer can translate into efficient index seeks. Below are the canonical predicate patterns, each annotated with its interval semantics.

CLOSED INTERVAL (BETWEEN)

WHERE col BETWEEN a AND b ⟺ WHERE col >= a AND col <= b

Both a and b are included. Safe when col is of type DATE (day precision). Risky when col is TIMESTAMP with sub-day precision.

HALF-OPEN INTERVAL (PREFERRED)

WHERE col >= a AND col < b

Includes a, excludes b. Immune to precision mismatches. Consecutive ranges [a, b) ∪ [b, c) cover the timeline with no gaps.

RELATIVE / ROLLING WINDOW

WHERE col >= CURRENT_DATE − INTERVAL 'N days' AND col < CURRENT_DATE

Anchors the window to the execution date. N is the lookback period. The half-open upper bound ensures today's partial data is excluded unless desired.

⚠

Sargability Warning

Applying a function to the column side of a predicate — such as WHERE YEAR(order_date) = 2024 — forces a full table scan because the optimizer cannot seek into a B-tree index on the transformed result. The sargable equivalent is WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01', which leverages the index directly. Always keep the column "bare" on one side of the comparison.

Date arithmetic varies by dialect — PostgreSQL uses INTERVAL syntax, SQL Server uses DATEADD, and MySQL accepts both DATE_ADD and INTERVAL expressions — but the conceptual model is identical: you compute a boundary value and then compare the column against it.

SECTION 5

Detailed Breakdown — Common Filtering Patterns

In practice, date range filters fall into a handful of recurring patterns. The diagram below classifies them by whether the boundaries are absolute (hard-coded dates) or relative (computed from the current date), and whether the window is fixed-width or variable. Understanding this taxonomy lets you select the right pattern before writing a single line of SQL.

The taxonomy splits date filters into absolute (compile-time-known boundaries) and relative (execution-time-computed boundaries). Leaf nodes show the SQL predicate skeleton and a typical use case. The selection guide at the bottom maps requirements to the appropriate pattern.

Pattern	Boundary Type	Typical Use Case	Sargable?
Fixed calendar period	Absolute	Quarterly financial report	✓ Yes
Ad-hoc range (parameterized)	Absolute	User-driven dashboard filter	✓ Yes
Rolling window	Relative	Last-30-day active users	✓ Yes
Fiscal / logical period	Relative (computed)	Custom fiscal-year reporting	✓ If boundaries are pre-computed
Function-wrapped column	N/A	Anti-pattern (e.g., YEAR(col) = 2024)	✗ No — forces full scan

Comparison of date range filtering patterns by boundary type, typical use case, and index friendliness.

SECTION 6

Worked Example — Monthly Sales Report

Suppose you have an orders table with a placed_at TIMESTAMP column (stored in UTC) and a total_usd DECIMAL(10,2) column. Your task is to compute total revenue for February 2024, handling the leap year correctly, while ensuring the query is sargable and immune to time-of-day boundary issues.

Computing February 2024 Revenue with a Half-Open Interval

1
Step 1 — Identify the Half-Open Boundaries

February 2024 starts at 2024-02-01 00:00:00 UTC (inclusive). Because 2024 is a leap year, the month ends on February 29, and the exclusive upper bound is 2024-03-01 00:00:00 UTC. Using the next month's first instant as the upper bound means we never have to think about 23:59:59.999999.

Range: [2024-02-01, 2024-03-01)
2
Step 2 — Write the Sargable WHERE Clause

The predicate keeps the placed_at column bare on the left side of both comparisons, enabling the optimizer to seek into a B-tree index on placed_at.

WHERE placed_at >= '2024-02-01' AND placed_at < '2024-03-01'
3
Step 3 — Aggregate and Construct the Full Query

We wrap the filter in a simple aggregation query. Note that some SQL dialects will implicitly cast the date literals to TIMESTAMP. If your engine does not, you can explicitly cast: TIMESTAMP '2024-02-01'.

SELECT SUM(total_usd) AS feb_revenue FROM orders WHERE placed_at >= '2024-02-01' AND placed_at < '2024-03-01';
4
Step 4 — Verify Edge Cases

An order placed at exactly 2024-02-29 23:59:59.999999 is less than '2024-03-01' and is correctly included. An order at exactly 2024-03-01 00:00:00.000000 is not less than the upper bound and is correctly excluded. The leap day is handled automatically because we specified the boundary as a date rather than manually counting days.

No off-by-one errors — boundary-safe ✓

💡

Alternative: Relative Version

If you need last month's revenue dynamically (not hard-coded to February), replace the literals with date arithmetic:

WHERE placed_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month') AND placed_at < DATE_TRUNC('month', CURRENT_DATE)

. The DATE_TRUNC function snaps a timestamp to the start of the specified unit, yielding the correct first-of-month boundary regardless of how many days the month contains.

SECTION 7

Strengths, Limitations & Common Pitfalls

Date range filtering is deceptively simple in concept but fraught with subtle pitfalls in practice. The table below contrasts the strengths of each approach with the limitations and failure modes that trip up even experienced engineers.

Approach	Strengths	Limitations / Pitfalls
BETWEEN	Concise syntax; readable for DATE columns; widely supported.	Inclusive upper bound catches midnight timestamps; developers must remember to subtract one day or one microsecond for TIMESTAMP columns.
Half-open (>=, <)	Precision-safe; partitions timeline without gaps; sargable.	Slightly more verbose; upper bound must be the "next" boundary, which can be non-obvious for irregular periods (e.g., fiscal quarters).
EXTRACT / YEAR()	Highly readable; maps directly to business language ("all January orders").	Non-sargable — destroys index utilization; can be orders of magnitude slower on large tables.
Rolling INTERVAL	No hard-coded dates; self-updating; great for dashboards and alerts.	Results change on every execution — not reproducible without snapshotting the execution timestamp. Time-zone shifts can cause unexpected behavior.
DATE_TRUNC + range	Combines relative logic with precise boundaries; handles variable-length months.	Vendor-specific syntax (DATE_TRUNC vs. DATEADD/DATEDIFF); may need explicit casting for cross-type comparisons.

Strengths and limitations of common date range filtering approaches.

✦

KEY TAKEAWAY

Choosing a date filtering strategy is analogous to choosing a hash function in systems design: the "best" option depends on your invariant requirements. If you need reproducibility (same result every time), use absolute boundaries. If you need freshness (always the latest window), use relative boundaries. If you need performance, keep your predicates sargable. These goals can coexist, but you must be intentional about each.

SECTION 8

Connection to Advanced Temporal Querying

Date range filtering is the gateway to a rich family of temporal techniques in modern SQL. Once you are comfortable with static range predicates, the next conceptual leap is to window functions over ordered time series, temporal joins that correlate events within overlapping intervals, and system-versioned temporal tables that track the full history of row mutations. The table below maps the basic concepts covered in this lesson to their advanced counterparts.

Basic Concept (This Lesson)	Advanced Extension	Key Addition
WHERE col >= a AND col < b	RANGE BETWEEN in window frames	Sliding aggregation without self-join; window defined by INTERVAL offset
Rolling INTERVAL predicates	LAG / LEAD analytics	Access prior/next row values for period-over-period comparisons
Filtering on a single TIMESTAMP column	Temporal joins (OVERLAPS, Allen's intervals)	Join two tables on overlapping validity periods, not just equality
Static snapshot of current data	System-versioned temporal tables (SQL:2011)	Automatic history tracking; FOR SYSTEM_TIME AS OF / BETWEEN queries
DATE_TRUNC for boundary alignment	Time-series databases & partitioning	Physical storage optimized for time-ordered range scans (e.g., TimescaleDB hypertables)

Mapping basic date range concepts to advanced temporal SQL features.

As data volumes grow and temporal queries become more complex, understanding the conceptual foundations laid in this lesson becomes increasingly valuable. The half-open interval convention, sargability awareness, and UTC normalization discipline carry over directly to distributed SQL engines like BigQuery, Snowflake, and Apache Spark SQL, where partition pruning on date columns is often the single most impactful optimization available.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why WHERE placed_at BETWEEN '2024-03-01' AND '2024-03-31' might exclude some March 31 records when placed_at is a TIMESTAMP column. What interval convention avoids this problem?

Show answer

When placed_at is a TIMESTAMP, the literal '2024-03-31' is cast to 2024-03-31 00:00:00. BETWEEN is inclusive, so only records at exactly midnight on March 31 match the upper bound; any record with a non-zero time component (e.g., 2024-03-31 14:30:00) is excluded because it exceeds the bound. The half-open interval convention — WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01' — captures every timestamp in March regardless of sub-day precision, because any value before midnight on April 1 satisfies the strict less-than.

Question 2: BASIC CALCULATION

Rewrite the following non-sargable query into an equivalent sargable form: SELECT * FROM events WHERE EXTRACT(YEAR FROM event_time) = 2023 AND EXTRACT(MONTH FROM event_time) = 7;

Show answer

The sargable equivalent is: SELECT * FROM events WHERE event_time >= '2023-07-01' AND event_time < '2023-08-01'; This removes the EXTRACT functions that prevent index usage. The optimizer can now perform an index range scan on event_time, seeking directly to the first qualifying row and stopping at the upper bound. The half-open interval ensures all July timestamps are captured without needing to know the maximum possible sub-second precision.

Question 3: INTERMEDIATE

Write a PostgreSQL query that returns the count of orders placed in the last 90 days (relative to today), grouped by calendar week. Use DATE_TRUNC for the grouping and ensure the WHERE clause is sargable.

Show answer

A correct solution is:

SELECT DATE_TRUNC('week', placed_at) AS week_start, COUNT(*) AS order_count FROM orders WHERE placed_at >= CURRENT_DATE - INTERVAL '90 days' AND placed_at < CURRENT_DATE GROUP BY DATE_TRUNC('week', placed_at) ORDER BY week_start;

The WHERE clause is sargable because placed_at is compared directly to computed constants (the INTERVAL arithmetic is evaluated once at plan time, not per row). DATE_TRUNC appears only in SELECT and GROUP BY, which does not affect the index scan on the WHERE predicate. The half-open upper bound < CURRENT_DATE excludes partial today data, which is a deliberate design choice for cleaner weekly aggregation.

Question 4: APPLIED

A SaaS company stores user login events in a table logins(user_id INT, login_at TIMESTAMPTZ) in UTC. The product team needs a query that returns users who logged in during "business hours" (9 AM – 5 PM) in the US Eastern time zone on any day in the week of 2024-04-15. Write the query and explain why time zone handling matters here.

Show answer

One approach is:

SELECT DISTINCT user_id FROM logins WHERE login_at >= TIMESTAMP '2024-04-15 00:00:00' AT TIME ZONE 'America/New_York' AND login_at < TIMESTAMP '2024-04-22 00:00:00' AT TIME ZONE 'America/New_York' AND EXTRACT(HOUR FROM login_at AT TIME ZONE 'America/New_York') >= 9 AND EXTRACT(HOUR FROM login_at AT TIME ZONE 'America/New_York') < 17;

The week boundaries are converted from Eastern to UTC by the AT TIME ZONE clause, keeping the week filter sargable. The hour extraction is applied in the local zone so that 9 AM means 9 AM Eastern. Note that the hour filter is non-sargable, so the optimizer will first prune to the one-week range via an index and then scan within that range — an acceptable cost. Time zone handling matters because during EDT (UTC−4), 9 AM Eastern is 13:00 UTC, so filtering on raw UTC hours would be incorrect.

Question 5: CRITICAL THINKING

A colleague argues that using WHERE CAST(placed_at AS DATE) = '2024-06-15' is "cleaner" than the half-open interval WHERE placed_at >= '2024-06-15' AND placed_at < '2024-06-16'. Provide a rigorous argument for or against this claim, considering correctness, performance, and portability across SQL dialects.

Show answer

The CAST approach is semantically correct — it truncates the timestamp to a date and compares — but it has three significant drawbacks. First, applying CAST to the column destroys sargability; the optimizer cannot use an index on placed_at because it sees a function-wrapped expression. On a table with millions of rows, this can mean the difference between a sub-millisecond index seek and a minutes-long full scan. Second, the behavior of CAST(TIMESTAMPTZ AS DATE) is dialect-dependent: PostgreSQL truncates in the session time zone, MySQL in the server zone, and SQL Server raises an implicit conversion. This undermines portability. Third, the CAST adds per-row computational overhead. The half-open interval, by contrast, is universally supported, index-friendly, and explicit about time-zone boundaries because you control the literal values. The only advantage of the CAST approach is readability, which is outweighed by the performance and correctness risks. If readability is the goal, a well-named CTE or view can wrap the half-open predicate with a self-documenting alias.

SUMMARY

Summary — Date Range Filtering

Summary

Date range filtering is the practice of selecting rows whose temporal column falls within a specified interval. The most robust pattern is the half-open interval — WHERE col >= start AND col < end — which avoids the precision mismatches inherent in BETWEEN when applied to TIMESTAMP columns. Boundaries may be absolute (hard-coded dates for reproducible reports) or relative (computed from CURRENT_DATE or NOW() for rolling dashboards).

A critical performance consideration is sargability: keep the date column bare in the predicate so the optimizer can leverage B-tree indexes. Wrapping the column in functions like YEAR() or CAST() prevents index seeks and forces full scans. Finally, always store and compare timestamps in UTC, converting to local time zones at the presentation layer. These principles — half-open intervals, sargable predicates, and UTC normalization — form the foundation for all advanced temporal querying, from window functions to system-versioned temporal tables.