Date Filtering

Almost every production table has a created_at or updated_at column, and almost every report narrows down to some time window. Date filtering is not complicated - but there are a few traps that catch people even after they feel comfortable with SQL basics.

Basic date comparison

Date and timestamp columns work with 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';

PostgreSQL automatically casts string literals like '2024-01-01' to dates when you compare them against a date column. Stick to ISO format (YYYY-MM-DD) - other formats work inconsistently depending on locale settings.

Filtering a date range with BETWEEN

BETWEEN is inclusive on both ends, which sounds convenient. The problem is with timestamps. BETWEEN '2024-01-01' AND '2024-01-31' stops at 2024-01-31 00:00:00 - anything that happened later that day is silently excluded. That trips people up.

The pattern that actually covers a full day or full month:

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

Filtering relative to today

This is where date filtering gets genuinely useful. Use CURRENT_DATE or NOW() with interval arithmetic and your reports stay fresh without hardcoded dates:

-- 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

Sometimes you want all December orders regardless of year, or only weekday activity. EXTRACT pulls out a single component:

-- 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;

Here is the hidden performance trap: wrapping a column in a function like EXTRACT(YEAR FROM created_at) = 2024 prevents the database from using an index on created_at. On a large table that matters a lot. The equivalent explicit range - created_at >= '2024-01-01' AND created_at < '2025-01-01' - is index-friendly and usually much faster.

NULL dates

Comparison operators silently exclude NULL dates - no error, no warning, those rows just disappear from your results. If NULL means "no end date yet" (a common pattern for active subscriptions), you have to check for it explicitly:

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

Forgetting this is the kind of bug that looks fine in testing but silently undercounts in production.