---
title: "Postgres Date BETWEEN Reference: Ranges and Timestamps"
description: "In PostgreSQL, col BETWEEN '2026-11-01' AND '2026-11-30' is inclusive of both dates and works correctly on a date column."
canonical: "https://www.aifordatabase.com/blog/postgres-date-between/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-18
updated: 2026-11-18
---

# 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](https://www.postgresql.org/docs/current/functions-comparison.html)). 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` |

![BETWEEN on a timestamp column drops rows after midnight on the end date](https://cdn.sanity.io/images/upsr56wu/production/0ef179dda7d8b8abd72f0f2c97f5375f0a792a2a-1344x440.png?w=1280)

FIG 01 — Order 3 on the last afternoon only shows up with the half-open range.

## 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](https://wiki.postgresql.org/wiki/Don%27t_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](https://www.postgresql.org/docs/current/functions-datetime.html)). 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](https://www.postgresql.org/docs/current/rangetypes.html)). `&&` tests overlap and `@>` tests containment ([Range Functions](https://www.postgresql.org/docs/current/functions-range.html)):

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

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

![A booking that ends on the 10th does not overlap one starting on the 10th](https://cdn.sanity.io/images/upsr56wu/production/bc33099c025cda4c10af5fc4c763a359eb8f6dcb-1344x410.png?w=1280)

FIG 02 — Half-open ranges let back-to-back bookings touch without overlapping.

## 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 SYMMETRIC` and `OVERLAPS` have 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 as `datemultirange` arrived in PostgreSQL 14.
*   `date_trunc` with a third `time_zone` argument arrived in PostgreSQL 12.
*   A `date` uses 4 bytes and stores values from 4713 BC to 5874897 AD in `yyyy-mm-dd` form ([Neon DATE tutorial](https://neon.com/postgresql/postgresql-tutorial/postgresql-date/)).

Related: [NVL in Postgres](https://www.aifordatabase.com/blog/nvl-in-postgres/), [PostgreSQL EXPLAIN](https://www.aifordatabase.com/blog/postgresql-explain-query/), [scheduling a Postgres query](https://www.aifordatabase.com/blog/postgres-schedule-query/), the [SQL cheat sheet](https://www.aifordatabase.com/blog/sql-cheat-sheet/) and [Postgres Slack alerts](https://www.aifordatabase.com/integrations/postgresql-slack-alerts/).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## 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).

Read next

*   [JSON EXTRACT IN MYSQL: JSON\_EXTRACT, -> AND ->> REFERENCE →](https://www.aifordatabase.com/blog/json-extract-mysql/)
*   [WHY IS POSTGRES BETTER THAN MYSQL? POSTGRES VS MYSQL →](https://www.aifordatabase.com/blog/why-is-postgres-better-than-mysql/)
*   [HOW TO QUERY RAILWAY POSTGRES WITHOUT SQL (2026 GUIDE) →](https://www.aifordatabase.com/blog/query-railway-postgres-without-sql-2026/)

Source: https://www.aifordatabase.com/blog/postgres-date-between/
