PostgreSQL Compare Date

Definition of PostgreSQL Compare Date

PostgreSQL compare date is used to compare date between two different dates which we have used as an input. We can compare the date by using where and between clauses, we can also compare the date using date_trunc function in PostgreSQL. We can also compare the date with timestamp by using the where clause, where clause is very important while comparing date in PostgreSQL.We have to compare date using select and update query using two different dates, after comparing the result it will display the result using select query and update query it will update the rows.

How to Compare Date in PostgreSQL?

• The below example shows how to compare the date.
• Where and between clause is useful when we have to compare date in PostgreSQL.
• We have using the stud_cmp table to describe the example of compare date in PostgreSQL is as follows.
• Below is the table and data description of the stud_cmp table.

select * from stud_cmp;
\d+ stud_cmp;

1. Compare Date by Using Where Clause

• We can compare date by using where clause. The below example shows that compare the two date columns by using the where clause.

Using Select Operations

• In the below example, we have using select operation on the stud_cmp table to retrieve data by comparing two dates.
• We have to compare the start_date and end_date column to compare the two date.
• We have used the AND clause with where clause to compare the two dates in PostgreSQL are as follows.

select * from stud_cmp where start_date = '2020-01-01' and end_date = '2020-01-02';

• In the above example after comparing the start date and end date result will display the three records which contains the comparison between the ‘2020-01-01’ and ‘2020-01-02’;
• In the Below example, we have compared the date interval and date functions.

SELECT * FROM stud_cmp WHERE start_date >= '2020-01-01'::date AND end_date < ('2020-02-01'::date + '1 day'::interval);

Using Update Operations

• We have compared the date in update operations. In the below example, we have compared the date in update operations.
• We have to compare the start_date and end_date column to update the id of 11 for the date between ‘2020-01-01’ and ‘2020-01-02’;
• We have used start_date and end_date column for compare date and update the id between those dates.
• We have used AND clause with where clause to update the ID of those dates which was compared using two columns.

update stud_cmp set id = 11 where start_date = '2020-01-01' and end_date = '2020-01-02';
select * from stud_cmp where id = 11;

2. Compare Date by Using Between Clause

• We can compare the date in PostgreSQL by using between clause. The below example shows that compare the two date columns by using the between clause.
• Using between clause we can compare the date of a single column. We have used a single column to compare the date using a clause in PostgreSQL.

Using Select Operations

• In the below example, we have using select operation on the stud_cmp table to retrieve data by comparing two dates using the between clause.
• We have to compare the start_date column to compare the data and retrieve the result.
• We have usedAND clause with between and where clause to compare the two date in PostgreSQL is as follows.

select * from stud_cmp where start_date between '2020-01-01' and '2020-01-02';

• In the above example after comparing start date will display the six records which contains the comparison between the ‘2020-01-01’ and ‘2020-01-02’;
• In the Below example, we have compared the date interval and date functions by using between clause.

SELECT * FROM stud_cmp WHERE start_date between '2020-01-01'::date AND ('2020-02-01'::date + '1 day'::interval);

Using Update Operations

• We have compared the date in update operations by using between clause. In the below example, we have compared the date in update operations by using between clause.
• We have to compare the start_date column to update the id of 12 for the date between ‘2020-01-01’ and ‘2020-01-02’;
• We have used AND clause with between and where clause to update the ID of those dates which was compared using two columns.

update stud_cmp set id = 12 where start_date between '2020-01-01' and '2020-01-02';
select * from stud_cmp;

3. Compare the date by using date_trunc function

• We can compare the date by using the date_trunc function in PostgreSQL.

Using Select Operations

• In the below example, we have using select operation on the stud_cmp table to retrieve data by comparing two dates using the date_trunc function.
• We have used the date_trunc function with where clause to compare the date in PostgreSQL is as follows.

SELECT * FROM stud_cmp WHERE DATE_TRUNC('day', start_date) = '2020-01-01'::timestamp;

• In the above example after comparing the start date and with date_trunc functions it will display the three records which contains the comparison between the ‘2020-01-01’ and timestamp;

Using Update Operations

• We have compared the date in update operations by using date_trunc functions. In the below example, we have compared the date in update operations by using date_trunc function.
• We have used where clause with date_trunc function to update the ID of those dates which was compared using date_trunc functions.

update stud_cmp set id = 13 WHERE DATE_TRUNC('day', start_date) = '2020-01-01'::timestamp;
select * from stud_cmp;

Recommended Articles

This is a guide to PostgreSQL Compare Date. Here we also discuss the definition and how to compare date in postgresql along with examples and its code implementation. You may also have a look at the following articles to learn more –

1. PostgreSQL REINDEX
2. Array in PostgreSQL
3. PostgreSQL ARRAY_AGG()
4. PostgreSQL CTE