Date Filtering

Filtering by date is one of the most common tasks in SQL. Most production tables have a created_at or updated_at column, and almost every report filters by some time window. The mechanics are straightforward once you know a few patterns.

Basic date comparison

Date and timestamp columns support the standard comparison operators directly:

SELECT * FROM orders WHERE created_at >= '2024-01-01';
SELECT * FROM orders WHERE created_at < '2024-02-01';
SELECT * FROM users  WHERE hire_date = '2023-06-15';

String literals like '2024-01-01' are automatically cast to dates when compared against a date column in PostgreSQL. Use ISO format (YYYY-MM-DD) to avoid ambiguity.

Filtering a date range with BETWEEN

BETWEEN is inclusive on both ends:

SELECT * FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31';

Be careful with timestamps. BETWEEN '2024-01-01' AND '2024-01-31' stops at 2024-01-31 00:00:00, so rows created later on January 31st are excluded. The safer pattern for full-day coverage:

SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2024-02-01';

Filtering relative to today

Use CURRENT_DATE or NOW() with interval arithmetic to express rolling windows:

-- Orders in the last 30 days
SELECT * FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

-- Users who signed up this calendar year
SELECT * FROM users
WHERE EXTRACT(YEAR FROM created_at) = EXTRACT(YEAR FROM CURRENT_DATE);

-- Records from this month
SELECT * FROM events
WHERE DATE_TRUNC('month', created_at) = DATE_TRUNC('month', NOW());

Filtering by a date component

When you only care about part of a date — the month, the year, the day of week — use EXTRACT:

-- All orders placed in December, any year
SELECT * FROM orders WHERE EXTRACT(MONTH FROM created_at) = 12;

-- Weekday orders only (Monday = 1, Sunday = 0 in PostgreSQL)
SELECT * FROM orders WHERE EXTRACT(DOW FROM created_at) BETWEEN 1 AND 5;

Note that filtering on EXTRACT(YEAR FROM created_at) = 2024 cannot use an index on created_at. The explicit range created_at >= '2024-01-01' AND created_at < '2025-01-01' is faster on large tables because it is index-friendly.

NULL dates

A row with a NULL date is excluded by all comparison operators. If NULL means "no end date yet" (e.g., an active subscription), check for it explicitly:

SELECT * FROM subscriptions
WHERE end_date IS NULL OR end_date > CURRENT_DATE;