How to Query Between Date Ranges in PostgreSQL

To filter PostgreSQL rows by date, use a comparison for a rolling window, BETWEEN for fixed inclusive bounds, or daterange containment for a date-only range. The examples below use a DATE column. A DATE has no time of day; timestamp columns need bounds that account for their time component.

Set Up a Sample Table

The sample table stores each login day in login_date. These representative rows are used to explain which dates each query selects; the SQL snippets are examples to run in a PostgreSQL session.

Create the table with CREATE TABLE logger (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, login_date DATE NOT NULL);. Then add rows with INSERT INTO logger (id, name, login_date) VALUES (1, 'Jhon', '2020-06-06'), (2, 'Alice', '2022-06-06'), (3, 'Bon', '2021-06-06'), (4, 'Trude', '2020-02-02'), (5, 'Jene', '2022-02-22'), (6, 'Dan', '2022-01-20');.

Filter a Rolling Window with INTERVAL

A rolling window moves with the current date. Subtract an INTERVAL from CURRENT_DATE to calculate its start, and compare the date column with that cutoff. For the last 120 days, use SELECT id, name, login_date FROM logger WHERE login_date > CURRENT_DATE - INTERVAL '120 days' ORDER BY id;. The strict > excludes a row exactly on the cutoff date; use >= when that date should be included. This condition has no future-date upper limit, so add AND login_date <= CURRENT_DATE if future-dated rows must be excluded.

Because CURRENT_DATE changes over time, the selected rows change too. To preview the fixed inclusive-date matching behavior in the supplied sample without relying on today’s date, run this standalone Python example. It illustrates date comparisons only; it does not execute PostgreSQL SQL.

from datetime import date

rows = [
    (1, "Jhon", date(2020, 6, 6)),
    (2, "Alice", date(2022, 6, 6)),
    (3, "Bon", date(2021, 6, 6)),
    (4, "Trude", date(2020, 2, 2)),
    (5, "Jene", date(2022, 2, 22)),
    (6, "Dan", date(2022, 1, 20)),
]
start, end = date(2021, 1, 1), date(2022, 3, 10)
for row_id, name, login_date in rows:
    if start <= login_date <= end:
        print(row_id, name, login_date.isoformat())

3 Bon 2021-06-06
5 Jene 2022-02-22
6 Dan 2022-01-20

Use BETWEEN for Inclusive Date Bounds

Use BETWEEN when both endpoints are fixed and should be included. This complete query selects dates from January 1, 2021 through March 10, 2022, including rows on either endpoint: SELECT id, name, login_date FROM logger WHERE login_date BETWEEN DATE '2021-01-01' AND DATE '2022-03-10' ORDER BY id;. BETWEEN is equivalent to a lower-bound comparison with >= and an upper-bound comparison with <=.

For a DATE column, the upper bound includes the full calendar day because the stored value has no time component. For a timestamp column, a bound such as midnight at the start of the final day does not include the rest of that day. Use an exclusive bound at the start of the following day, or otherwise choose timestamp bounds that match the precision and time zone of the data.

Use daterange for Date-Only Range Queries

PostgreSQL’s daterange type represents a range of calendar dates. The @> operator asks whether the range contains a value. To select rows from June 6, 2021 through March 10, 2022, use SELECT id, name, login_date FROM logger WHERE daterange(DATE '2021-06-06', DATE '2022-03-11', '[)') @> login_date ORDER BY id;. The range includes its lower bound and excludes its upper bound, so using March 11 as the exclusive endpoint includes March 10. This form makes the endpoint convention explicit and works with the sample’s DATE values.

Choose BETWEEN when inclusive fixed bounds make the query easiest to read. Choose daterange when range containment expresses the operation more clearly or when the range value itself is useful elsewhere in the query. These examples concern dates; they do not establish timestamp range behavior.

Handle Date Formats and Moving Endpoints

Write date literals in the unambiguous YYYY-MM-DD form, such as DATE '2021-06-06'. The explicit DATE type makes clear that the value is a calendar date. CURRENT_DATE supplies the database session’s current date, so it is useful for queries that should move forward automatically, such as a rolling interval filter.

For a stable report, use fixed date bounds so the same period is queried each time. For a query that should follow the calendar, calculate a cutoff from CURRENT_DATE and INTERVAL. Keep the data type in mind when choosing endpoints: inclusive date bounds are straightforward for DATE, while timestamp filters need a time-aware upper bound.