JIYIK CN >

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

Date ranges in PostgreSQL

By default, PostgreSQL provides some ranges to compare values. In particular, we can use daterangeand betweento compare dates.

To demonstrate, 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)

Query date range using date intervals and differences in PostgreSQL

Let's say you want all the names (with id) of people who have logged into the database and the number of days they have logged in till today. You only want to see users who have logged in in the last 120 days.

Sample code:

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

Here you can enter 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)

betweenQuerying date ranges in PostgreSQL using

You can run the following SQL command 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 date data type is YYYY-MM-DD. So when you try to insert or write query, make sure you use the format supported by PostgreSQL database.

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

daterangeQuerying date ranges using the type in PostgreSQL

Now, suppose you want to see users who logged in between a date range, such as 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 use ::daterange, which means we convert the range type to date data type. @>It is called range operator and can be used for range queries on other different data types as well.

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)

Additionally, you can use it in ranges as well CURRENT_DATE. Remember, inside the square brackets, the first one is the start date and the second one is the end date of the range.

For the end date, you can also write infinity. You can visit the website here to learn more about date ranges.