MONTHS_BETWEEN

Returns the number of months between two dates. MONTHS_BETWEEN can return an integer or a FLOAT:

  • Integer: The day portions of date1 and date2 are the same, and neither date is the last day of the month. MONTHS_BETWEEN also returns an integer if both dates in date1 and date2 are the last days of their respective months. For example, MONTHS_BETWEEN calculates the difference between April 30 and March 31 as 1 month.
  • FLOAT: The day portions of date1 and date2 are different and one or both dates are not the last day of their respective months. For example, the difference between April 2 and March 1 is 1.03225806451613. To calculate month fractions, MONTHS_BETWEEN assumes all months contain 31 days.

MONTHS_BETWEEN disregards timestamp time portions.

Behavior Type

  • Immutable if both date arguments are of data type TIMESTAMP or DATE
  • Stable if either date is a TIMESTAMPTZ

Syntax

MONTHS_BETWEEN ( date1 , date2 );

Parameters

date1
date2

Specify the dates to evaluate where date1 and date2 evaluate to one of the following data types:

  • DATE
  • TIMESTAMP
  • TIMESTAMPTZ

If date1 < date2, MONTHS_BETWEEN returns a negative value.

Examples

Return the number of months between April 7 2016 and January 7 2015:

=> SELECT MONTHS_BETWEEN ('04-07-16'::TIMESTAMP, '01-07-15'::TIMESTAMP);
 MONTHS_BETWEEN
----------------
             15
(1 row)

Return the number of months between March 31 2016 and February 28 2016 (MONTHS_BETWEEN assumes both months contain 31 days):

=> SELECT MONTHS_BETWEEN ('03-31-16'::TIMESTAMP, '02-28-16'::TIMESTAMP);
  MONTHS_BETWEEN
------------------
 1.09677419354839
(1 row)

Return the number of months between March 31 2016 and February 29 2016:

=> SELECT MONTHS_BETWEEN ('03-31-16'::TIMESTAMP, '02-29-16'::TIMESTAMP);
 MONTHS_BETWEEN
----------------
              1
(1 row)