EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Compare Date
 

PostgreSQL Compare Date

Priya Pedamkar
Article byPriya Pedamkar

Updated May 15, 2023

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 and the date using the date_trunc function in PostgreSQL. Where clauses are crucial when comparing dates in PostgreSQL and can be used to compare dates with timestamps. We have to compare the date using the select and update query using two different dates; after comparing the result, it will display the result using the select query and update query. It will update the rows.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 used the stud_cmp table to describe the example of compare date in PostgreSQL.
  • Below is the table and data description of the stud_cmp table.
select * from stud_cmp;
\d+ stud_cmp;

PostgreSQL Compare Date-1.1

1. Compare Date by Using the Where Clause

  • We can compare date by using the where clause. The example below shows that compare the two date columns using the where clause.
Using Select Operations
  • In the example below, we use the select operation on the stud_cmp table to retrieve data by comparing two dates.
  • We must compare the start_date and end_date columns to compare the two dates.
  • We have used the AND clause to compare the two dates in PostgreSQL as follows.
select * from stud_cmp where start_date = '2020-01-01' and end_date = '2020-01-02';

PostgreSQL Compare Date-1.2

  • In the above example, after comparing the start date and end date result will display the three records which contain the comparison between the ‘2020-01-01’ and ‘2020-01-02’;
  • We have compared the date interval and date functions in the below example.
SELECT * FROM stud_cmp WHERE start_date >= '2020-01-01'::date AND end_date < ('2020-02-01'::date + '1 day'::interval);

PostgreSQL Compare Date-1.3

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 the start_date and end_date column to compare date and update the id between those dates.
  • To update the ID of those dates, which were compared using two columns, we utilised the AND clause with the where clause.
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;

PostgreSQL Compare Date-1.4

2. Compare Date by Using Between Clause

  • We can compare the date in PostgreSQL by using between clause. The example below shows that compare the two date columns 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 example below, we use the 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 between and where the clause to compare the two dates in PostgreSQL.
select * from stud_cmp where start_date between '2020-01-01' and '2020-01-02';

PostgreSQL Compare Date-3.1

  • In the above example, after comparing the start date will display the six records which contain 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);

PostgreSQL Compare Date-3.2

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;

Output-3.3

3. Compare the date by using the date_trunc function

  • We can compare the date by using the date_trunc function in PostgreSQL.
Using Select Operations
  • In the example below, we use the 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 the where clause to compare the date in PostgreSQL as follows.
SELECT * FROM stud_cmp WHERE DATE_TRUNC('day', start_date) = '2020-01-01'::timestamp;

Output-4.1

  • In the above example, after comparing the start date and with date_trunc functions, it will display the three records which contain 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 example below, we have compared the date in update operations using the date_trunc function.
  • We have used the where clause with the 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;

Output-4.2

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Compare Date” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW