PostgreSQL: How to Select Rows in Date Range

Often in PostgreSQL you may want to select rows that fall between a specific date range.

You can use the following basic syntax to do so:

SELECT *
FROM emp
WHERE '[2022-01-01, 2022-10-01]'::daterange @> start_date;

This particular example will select all rows from the table named emp where the date in the start_date column is between January 1, 2022 and October 1, 2022.

The following example shows how to use this syntax in practice.

Example: How to Select Rows in Date Range in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT PRIMARY KEY,
  start_date DATE,
  sales INT
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);

-- view all rows in table
SELECT * FROM emp;

 empid | start_date | sales 
-------+------------+-------
     1 | 2021-04-01 |   134
     2 | 2022-07-01 |   201
     3 | 2022-08-15 |   155
     4 | 2022-09-01 |    98
     5 | 2023-11-23 |    65
     6 | 2024-02-20 |    52
     7 | 2024-05-15 |    50

We can use the following syntax to select all rows where the start_date column is between January 1, 2022 and October 1, 2022:

SELECT *
FROM emp
WHERE '[2022-01-01, 2022-10-01]'::daterange @> start_date;

 empid | start_date | sales 
-------+------------+-------
     2 | 2022-07-01 |   201
     3 | 2022-08-15 |   155
     4 | 2022-09-01 |    98

Notice that this returns three rows from the table.

We can see that the dates in the start_date column of  each row is indeed between January 1, 2022 and October 1, 2022.

Feel free to change the start and end dates to return rows that fall in a specific date range of your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Range of Rows from Table
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows