How to Query Between Date Ranges in PostgreSQL

1. Date Ranges in PostgreSQL
2. Use the Interval and Difference in Date to Query Between Date Ranges in PostgreSQL
3. Use between to Query Between Date Ranges in PostgreSQL
4. Use the daterange Type to Query Between Date Ranges in PostgreSQL

This article will discuss the different types of ranges to compare the dates in PostgreSQL.

Date Ranges in PostgreSQL

By default, PostgreSQL provides some ranges to compare the values. Particularly, we can use daterange and between to compare dates.

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

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

Output:

postgres=# select * from logger;
 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 to Query Between Date Ranges in PostgreSQL

Suppose you want all names (with the id) of those who logged in to the database and the days they spent until today. You want to see only those who logged in the last 120 days.

Example code:

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

Here, 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 between to Query Between Date Ranges in PostgreSQL

You can run the SQL command below to see who logged in between 2021 and the current date.

postgres-# WHERE login_date between '2021-01-01' AND CURRENT_DATE;
 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 the 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 PostgreSQL database.

Here is the documentation for the date data type format in PostgreSQL.

Use the daterange Type to Query Between Date Ranges in PostgreSQL

Now, suppose you want to see who logged in between the date range - for example, 2021-06-06 to 2022-03-10. Let’s create the query.

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

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

Output:

postgres-# WHERE '[2021-06-06, 2022-03-10]'::daterange @> login_date;
 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. Remember that, inside the square bracket, the first is the beginning date, and the second is the ending date of the range.

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