By the end of this lesson you can filter date and timestamp columns for any period with half-open ranges, use EXTRACT and casts when you need part of a date, compute boundaries with INTERVAL, and explain why ranges are usually better than functions on the column.
Why it matters: Almost every business question has a time window: this month's revenue, last quarter's leavers, orders during Ramadan, late-night orders. Date filters are also where small mistakes (a lost last day, every December instead of one) most often reach a board report.
Dataset: Wadi Market online store. Examples run in the editor on the right.
PostgreSQL has two main kinds of time values:
- date: a calendar day with no time, such as
customers.signup_dateoremployees.hire_date. - timestamp: a date plus a time of day, such as
orders.ordered_at(2025-03-14 21:37:05).
You write both as ISO text in single quotes: '2025-03-14' or '2025-03-14 21:37'. To be explicit about the type, prefix it: DATE '2025-03-14', TIMESTAMP '2025-03-14 21:37'. When a date-only value meets a timestamp, it becomes midnight at the start of that day.
Periods: the half-open range
You've already used the most important pattern: inclusive start, exclusive end.
ordered_at >= '2025-02-01' AND ordered_at < '2025-03-01'
It has three big advantages. It works for dates and timestamps alike. You never need to know the last day of a month (28? 29? 31?) or the last second of a day: the end is always the first moment of the next period. And consecutive periods fit together with no gap and no overlap, so monthly totals add up to the yearly total.
WHERE ts_column >= 'start of period'
AND ts_column < 'start of the next period'SELECT order_id, ordered_at, status
FROM orders
WHERE ordered_at >= '2025-02-01'
AND ordered_at < '2025-03-01';Every order from 00:00:00 on 1 February to the last second of 28 February. The query doesn't need to know that February 2025 has 28 days; the boundary is simply 1 March.
Functions on the column: readable, but costly
The same filter can be written with functions that pull a part out of each timestamp:
EXTRACT(YEAR FROM ordered_at) = 2025 AND EXTRACT(MONTH FROM ordered_at) = 2date_trunc('month', ordered_at) = '2025-02-01'(rounds each timestamp down to the start of its month)ordered_at::date = '2025-11-28'(casts the timestamp to a date, dropping the time)
They return the same rows, and they read nicely. The catch is performance: PostgreSQL has to compute the function for every row before comparing, so it can't use an index on ordered_at (Wadi Market has one). On 6,000 orders you won't notice; on 60 million you will. Prefer a range on the raw column for periods, and keep functions for questions a range can't express.
SELECT order_id, ordered_at, status
FROM orders
WHERE EXTRACT(YEAR FROM ordered_at) = 2025
AND EXTRACT(MONTH FROM ordered_at) = 2;Exactly the same rows as the range version. Forget the YEAR condition, though, and you'd get February of 2023, 2024 and 2025 together, a classic mistake with month filters.
When you really need part of a date
Some questions aren't about a period at all: orders placed after midnight, orders on Fridays, hires in any April. Then EXTRACT is the right tool:
EXTRACT(HOUR FROM ordered_at) < 3: placed between 00:00 and 02:59EXTRACT(ISODOW FROM ordered_at) = 5: placed on a Friday (ISO weekday: Monday = 1 … Sunday = 7)EXTRACT(MONTH FROM hire_date) = 4: hired in April of any year
Combine them with a range when the question also has a period.
SELECT order_id, ordered_at, channel
FROM orders
WHERE ordered_at >= '2025-12-01'
AND ordered_at < '2026-01-01'
AND EXTRACT(HOUR FROM ordered_at) < 3;The range selects December 2025; EXTRACT(HOUR …) < 3 keeps orders placed between midnight and 02:59. Only a handful remain: outside Ramadan, few people shop in the small hours.
Computed boundaries: INTERVAL
Boundaries can be calculated. date + 7 adds days to a date. For timestamps, and for months and years, use an interval: TIMESTAMP '2026-01-01' - INTERVAL '7 days', DATE '2025-12-31' - INTERVAL '10 years', '2025-03-01'::date + INTERVAL '1 month'. In a live system you'd anchor these on CURRENT_DATE or now() ("the last 7 days"). SQL Quest's data ends on 31 December 2025, so exercises use fixed dates, which also keeps answers reproducible.
SELECT employee_id, first_name, last_name, hire_date
FROM employees
WHERE termination_date IS NULL
AND hire_date <= DATE '2025-12-31' - INTERVAL '10 years';The boundary is computed once: 2015-12-31. Current employees (no termination date) hired on or before that day had at least ten years of service at the end of 2025. They're long-service award candidates; all of them joined in 2015, the year Masar Holding was founded.