PostgreSQL DATEDIFF Function

PostgreSQL doesn’t have a DATEDIFF function like some other databases, but you can still calculate the difference between dates using simple subtraction. This approach allows you to find out how many days, months, or years separate two dates.

In this article, we'll explore how to compute date differences in PostgreSQL, with clear examples to help you understand how to work with days, weeks, hours, minutes, and seconds.

PostgreSQL DATEDIFF Function

In PostgreSQL, there isn't a direct DATEDIFF function like in other databases. Instead, you can subtract dates or use the AGE() function to calculate differences in days, months, or years between two dates.

For more precise measurements, such as hours, minutes, or seconds, PostgreSQL offers the AGE function and interval arithmetic. These tools make it easy to handle various date and time calculations.

Examples of PostgreSQL Date Differences

In PostgreSQL, the AGE() function calculates the time interval between two dates, and EXTRACT(YEAR) retrieves the difference in years. This is useful for determining the number of complete years between two specific dates, like calculating someone's age or the duration between events.

1. Difference in Years

• The query SELECT EXTRACT(YEAR FROM AGE('2024-09-20', '2020-09-20')) AS year_difference.
  
• Calculates the difference in years between the dates '2024-09-20' and '2020-09-20'.
  
• The result labeled as year_difference, returns the number of full years between these dates, which is 4 years.

Query:

SELECT EXTRACT(YEAR FROM AGE('2024-09-20', '2020-09-20')) AS year_difference;

Output:

Explanation:

The AGE() function calculates the difference between two dates, and EXTRACT(YEAR) fetches the year part. This output table shows a difference of 4 years.

2. Difference in Months

In this example, the AGE() function calculates the time interval between two dates, while EXTRACT(MONTH) is used to isolate the number of months within that interval. This helps in finding out how many months exist between two dates.

Query:

SELECT EXTRACT(MONTH FROM AGE('2024-03-20', '2020-09-20')) AS month_difference;

Output:

Explanation:

• The query SELECT EXTRACT(MONTH FROM AGE('2024-03-20', '2020-09-20')) AS month_difference.
  
• Computes the difference between the dates '2024-03-20' and '2020-09-20'.
  
• The EXTRACT(MONTH) function retrieves only the month portion of the interval, resulting in a month_difference of 6 months, as the dates span 3 years and 6 months.

3. Difference in Days

In PostgreSQL, subtracting two date values directly returns the number of days between them. This method is straightforward for calculating exact day differences without needing additional functions.

Query:

SELECT ('2024-11-08'::date - '2024-10-25'::date) AS difference_in_days;

Output:

Explanation:

• The query SELECT ('2024-11-08'::date - '2024-10-25'::date) AS difference_in_days.
  
• Subtracts the date '2024-10-25' from '2024-11-08'.
  
• The result, stored as difference_in_days, shows the number of days between the two dates, which is 14 days.
  
• This query converts the date strings to the date type using the ::date cast.

4. Difference in Weeks

To calculate the difference in weeks between two dates in PostgreSQL, you can subtract the dates to get the number of days, then divide the result by 7 to convert the days into weeks. This is helpful for tracking week-based durations.

Query:

SELECT ('2024-11-08'::date - '2024-10-25'::date) /7 AS difference_in_weeks;

Output:

Explanation:

• The query SELECT ('2024-11-08'::date - '2024-10-25'::date) / 7 AS difference_in_weeks.
  
• Subtracts the date '2024-10-25' from '2024-11-08', yielding 14 days.
  
• Dividing this by 7 converts the difference into weeks, resulting in a difference_in_weeks of 2 weeks.
  
• This approach is useful for converting day intervals to week-based calculations.

5. Difference in Hours

To calculate the difference in hours between two timestamps in PostgreSQL, the EXTRACT(EPOCH) function is used to get the difference in seconds, which is then divided by 3600 (the number of seconds in an hour) to convert it into hours. This method is useful for precise time-based calculations.

Query:

SELECT EXTRACT(EPOCH FROM ('2024-09-20 18:00:00'::timestamp - '2024-09-20 08:00:00'::timestamp)) / 3600 AS hour_difference;

Output:

Explanation:

• The query SELECT EXTRACT(EPOCH FROM ('2024-09-20 18:00:00'::timestamp - '2024-09-20 08:00:00'::timestamp)) / 3600 AS hour_difference.
  
• Subtracts two timestamps, producing a time interval of 10 hours.
  
• EXTRACT(EPOCH) converts this interval into seconds (36,000 seconds), and dividing by 3600 converts it into hours, yielding a result of hour_difference = 10.
  
• This method accurately measures the time difference between two specific points in time.

6. Difference in Minutes

To calculate the difference in minutes between two timestamps in PostgreSQL, you can subtract the timestamps to get the interval and then use EXTRACT(EPOCH) to convert the interval to seconds. Dividing the result by 60 converts it to minutes.

Query:

SELECT EXTRACT(EPOCH FROM ('2024-09-20 15:30:00'::timestamp - '2024-09-20 14:45:00'::timestamp)) / 60 AS minute_difference;

Output:

Explanation:

• The query SELECT EXTRACT(EPOCH FROM ('2024-09-20 15:30:00'::timestamp - '2024-09-20 14:45:00'::timestamp)) / 60 AS minute_difference.
  
• Calculating the time difference between two timestamps.
  
• EXTRACT(EPOCH) converts the difference into seconds, which is 2,700 seconds (15:30:00 - 14:45:00).
  
• Dividing by 60 converts this to minute_difference = 45, meaning the two timestamps are 45 minutes apart.

7. Difference in Seconds

In PostgreSQL, the EXTRACT(EPOCH) function can be used to calculate the exact difference between two timestamps in seconds. This is useful for precise time-based calculations where you need the interval in seconds.

Query:

SELECT EXTRACT(EPOCH FROM ('2024-09-20 15:00:00'::timestamp - '2024-09-20 14:55:00'::timestamp)) AS seconds_difference;

Output:

Explanation:

• The query SELECT EXTRACT(EPOCH FROM ('2024-09-20 15:00:00'::timestamp - '2024-09-20 14:55:00'::timestamp)) AS seconds_difference.
  
• Subtracts the two timestamps and calculates the difference.
  
• EXTRACT(EPOCH) returns the total number of seconds in the time interval.
  
• Since the time difference is 5 minutes, the result is seconds_difference = 300, which equals 300 seconds.

Conclusion

Calculating date differences in PostgreSQL is simple and doesn’t require a DATEDIFF function. You can subtract dates or timestamps to find differences in days, months, or years.

For more detailed differences, including time, you can use the AGE function and interval arithmetic. If you need the difference in seconds, the EXTRACT(EPOCH FROM ...) function makes it easy. PostgreSQL offers flexible and powerful ways to work with dates and times.