Postgresql date between two dates

In this Postgresql tutorial, we are going to cover “Postgresql date between two dates”. And we will also learn to extract or generate a date between two dates. Here is the list of topics that we are going to cover.

• Postgresql date between two dates inclusive
• Postgresql date between two dates timestamp
• Postgresql date difference between two timestamps
• Random date between two dates
• Generate date between two dates

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;

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';

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

Read: How to create a table in PostgreSQL

Postgresql date between two dates inclusive

In Postgresql, we can extract the date between two dates inclusively using 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, BETWEEN clause will show the name of employees who left the company on which date inclusively, means till the date that included in BETWEEN clause such as till 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’.

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

Read: PostgreSQL WHERE

Postgresql date between two dates timestamp

In Postgresql, we show the date between two dates timestamp, let’s understand through an 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';

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

Read: PostgreSQL CASE

Postgresql date difference between two timestamps

In Postgresql, we can also calculate the date between two timestamps using the minus (-) operator between two dates timestamps.

Let’s understand through an example.

SELECT (end_timestamp - start_timestamp) as date_diff FROM timestp;

From the above code, we are subtracting start_timestamp from end_timestamp to get the date difference.

In the above output, we have calculated the date difference between two dates timestamp like how many days between two dates timestamp with the hour.

Read: PostgreSQL WHERE IN

Random date between two dates

In Postgresql, we can generate random dates between two dates with help of two functions random( ) and now( ).

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

Let’s understand with an example.

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

If we run the above query it shows a random date of random multiplication of current date + 100 days minus current date plus 20 days plus current date, which is 100 days time window that we want and the 20 days to how far out to push the time window.

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

Read: PostgreSQL DATE Format

Generate date between two dates

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 starting point to the ending point with increment step.

Let’s generate a date.

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

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

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

So, in this tutorial, we have learned about “Postgresql date between two dates” and we have covered the following topics.

• Postgresql date between two dates inclusive
• Postgresql date between two dates timestamp
• Postgresql date difference between two timestamps
• Random date between two dates
• Generate date between two dates