PostgreSQL date between two dates

In this PostgreSQL tutorial, I’ll guide you through multiple approaches to filtering data between two dates in PostgreSQL, complete with practical examples and performance considerations.

PostgreSQL date between two dates

Why Date Range Filtering Matters in PostgreSQL

Before diving into the syntax, let’s understand why effective date filtering is crucial:

• Performance optimization: Properly indexed date filters can dramatically improve query speed
• Data analysis: Time-based analysis requires precise date range filtering
• Reporting accuracy: Financial and compliance reports often require exact date boundaries
• User experience: Applications frequently need to display time-bound data

Now, let’s explore the various methods for querying data within date ranges in PostgreSQL.

So first, create an employee table and insert some data into it.

CREATE TABLE employee(id SERIAL,name VARCHAR(30),start_date DATE,end_date DATE);

Insert the following data.

INSERT INTO employee(name,start_date,end_date)VALUES('Dorthy Hamill','2005-02-26','2016-08-01'),
('Jerrold Moore DVM','1999-05-03','2012-07-06'),('Blaise Schowalter MD','2008-04-05','2017-09-02'),
('Garfield Hayes','2001-08-20','2014-05-01'),('Jalen Bogisich','2004-02-26','2013-08-15'),
('Decker','2008-03-27','2018-06-01'),('Amenadiel','2003-10-22','2019-01-05'),
('Charlie','2000-02-25','2016-05-01'),('Adam','2002-11-08','2019-10-11'),
('Dan','1998-09-26','2015-03-16'),('Trixie','2009-06-29','2019-12-30'),
('Lucifer','1995-12-01','2020-08-01'),('Linda','2000-02-26','2016-12-20'),
('Mazekin','2005-07-19','2018-07-010'),('Kinley','2006-02-17','2013-05-01'),
('Pierce','2015-01-01','2020-09-01'),('Dormos','2010-08-29','2020-10-01'),
('Azriel','1999-03-18','2017-07-01'),('Chloe','2001-11-24','2014-09-01');


SELECT * FROM employee;

After executing the above queries, we got the expected output as shown in the screenshot below.

Approach 1: Using the BETWEEN operator

The most straightforward approach to filter records between two dates is to use PostgreSQL’s BETWEEN operator. This operator is inclusive, meaning it returns records that match both the start and end dates.

Example 1

Let’s show the date between two dates using an example.

SELECT name,start_date FROM employee
WHERE start_date BETWEEN '2000-01-01' AND '2006-01-01';

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

In the above output, we have shown the number of employees who started a job between January 1, 2000, and January 1, 2006. As a result, we found that there are 9 employees.

Read: How to create a table in PostgreSQL

Example 2

In PostgreSQL, we can extract the date between two dates inclusively using the BETWEEN clause.

Let’s understand through an example.

SELECT name,end_date as left_date FROM employee
WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01';

In the above code, the BETWEEN clause will display the names of employees who left the company on the specified date, inclusive, meaning up to and including the date specified in the BETWEEN clause, such as up to ‘2016-08-01’.

In other words, it will show the name of the employee who left the company from the date ‘1998-01-07 to exactly on the date ‘2016-08-01’.

Check out the screenshot below, we got the expected output after executing the above query.

In the above output, Dorthy Hamil left the company on 2016-08-01 because this query is inclusive.

Read: PostgreSQL WHERE

Approach 2: Using Comparison Operators

In PostgreSQL, we show the date between two dates as a timestamp. Let’s understand through an example.

Example

Create a table named timestp.

CREATE TABLE timestp(start_timestamp timestamp, end_timestamp timestamp);

Insert the following data.

INSERT INTO timestp(start_timestamp,end_timestamp)VALUES('2000-04-26 01:05:40','2004-04-15 19:03:09'),
('2012-04-15 14:50:10','2029-10-02 10:58:50'),('2017-07-17 06:44:34','2018-09-15 17:45:36'),
('2018-12-21 04:54:12','2026-05-31 00:10:05'),('2012-03-08 17:59:41','2015-08-29 12:42:28');

Let’s calculate the date between two timestamps.

SELECT end_timestamp FROM timestp 
WHERE end_timestamp >= '2004-01-01' AND end_timestamp <= '2027-12-30';

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

In the above output, we have calculated timestamps between ‘2004-01-01’ and ‘2027-12-30’, and it shows four timestamps.

Read: PostgreSQL CASE

Approach 3: Using the minus (-) operator

In PostgreSQL, we can also calculate the difference between two timestamps using the minus (-) operator.

Example

Let’s understand through an example.

SELECT (end_timestamp - start_timestamp) as date_diff FROM timestp;

From the above code, we subtract the start_timestamp from the end_timestamp to calculate the date difference.

In the above output, we have calculated the date difference between two date timestamps, including the number of days and hours between them.

Read: PostgreSQL WHERE IN

Approach 4: Using the random() and now() functions.

In PostgreSQL, we can generate random dates between two dates using the random() and now() functions.

• random( ) : It is the random function that returns a value between 0 (inclusive) and 1 (exclusive), so value >= 0 and value < 1.
• now(): The PostgreSQL now function returns the current date and time, including the time zone.

Example

Let’s understand with an example.

SELECT NOW() + (random() * (NOW()+'100 days' - NOW())) + '20 days';

If we run the above query, it displays a random date calculated by multiplying the current date by 100 days, subtracting 20 days from the current date, and then adding the result, which creates a 100-day time window that we want, along with the 20 days to extend the time window.

Every time, it shows a random date when we run the above query.

Read: PostgreSQL DATE Format

Approach 5: Using the generate_series function

In PostgreSQL, we can generate a date between two dates using the generate_series function.

generate_series(start, end, step): It is a function that generates a set of data from the starting point to the ending point with an increment step.

Example

Let’s generate a date.

SELECT generate_series(date'2010-01-20',date'2010-02-04','1 day');

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

In the above output, a series of dates is generated from ‘2010-01-20’ to ‘2010-02-04’ with an interval of 1 day.

Conclusion

After working with PostgreSQL date filtering in numerous projects, I have concluded that understanding these different approaches is essential for building efficient and accurate database queries. The method you choose depends on your specific requirements:

• Use BETWEEN for simple, inclusive date ranges
• Use comparison operators (<, <=, >, >=) for more precise control
• Use DATE_TRUNC for period-based filtering
• Use range types for complex overlap scenarios
• Use time zone conversion when dealing with global data

You may also like some of our tutorials on PostgreSQL.

• PostgreSQL ADD COLUMN
• PostgreSQL DATE Functions
• PostgreSQL ALTER TABLE
• PostgreSQL INSERT INTO table
• PostgreSQL DROP COLUMN
• Postgresql date add
• PostgreSQL Data Types
• PostgreSQL list databases
• PostgreSQL Loop Examples