Postgres date range + Examples

In this PostgreSQL tutorial, we learn about the “Postgres date range” and cover the following topics.

• Postgres date range query
• Postgres date range overlap
• Postgres date range type
• Postgres date range functions
• Postgres date range timestamp and between date range
• Postgres date range infinity
• Postgres generate date range

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 below code.

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 below command.

SELECT * FROM employee;

If the output looks as above output, then data is inserted successfully into the PostgreSQL table.

Postgres date range query

In PostgreSQL, we can write a query on a range of dates using a daterange data type and it represents the range of dates.

Let’ run the below query to know the name of the employees whose hired date range between 1992-03-21, 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 name of employees whose hire date between 1992-03-21, 1994-06-25.

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

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

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

The output of the above code is given below.

Read: Postgresql date comparison

Postgres date range overlap

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

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

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

In the above output, the query returns true, it means both the date overlap.

Also, Check: Postgresql date to string

Postgres date range 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 date.

Let’ 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

In the above output, it shows that all the records whose hire date between 1985-11-21 and 1989-06-02.

Read: PostgreSQL list users

Postgres date range functions

In Postgresql, there is no inbuilt function for showing a range of dates, but we will create our own function to show the range of dates.

Let’s create a function named daterange that accepts two parameters in the form of string or 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’ ‘2021-01-01’, the first parameter is starting date and the second is 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

Postgres date range timestamp and between date range

In Postgresql, we can find the date range between the timestamp using the BETWEEN clause.

In this section, we will use the table named journey, and a description of the table is given below.

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

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

In the above code, checking the arrival or departure of a person 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

Postgres date range 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 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 dater from 1994-06-25 to infinity using ‘[1994-06-25, infinity]’.

The output of the above code is given below.

Postgres generate date range

In Postgresql, the range of date can be generated using the generate_series function that accepts three-argument start, stop, step.

Let’s generate the series of dates.

SELECT generate_series(date'2020-03-07', '2030-08-16', '1 day')::date AS date_range;

In the above code, we are generating a range of dates from start (‘2020-03-07’) to stop (‘2030-08-16’) with the step of ‘1 day’.

The output of the above code is given below.

You may also like to read the following articles.

• PostgreSQL list databases
• PostgreSQL Data Types
• PostgreSQL Date Add
• Postgresql group_concat
• Postgresql row_number

So, in this tutorial, we have learned about the “Postgres date range“, and we have also covered the following topics.

• Postgres date range query
• Postgres date range overlap
• Postgres date range index
• Postgres date range type
• Postgres date range functions
• Postgres date range timestamp and between date range
• Postgres date range infinity
• Postgres generate date range