DATEDIFF ​

Given a datepart, return the difference between two dates or timestamps.

DATEDIFF ( datepart, { date | timestamp | timestamptz }, { date | timestamp | timestamptz } )

See Supported Date Parts.

Dates or timestamps must contain the specified datepart. The function returns 0 or a positive result if the second date or time is later than the first. The function returns 0 or a negative result if the second date or time is earlier than the first.

If one of the arguments is an infinite date or timestamp ('infinity'), the function returns an error.

Examples ​

Calculate the number of years since two teams played each other:

premdb=# select datediff(year, matchday, current_date) 
from match where (htid=10 and atid=72) or (htid=72 and atid=10);
 datediff 
----------
      18
      17
(2 rows)

Find the difference in seconds between two timestamps:

premdb=# select datediff(seconds, timestamp '2017-11-21 12:30:00', '2016-11-21 12:30:00')/60 from sys.const;
 ?column? 
----------
  -525600
(1 row)

This function calculates the number of datepart boundaries that are crossed between the two expressions (it does not calculate an exact interval). For example, the difference in days between two dates that are 36 hours apart is 2 days.

premdb=# select datediff(days, current_date-interval '36 hours', current_date) from sys.const; 
 datediff 
----------
       2
(1 row)