YEARS_BETWEEN ​

Return the full number of years between two dates. The result may be positive or negative.

YEARS_BETWEEN (date1, date2)

The inputs may have DATE, TIMESTAMP, or TIMESTAMPTZ data types. If one of the inputs is null, the result is null. The return type of this function is DOUBLE PRECISION.

The result of this function is equivalent to the result of EXTRACT(year FROM AGE(date1, date2)).

If the first date is later than the second date, the result is a positive number; otherwise, the result is negative.

For example, depending on the order of date expressions in this example, the function returns a positive number or a negative number:

premdb=> select years_between(matchday,current_date) num_years, matchday from match where htid=2 and atid=60;
 num_years |      matchday       
-----------+---------------------
      -13 | 2010-03-06 00:00:00
(1 row)

premdb=> select years_between(current_date,matchday) num_years, matchday from match where htid=2 and atid=60;
 num_years |      matchday       
-----------+---------------------
       13 | 2010-03-06 00:00:00
(1 row)

Note that the result of this function is always a whole number. These two dates are 2 years, 11 months apart, but the answer is 2:

yellowbrick=> select years_between('2023-11-30','2020-12-31') from sys.const;
 years_between 
---------------
            2
(1 row)

See also MONTHS_BETWEEN and DAYS_BETWEEN.

Parent topic:Datetime Functions