JIYIK CN >

In this article, we will discuss the different types of ranges in PostgreSQL.

Range types in PostgreSQL

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

Postgres has a daterangedate type to compare dates. Also, we can use betweento compare dates.

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

Once the table is populated, execute 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)

INTERVALDifferences between using and date types in Postgres

Let's say you want all the names and idwho logged into the database and how many days it took so far, and you want to see only the people who logged in 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)

Used in Postgres date typesbetween

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

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)

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

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

Used in Postgres date typesdaterange

Let's say you want to see users who logged in between a date range. For example, June 6, 2021 to March 10, 2022.

Execute the following SQL command:

Query SQL:

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

We used ::daterange, which means we converted the range type to date data type. @>It is called range operator and it can also be used for other range queries on 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)

Additionally, you can use it in ranges CURRENT_DATE. You just need to remember that within the square brackets, the first date is the start date of the range and the second date is the end date of the range.

For the end date you can write that as well infinity.