PostgreSQL DATEDIFF Function

1. Use the EXTRACT Function to Get Date Difference in PostgreSQL
2. Use the DATE_PART Function to Get Date Difference in PostgreSQL

Unlike SQL Server, PostgreSQL does not have a built-in DATEDIFF function to calculate the difference between dates. However, the date difference can be obtained using expressions containing other functions provided by PostgreSQL.

This tutorial discusses ways to calculate the difference in days between dates in PostgreSQL.

Use the EXTRACT Function to Get Date Difference in PostgreSQL

Syntax:

EXTRACT(field, source)

The field could be a year, month, day, etc. The source type could be a timestamp, time or interval.

If a table’s column is of type date, it can be typecast to timestamp.

The difference in days can be calculated using the EXTRACT function, as shown in the next example.

SELECT EXTRACT(DAY FROM '2022-01-15 11:40:00'::timestamp - '2021-11-03 13:10:00'::timestamp)

Output:

 extract
---------
   72

Note that PostgreSQL will return the full days between the two dates, and it considers the difference in hours minutes. A different result is returned if there are no differences in hours, minutes and seconds, as seen in the example below.

SELECT EXTRACT(DAY FROM '2022-01-15 00:00:00'::timestamp - '2021-11-03 00:00:00'::timestamp)

Output:

 extract
---------
   73

The dates used in the example above are the same as when a date type field is converted to timestamp.

Use the DATE_PART Function to Get Date Difference in PostgreSQL

Syntax:

DATE_PART('field', source)

The field could be a year, month, day, etc. It must be of type string.

The difference in days can also be calculated using the DATE_PART function like the following command.

SELECT DATE_PART('day', '2022-01-15 11:40:00'::timestamp - '2021-11-03 13:10:00'::timestamp);

Output:

 date_part
-----------
    72