Postgresql date comparison

In this PostgreSQL tutorial, we will learn about “Postgresql date comparison“. And we will also cover the following topics.

• How can we compare dates in Postgresql?
• Compare date with the help of WHERE clause
• Compare date with the help of BETWEEN clause

In PostgreSQL, we can compare the date between two different dates, which we will use as an input, and we will compare the date by using WHERE and BETWEEN clauses.

We can also compare the date using the DATE_TRUNC function in PostgreSQL. We can also compare the date with TIMESTAMP by using the where clause, WHERE clause is necessary while comparing the date in PostgreSQL.

Also, Read: PostgreSQL Date Difference

How can we compare dates in PostgreSQL?

• The below example shows how can we compare the dates.
• The WHERE and BETWEEN clause is very useful when we need to compare the date in PostgreSQL.
• We are going to use the journey table to describe the example of compare date in PostgreSQL is as follows.
• Below is the data description of the journey table.

Read: Postgresql difference between two timestamps

Compare date with the help of WHERE clause

Now, in this section, we will illustrate this implementation using SELECT as well as the UPDATE clause.

Using the SELECT clause:

SELECT * FROM journey WHERE arrival = '2017-05-06 07:30:00' and departure = '2017-02-24 12:00:00';

In the above code, we searching the name of the person using the arrival and departure column of the table named journey

After comparing the of the above column arrival, departure with the date and time we get the name of person Dan.

Let’s know the name of the person whose arrival time is greater than the date 2019-07-14 20:40:30

SELECT * FROM journey WHERE arrival >= '2019-07-14 20:40:30';

In the above output, George, Jeff, John has arrival times greater than or equal to 2019-07-14 20:40:30.

Again, know the name of the person whose departure time is less than or equal to the date 2019-07-14 16:15:00.

SELECT * FROM journey WHERE departure <= '2019-07-14 16:15:00';

In the above output, Dan, Jeff, Jhony has departure time less than or equal to the date 2019-07-14 16:15:00.

Using the UPDATE clause:

We are going to compare the date in UPDATE operation.

UPDATE journey SET name = 'Jhony' WHERE arrival = '2020-01-08 14:00:00' and departure = '2019-01-05 08:35:00';
SELECT * FROM journey WHERE name='Jhony'

In the above code, we are updating the name of the person whose arrival and departure time is ‘2020-01-08 14:00:00’ and ‘2019-01-05 08:35:00’ respectively.

The output of the above code is given below.

In the above output, name update from Jhon to Jhony.

Read: PostgreSQL Date Add + Add days to date in PostgreSQL

Compare date with the help of BETWEEN clause

Also, in this section, we will illustrate this implementation using SELECT as well as the UPDATE clause.

Using the SELECT clause:

SELECT * FROM journey WHERE departure BETWEEN '2017-02-24 12:00:00' AND '2019-09-12 15:50:00';

In the above code, we fetching records that have departure time between ‘2017-02-24 12:00:00’ AND ‘2019-09-12 15:50:00’.

The output of the above code is given below.

In the above output, Dan, Jeff, Jhony has departure time between ‘2017-02-24 12:00:00′ AND ‘2019-09-12 15:50:00’.

Using the UPDATE clause:

We will update the id of the person using the UPDATE clause by comparing two dates.

UPDATE journey SET name = 'Lucifer' WHERE arrival BETWEEN '2019-01-05 08:35:00'  and  '2020-01-08 14:00:00';
SELECT * FROM journey;

In the above code, we are updating the name of the person to Lucifer whose arrival time is between ‘2019-01-05 08:35:00’ and ‘2020-01-08 14:00:00’.

The output of the above code is given below.

In the above output, the person’s name is updated to Lucifer.

Also, take a look at some of the other tutorials on PostgreSQL.

• PostgreSQL CREATE INDEX
• Postgresql if else
• Postgresql Sum
• Postgres date range
• Postgresql import SQL file
• Postgresql generate_series
• Postgresql date to string
• PostgreSQL Update
• Postgresql cast int
• PostgreSQL Subquery with Examples

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

• How can we compare dates in Postgresql?
• Compare date with the help of WHERE clause
• Compare date with the help of BETWEEN clause