MONTHS_BETWEEN

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

MONTHS_BETWEEN disregards timestamp time portions.

Behavior Type

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)