How to Query Between Date Ranges in PostgreSQL

1. Types of Ranges in PostgreSQL
2. Use the INTERVAL and Difference in Date Type in Postgres
3. Use the between in Date Type in Postgres
4. Use the daterange in Date Type in Postgres

We’ll discuss in this article the different types of ranges in PostgreSQL.

Types of Ranges in PostgreSQL

By default, Postgres provides some ranges to compare the values.

There’s a daterange type in the Postgres to compare dates. Also, we can use the between to compare the dates.

For demonstration, let’s create a table and populate it with some data.

Schema SQL:

CREATE TABLE logger(
    id SERIAL PRIMARY KEY,
    name VARCHAR (255) NOT NULL,
    login_date DATE NOT NULL DEFAULT CURRENT_DATE
);

After populating the table, do this Postgres Query SQL.

select * from logger;

Output:

 id | name  | login_date
----+-------+------------
  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
(6 rows)

Use the INTERVAL and Difference in Date Type in Postgres

Let’s say you want all the names along with id, who logged in the database and how many days spent till now, and you want to see only those who logged in the last 120 days.

Query SQL:

SELECT id, name, now() - login_date as time_spent from logger
WHERE login_date> (CURRENT_DATE - INTERVAL '120 days');

You can put hours, days, months, and years in the interval.

Output:

 id | name |       time_spent
----+------+-------------------------
  5 | Jene | 21 days 11:44:35.790685
  6 | Dan  | 54 days 11:44:35.790685
(2 rows)

Use the between in Date Type in Postgres

You can run the following SQL command if you want to see who logged in between 2021 and the current date.

Query SQL:

WHERE login_date between '2021-01-01' AND CURRENT_DATE;

Output:

 id | name | login_date
----+------+------------
  3 | Bon  | 2021-06-06
  5 | Jene | 2022-02-22
  6 | Dan  | 2022-01-20
(3 rows)

Here the format of date data type is YYYY-MM-DD. So, when you try to insert or write the query, make sure that you use the format supported by the Postgres database.

Here is the documentation for the date datatype format in Postgres.

Use the daterange in Date Type in Postgres

Suppose you want to see who logged in between the date range. For example, 2021-06-06 to 2022-03-10.

Do the following SQL command:

Query SQL:

SELECT *
FROM logger
WHERE '[2021-06-06, 2022-03-10]'::daterange @> login_date;

We used ::daterange, meaning we are typecasting the range to the date data type. The @> is called the range operator, which can also be used for other range queries of different data types.

Output:

 id | name | login_date
----+------+------------
  3 | Bon  | 2021-06-06
  5 | Jene | 2022-02-22
  6 | Dan  | 2022-01-20
(3 rows)

Also, you can use the CURRENT_DATE in the range as well. You need to remember that, inside the square bracket, the first date is the beginning date of the range and the second date is the ending date of the range.

For the ending date, you can also write infinity. To know more about date ranges, you can visit the following site here.