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 datediff
 

PostgreSQL datediff

Updated May 24, 2023

PostgreSQL datediff

 

 

Introduction to PostgreSQL datediff

PostgreSQL provides a datediff function to users. The datediff means we can return the difference between two dates based on their specified interval. The datediff Function plays an important role in the database management system because datediff functions as a calendar and is very helpful to users. With the help of the datediff Function, we can determine any previous date or next date similarly, we can determine the previous day and next day, and we can also determine time, week, and minutes. Some functions of datediff use the system’s current date as per query syntax.

Watch our Demo Courses and Videos

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

Syntax:

select datediff function() date1,  interval date2;

Explanation:

  • In the above syntax, we use a select clause where the datediff Function means various date-related functions, where date1 is the first specified date, and date2 is the second specified date.
  • Here interval is used to determine the difference between the specified dates.

How datediff Function works in PostgreSQL?

  • We must install PostgreSQL in our system.
  • Require basic knowledge of PostgreSQL.
  • We must require the database to apply the datediff Function.

Given below are the different datediff Functions as follows:

First, we see basic functions related to date and time.

1. now() function

This Function is used to know the current date and time of the system, and this Function is a system defined Function, it gives a result in timestamp.

Example:

Code:

select now();

Output:

PostgreSQL datediff 1

2. Time of day function

This Function shows the date and time in text format.

Example:

Code:

Select timeofday();

Output:

PostgreSQL datediff 3

3. Quarter function

This Function is used to know a quarter of a year.

Example:

Code:

select     extract      (quarter       from        now());

Explanation:

  • In this example, we extract a quarter of the current year.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 3

4. Day of the year

This Function calculates the number of days in a single year.

Code:

select     extract     (doy     from      now());

Output:

PostgreSQL datediff op 5

Explanation:

  • In this example, we use the extract function to know the number of days in a year specific in this example, we use now a function that means it counts days from the system’s current date.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

5. Day of the week

This Function is used to extract days from the week.

Example:

Code:

Select    extract    (dow      from    now());

Explanation:

  • In this example, extract the current day in the week it takes the system’s current date to know the day it starts from Monday as 1, Tuesday as 2, and so on. Thus by default, the value of Sunday is 0.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 6

6. Current month

This Function is used to know the current month.

Example:

Code:

select        extract       (month     from      now());

Explanation:

  • In this example, we extract the current month, where the now() Function takes the system’s current date.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 7

Apart from datediff and extract function, we have another function which we call the date_part Function working of this Function the same as the datediff Function. The datediff Function is not directly supported by PostgreSQL.

Example:

Code:

Select    date_part       ('dow',    now());

Explanation:

  • In this example, we use the date_part Function instead of extract. Similarly, we implement all the above functions by using the date_part Function.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 8

Now we see the example of the main datediff Function:

Example #1: Difference between two years

Code:

select date_part  ('year', '2020/01/01'::date) -    date_part     ('year',     '2010/01/01'::date);

Explanation:

  • Suppose we need to know the difference between two dates at that time, we use the above statement, see here, we use date_part instead of datediff because PostgreSQL does not support it directly.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 9

Example #2: Difference between two days

Code:

Select         '2020-07-28 22:00:00'         ::          timestamp      -        '2020-07-26 02:00:00'           ::            timestamp;

Explanation:

  • Suppose we need to determine the difference between two days at that time, we use the above statement.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL datediff op 10

Example #3: Difference between the two week

Code:

Select       trunk     (date_part     ('day',    '2020-07-28'   ::    timestamp     -    '2020-07-21'  ::          timestamp)        /     7);

Explanation:

  • Suppose we need to know the difference between the two weeks when we use the above statement.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Difference between the two week

Example #4: Difference between hours

Code:

select date_part('day','2020-07-28 07:55'::timestamp - '2020-07-28 08:05'::timestamp)
* 24+
date_part('hour','2020-07-28 07:55'::timestamp - '2020-07-28 08:05'::timestamp);

Explanation:

  • Suppose we need to know the difference between two hours at that time. We use the above statement.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Difference between hours

Example #5: Difference between minutes

Code:

select          date_part          ('hour',      '09:40:10'::time     -        '09:45:50'::time)    *60+
date_part    ('minute',          '09:40:10'::time -                              '09:45:50'::time);

Explanation:            

  • Suppose we need to know the difference between minutes at that time, we use the above statement.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Difference between minutes

Example #6: Difference between seconds

Code:

select   date_part        ('hour','09:40:10'::time - '09:40:50'::time)*60+
date_part           ('minute','09:40:10'::time - '09:40:50'::time)*60+
date_part        ('second','09:40:10'::time - '09:40:50'::time);

Explanation:

  • Suppose we need to know the difference between the second at that time we use the above statement.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Difference between Seconds

Conclusion

From the above article, we saw the syntax of datediff and basic functions of datediff as well as some advanced functions like determine the difference between year, month, day, hour, and minutes with multiple examples. From this article, we saw how we could handle different datediff functions.

Recommended Articles

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

  1. PostgreSQL DROP DATABASE
  2. Replication in PostgreSQL
  3. PostgreSQL Stored Procedures
  4. PostgreSQL ORDER BY Random

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