DATEDIFF

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

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

See Supported Dateparts.

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.

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)