The most-typed date code of all: filtering a table to a recent window. Keep the function off the column so an index can still be used.
Last 7 days
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days' -- Postgres / DuckDB
WHERE created_at >= CURDATE() - INTERVAL 7 DAY -- MySQL
WHERE created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) -- BigQuery
WHERE created_at >= DATEADD(day, -7, CURRENT_DATE) -- Snowflake
WHERE created_at >= DATE('now', '-7 days') -- SQLite
Today only
WHERE created_at >= CURRENT_DATE
AND created_at < CURRENT_DATE + INTERVAL '1 day' -- half-open range, index-friendly
This month
-- Postgres / DuckDB / Snowflake
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
-- MySQL
WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
AND created_at < DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL 1 MONTH
-- SQLite
WHERE created_at >= DATE('now', 'start of month')
AND created_at < DATE('now', 'start of month', '+1 month')
The performance gotcha: filter with created_at >= <expr> — don’t wrap the column in a
function (WHERE DATE(created_at) = CURRENT_DATE or WHERE YEAR(created_at) = 2024), which prevents
the database from using an index on created_at. Use a half-open range (>= start AND < end)
instead of BETWEEN for timestamps, so the last day’s 23:59:60-ish values aren’t missed or
double-counted.