Postgres Date BETWEEN Reference: Ranges and Timestamps
TL;DR: In PostgreSQL, col BETWEEN '2026-11-01' AND '2026-11-30' is inclusive of both dates and works correctly on a date column. On a timestamp column it silently drops every row after midnight on the last day, so use the half-open form col >= '2026-11-01' AND col < '2026-12-01'. For overlap checks use daterange with && or the OVERLAPS operator. If you would rather ask "orders from last week" in plain English, AI for Database writes and runs the query and shows you the SQL.
To query between two dates in Postgres, write WHERE order_date BETWEEN '2026-11-01' AND '2026-11-30' when the column is a date. a BETWEEN x AND y is equivalent to a >= x AND a <= y, so both endpoints are included (PostgreSQL docs, Comparison Functions and Operators). When the column is a timestamp or timestamptz, the end date '2026-11-30' means 2026-11-30 00:00:00, so BETWEEN misses the whole last day. The safe pattern for both types is >= start AND < end_plus_one_day.
Try AI for Database free at aifordatabase.com (https://app.aifordatabase.com/signup, sample data included).
Postgres date range cheat sheet
| Need | Pattern | Works on |
|---|---|---|
| Between two dates, inclusive | d BETWEEN '2026-11-01' AND '2026-11-30' | date only |
| A whole month, any type | ts >= '2026-11-01' AND ts < '2026-12-01' | date, timestamp, timestamptz |
| Last 30 days | ts >= current_date - 30 | all |
| Today | ts >= current_date AND ts < current_date + 1 | all |
| Current month | ts >= date_trunc('month', now()) | all |
| Endpoints in either order | d BETWEEN SYMMETRIC x AND y | all |
| Outside a range | d NOT BETWEEN x AND y | all |
| Date in a range value | daterange('2026-11-01','2026-12-01') @> d | date |
| Two periods overlap | daterange(a1,a2) && daterange(b1,b2) | date |
| Two periods overlap (SQL standard) | (a1, a2) OVERLAPS (b1, b2) | date, time, timestamp |

What AI for Database does with date ranges
Most date-range bugs come from someone writing the filter by hand under time pressure. AI for Database takes that step away for teams without a data team:
- Ask in plain English. Connect PostgreSQL (or MySQL, SQL Server, BigQuery, Supabase and more), type "revenue by day for the last 30 days", and get a table or chart. Every answer shows its SQL, so you can check the date filter before you trust the number.
- Read-only. Answers run read-only, so a test query can't change data.
- Dashboards that refresh themselves. Pin "signups this month" and it re-runs on a schedule, so the date window moves forward without anyone editing it.
- Workflows on data changes. "When daily signups drop below 50, send a Slack alert" watches a date window for you.
- Price. Start free with sample data; Pro is $10 per user per month (aifordatabase.com/pricing, as of 2026-10-23).
What it does not do: change your column types. If your table stores dates as text, fix that in the schema first.
Is BETWEEN inclusive in Postgres?
Yes. BETWEEN includes both endpoints. The docs give 2 BETWEEN 1 AND 3 → t, and the predicate is defined as a >= x AND a <= y. Order matters: 2 BETWEEN 3 AND 1 returns false because the lower bound comes first. Add SYMMETRIC if the endpoints may arrive in either order, for example from two user-picked dates:
-- date column: inclusive on both ends, correct
SELECT id, order_date
FROM orders
WHERE order_date BETWEEN DATE '2026-11-01' AND DATE '2026-11-30';
-- endpoints from a UI may come in any order
SELECT id
FROM orders
WHERE order_date BETWEEN SYMMETRIC DATE '2026-11-30' AND DATE '2026-11-01';NOT BETWEEN is the plain negation, so d NOT BETWEEN x AND y means d < x OR d > y.
Why does BETWEEN miss rows on the last day?
Because a date literal compared with a timestamp becomes midnight. created_at BETWEEN '2026-11-01' AND '2026-11-30' stops at 2026-11-30 00:00:00, so an order at 14:40 that afternoon is left out. The PostgreSQL wiki page Don't Do This lists "Don't use BETWEEN (especially with timestamps)" for exactly this reason, and points at a second bug: a row stamped exactly at midnight on the boundary is counted in two adjacent ranges.
The fix is the half-open range, inclusive start and exclusive end:
-- timestamp or timestamptz: every row in November, none of December
SELECT count(*)
FROM orders
WHERE created_at >= '2026-11-01'
AND created_at < '2026-12-01';Two common workarounds are worse. BETWEEN '2026-11-01' AND '2026-11-30 23:59:59' misses anything in the last fractional second (Postgres timestamps keep microseconds). created_at::date BETWEEN ... gives the right answer but wraps the column in a cast, so a plain index on created_at can't be used for the range scan. The half-open form avoids both and uses the index.
With timestamptz, the literal '2026-11-01' is read in the session's TimeZone setting. If your report is "November in Sydney", say so: created_at >= TIMESTAMPTZ '2026-11-01 00:00 Australia/Sydney'.
How do I query the last 30 days?
current_date returns today's date, and date + integer adds whole days (PostgreSQL docs, Date/Time Functions). Use them for rolling windows:
-- last 30 days including today
SELECT date_trunc('day', created_at) AS day, count(*)
FROM signups
WHERE created_at >= current_date - 29
AND created_at < current_date + 1
GROUP BY 1
ORDER BY 1;
-- current calendar month so far
SELECT sum(amount)
FROM payments
WHERE paid_at >= date_trunc('month', now());
-- previous calendar month
SELECT sum(amount)
FROM payments
WHERE paid_at >= date_trunc('month', now()) - interval '1 month'
AND paid_at < date_trunc('month', now());date_trunc(field, source [, time_zone]) rounds down: date_trunc('hour', timestamp '2001-02-16 20:38:40') returns 2001-02-16 20:00:00. Truncating now() is fine because it is a single value, not the column, so the index still applies.
How do I check if two date ranges overlap?
Postgres has two built-in ways. The daterange type uses the canonical form [), lower bound inclusive and upper bound exclusive, the same half-open rule as above (Range Types). && tests overlap and @> tests containment (Range Functions):
-- bookings that overlap a requested stay of Nov 10 to Nov 14 (checkout day free)
SELECT id
FROM bookings
WHERE daterange(check_in, check_out) && daterange('2026-11-10', '2026-11-14');
-- is a given date inside a range column?
SELECT id FROM campaigns WHERE active_during @> DATE '2026-11-18';The SQL-standard OVERLAPS operator works on dates, times and timestamps without a range type. Each period is treated as half-open start <= time < end, so two periods that only share an endpoint do not overlap, and the endpoints of each pair can be given in either order:
SELECT (DATE '2026-11-01', DATE '2026-11-10')
OVERLAPS (DATE '2026-11-10', DATE '2026-11-20'); -- false, they only touchFor a range column, a GiST index speeds up =, &&, <@, @>, <<, >>, -|-, &< and &>. To stop overlapping rows from being inserted at all, use an exclusion constraint:
CREATE TABLE room_booking (
room int,
during daterange,
EXCLUDE USING GIST (room WITH =, during WITH &&)
);
-- room WITH = needs: CREATE EXTENSION btree_gist;
How do I list every date between two dates?
Use generate_series with a timestamp start, a stop and an interval step, then cast to date. This is the basis of a report that shows zero for days with no rows:
SELECT d::date AS day, count(o.id) AS orders
FROM generate_series(DATE '2026-11-01', DATE '2026-11-07', interval '1 day') AS d
LEFT JOIN orders o
ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY 1
ORDER BY 1;Version notes
BETWEEN,BETWEEN SYMMETRICandOVERLAPShave been in PostgreSQL for many major versions; the patterns above run on every supported release.- Range types (
daterange,tsrange,tstzrange) arrived in PostgreSQL 9.2; multiranges such asdatemultirangearrived in PostgreSQL 14. date_truncwith a thirdtime_zoneargument arrived in PostgreSQL 12.- A
dateuses 4 bytes and stores values from 4713 BC to 5874897 AD inyyyy-mm-ddform (Neon DATE tutorial).
Related: NVL in Postgres, PostgreSQL EXPLAIN, scheduling a Postgres query, the SQL cheat sheet and Postgres Slack alerts.
Frequently Asked Questions
Is BETWEEN inclusive in Postgres?
Yes. a BETWEEN x AND y equals a >= x AND a <= y, so both dates are included. On a timestamp column the end date means midnight, so the rest of that day is excluded.
Why does BETWEEN miss rows on the last day?
The end literal '2026-11-30' becomes 2026-11-30 00:00:00 when compared with a timestamp. Use >= '2026-11-01' AND < '2026-12-01' instead.
How do I query the last 30 days in Postgres?
WHERE created_at >= current_date - 29 AND created_at < current_date + 1 covers 30 calendar days including today. Use now() - interval '30 days' if you want a rolling 720 hours instead.
How do I check if two date ranges overlap?
daterange(a_start, a_end) && daterange(b_start, b_end), or (a_start, a_end) OVERLAPS (b_start, b_end). Both treat the end as exclusive.
Does casting to date break the index?
A filter like created_at::date BETWEEN x AND y can't use a plain index on created_at. Keep the column bare and put the arithmetic on the constants.
Can I query date ranges without writing SQL?
Yes. AI for Database connects to PostgreSQL, turns "orders from last week" into SQL, runs it read-only and shows the query it used. > Try AI for Database free at aifordatabase.com (https://app.aifordatabase.com/signup, sample data included).