Postgres date range

Recently, I got a requirement to work with Postgres date range functionality. I have identified a few key approaches to achieve this functionality. This PostgreSQL tutorial teaches us about the “Postgres date range” and covers the following topics.

Postgres date range

Let us understand this topic deeply with an example.

In PostgreSQL, create a table named employee that we will use in this tutorial.

CREATE TABLE employee(emp_no INT, birth_date DATE,first_name VARCHAR,
		last_name VARCHAR,gender VARCHAR,hire_date DATE);

Download the CSV file and insert the records using the code below.

COPY employee FROM 'D:/employee.csv' DELIMITER ',' CSV HEADER;

You can also check out an article on how to import CSV file into PostgreSQL.

After inserting the records into the PostgreSQL table, you can view the data by running the following command.

SELECT * FROM employee;

Example 1: Using daterange data type

In PostgreSQL, we can write a query on a range of dates using a date range data type, which represents the range of dates.

Let’s run the below query to find out the names of the employees whose hired dates ranged between 1992-03-21 and 1994-06-25.

SELECT first_name
FROM employee
WHERE '[1992-03-21, 1994-06-25]'::daterange @> hire_date

In the above code, we are fetching the names of employees whose hire dates are between 1992-03-21 and 1994-06-25.

In the WHERE clause, dates within the brackets are cast to the daterange datatype using ‘::daterange’,

Where (::) is a typecast operator that can cast any value to any data type in PostgreSQL,

@> (called contains element) It is the range operator for the date range or other range types.

The output of the above code is given below.

Read: PostgreSQL date comparison

Example 2: Using overlaps operator

In PostgreSQL, we can check whether two dates overlap using the operator OVERLAPS that returns TRUE if dates overlap, otherwise returns FALSE.

Let’s understand this through an example by running the query below.

SELECT (DATE '1985-11-21', DATE '1991-09-01')
OVERLAPS 
(DATE '1985-11-21', DATE '1991-09-01');

After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, the query returns true, which means both dates overlap.

Also, check: Postgresql date to string

Example 3: Using the date range data type

In PostgreSQL, Range types are data types that represent a range of values of some element type.

There are many different range types in PostgreSQL, and daterange is one of the types that represent the range of dates.

Let’s view the records of employees whose hire date range between 1985-11-21 and 1989-06-02.

SELECT *
FROM employee
WHERE '[1985-11-21, 1989-06-02]'::daterange @> hire_date

Check out the screenshot below, showing that I got the expected output after executing the above query.

The above output shows all the records whose hire date is between 1985-11-21 and 1989-06-02.

Read: PostgreSQL list users

Example 4: Using the date range functions

There is no built-in function in PostgreSQL to show a range of dates, but we will create our function.

Let’s create a function named daterange that accepts two parameters: a string or a varchar data type.

CREATE FUNCTION daterange(x varchar,y varchar)
RETURNS TABLE(date_range date) AS
$$
BEGIN
  RETURN QUERY SELECT generate_series(x::date, y::date, '1 day')::date AS date_range;
  
 END;
$$LANGUAGE plpgsql;

Let’s call the daterange function with two parameters ‘2020-01-01’ and ‘2021-01-01’, the first parameter is the starting date and the second is the ending date.

SELECT daterange('2020-01-01','2021-01-01');

The output of the above code is given below.

As we can see in the above output, the range of dates is generated using the daterange function.

Read: Postgresql difference between two timestamps

Example 5: Using the between operator

In PostgreSQL, we can find the date range between two timestamps using the BETWEEN clause.

In this section, we will use the table journey, which is described below.

Let’s see the arrival and departure of the person using the query below.

SELECT * FROM journey 
WHERE arrival 
BETWEEN '2017-05-06 07:30:00' AND '2019-07-14 20:40:30';

In the above code, the arrival or departure of a person should be checked between the date timestamp range of ‘2017-05-06 07:30:00’ and ‘2019-07-14 20:40:30’.

The output of the above code is given below.

Read: PostgreSQL Date Difference

Example 6: Using infinity

In PostgreSQL, we can return the date range from a specific date to infinity using daterange.

Let’s return the name of the person whose hiring date is from a specific date to infinity.

SELECT first_name
FROM employee
WHERE '[1994-06-25, infinity]'::daterange @> hire_date

The above code will show the first_name of the person whose hiring date is from 1994-06-25 to infinity using ‘[1994-06-25, infinity]’.

The output of the above code is given below.

In this tutorial, we have learned about the “Postgres date range” with multiple real-time examples.

You may also like to read the following articles.

• PostgreSQL Cast Timestamp To Date
• PostgreSQL list databases
• PostgreSQL Data Types
• PostgreSQL Date Add
• Postgresql group_concat
• Postgresql row_number