How to find date difference in PostgreSQL

In this PostgreSQL tutorial, I will teach you how to find the date difference in PostgreSQL. You will learn how to calculate the difference between two dates using the DATE_PART(), EXTRACT(), and AGE () functions.

How to find date difference in PostgreSQL

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

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

Let’s examine some examples and determine the date differences.

Approach 1: Using Extract()

The EXTRACT() function retrieves the minutes, seconds, dates, years, months, and other components 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 accepts two values: the source, also called the date and time value, and the second is the field name, such as year, month, 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 years’ differences between the two dates.

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

After executing the above query, I got the expected output as shown in the screenshot below.

If you examine the above statement, the EXTRACT() function extracts the YEAR from both dates. Then, using the minus (-) operator between the extracted years, the year difference is computed, which is 4 in this case, as shown in the output.

Approach 2: Using DATE_PART()

To extract sub-fields such as minute, year, second, day, hour, and so on, from the given date and time, use the DATE_PART() function. Here, you will learn how to use the DATE_PART() function to calculate 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, month, etc, and the second value is the source, which is the date and time value from which a specific field is extracted.

Check the examples below 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;

After executing the above query, I got the expected output as shown in the screenshot below.

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 the 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 the 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 when multiplied by 12, the result is 12 * 1 = 12.
• And this part (DATE_PART(‘MONTH’, DATE ‘2023-01-01’) – DATE_PART(‘MONTH’, DATE ‘2022-10-02’)) yields a month of -9. Then, this -9 is added to 12, which equals 12 – 9 = 3.

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

Approach 3: 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, etc.

The syntax is given below.

AGE(first_timestmap, second_timestamp)

The AGE() function takes two timestamps as input to calculate the age difference between them.

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

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

After executing the above query, I got the expected output as shown in the screenshot below.

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

Approach 4: Using Simple Subtraction for Day Differences

The most straightforward approach to finding the difference between two dates in PostgreSQL is to use the subtraction operator. When you subtract one date from another, PostgreSQL returns the difference in days.

SELECT '2023-12-31'::date - '2023-01-01'::date AS days_difference;

I got the expected output after executing the above query, as shown in the screenshot below.

Comparison of Date Difference Methods

Here’s a comparison table of the different methods:

Method	Best For	Pros	Cons
Date Subtraction	Simple day counting	Easy to use, efficient	Only provides days
AGE Function	Complete interval breakdown	Provides years, months, days	More complex to parse specific units
DATE_PART	Specific unit differences	Flexible, can target specific units	May not account for full date context
EXTRACT	Specific unit differences	Clear syntax, targeted extraction	Similar limitations to DATE_PART
Custom Functions	Business days, complex logic	Highly customizable	Requires more coding, may be less efficient
EPOCH Extraction	Precise time differences	Works with timestamps, can convert to any unit	More complex syntax

Performance Considerations

When working with large datasets, performance becomes crucial. Here are some performance tips:

• Indexing: Ensure your date columns are indexed adequately if you’re filtering or joining based on them.
• Materialized Views: For complex date calculations that are used frequently, consider creating materialized views that pre-calculate these values.
• Simple vs. Complex: Simple date subtraction (date1 – date2) is generally more efficient than functions like AGE() for large datasets.
• Batch Processing: For very large tables, consider processing date calculations in batches rather than all at once.

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.

You may also like:

• PostgreSQL Date Part Hour
• PostgreSQL date comparison
• PostgreSQL date to string