How to Find PostgreSQL DateDiff

In this PostgreSQL tutorial, I will teach how to find PostgreSQL DateDiff. You will understand how to find the difference between two dates using the functions DATE_PART(), and EXTRACT().

Also, I will show how to use the function AGE() to compute the date difference between two dates which returns the date difference as an interval form.

Table of Contents

How to Find PostgreSQL DateDiff

There is a function called DATEDIFF in other database software such as Oracle, SQL and etc, to compute the difference between the two dates. But this kind of function doesn’t exist in PostgreSQL.

To get the same functionality as DATEDIFF, you can use the minus (-) operator with different functions such as EXTRACT(), DATE_PART() and etc, to find the difference between the two dates.

Let’s check some of the examples and find the date differences.

How to Find PostgreSQL DateDiff using Extract()

The EXTRACT() function gets the minutes, seconds, dates, years, months and etc, from the given date and time or timestamp value. In simple words, When you need to find the specific field of the given date and time value, you can use this function.

The syntax is given below.

EXTRACT(field_name FROM date_time_vaue)

The EXTRACT() function accept two value, the source also called the date and time value, and the second is field name like year, month and etc, that you want to extract from that date and time value.

Let’s see how to use the EXTRACT() to find the date differences.

Use the below statement to find the year’s differences between the two dates.

SELECT 
EXTRACT(YEAR FROM DATE '2023-11-25') - EXTRACT(YEAR FROM DATE '2019-11-25') AS DATEDIFF_YEAR;

If you look at the above statement, The EXTRACT() function extracted the YEAR from both dates, then using the minus (-) operator between the extracted YEAR, the year difference is computed which is 4 in this case as you can see in the output.

How to Find PostgreSQL DateDiff using DATE_PART()

To extract the sub-fields such as minute, year, second, day, hour and etc, from the given date and time, use the DATE_PART() function. So here you will see how to use the DATE_PART() function to find the difference between two dates.

The syntax is given below.

DATE_PART(field_name, date_time_value)

The DATE_PART() function takes two values, the field name which is the unit of the date and time such as year, day, months and etc, and the second value is the source which is the date and time value from which a specific field is extracted.

Check the below examples to find the datediff in months between the two dates.

SELECT (DATE_PART('YEAR', DATE '2023-01-01') - DATE_PART('YEAR', DATE '2022-10-02')) * 12 +
    (DATE_PART('MONTH', DATE '2023-01-01') - DATE_PART('MONTH', DATE '2022-10-02')) AS month_datediff;

In the above query to compute the months between two dates, the first (DATE_PART(‘YEAR’, DATE ‘2023-01-01’) – DATE_PART(‘YEAR’, DATE ‘2022-10-02’)) function extracts the year between two dates, and then returned year is multiplied by 12.

Then the second (DATE_PART(‘MONTH’, DATE ‘2023-01-01’) – DATE_PART(‘MONTH’, DATE ‘2022-10-02’)) function extracts the month between two dates and then adds the result of the first DATE_PART() function to the second result returned by second DATE_PART() function.

Let’s understand each part of the above query to know how months are calculated using the DATE_PART() function:

This part (DATE_PART(‘YEAR’, DATE ‘2023-01-01’) – DATE_PART(‘YEAR’, DATE ‘2022-10-02’)) returns the year as 1 and multiplied by 12 which is 12 * 1 = 12.
And this part (DATE_PART(‘MONTH’, DATE ‘2023-01-01’) – DATE_PART(‘MONTH’, DATE ‘2022-10-02’)) month as -9 then this -9 is added to 12 which is 12-9 =3.

So the months between two dates ‘2023-01-01‘ and ‘2022-10-02’ is 3.

How to Find PostgreSQL DateDiff using Age()

To find the ages between two timestamps, the AGE() function is used. The AGE() function returns the result as an interval something like this 1 year 2 months 3 days and etc.

The syntax is given below.

AGE(first_timestmap, second_timestamp)

The AGE() function two value as a timestamp to find the ages between the two timestamps.

Let’s take an example and find the datediff in Postgres using the below statement.

SELECT AGE('2023-01-01','2022-10-02');

In the above statement, the AGE() function returned the result as 2 mons 30 days between two dates ‘2023-01-01’ and ‘2022-10-02’. As you can see this function represented the date differences in a more meaningful way as compared to other functions that you have learned in the above topics.

Conclusion

In this PostgreSQL tutorial, you learned how to calculate the difference between two dates using the function EXTRACT() and DATE_PART() with the minus (-) operator. Additionally, you covered how to use the AGE() function to compute the date difference between two dates.