MONTHS_BETWEEN
Returns the number of months between two dates. MONTHS_BETWEEN
can return an integer or a FLOAT8:
- 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. - FLOAT8: 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
orDATE
- Stable if either date is a
TIMESTAMPTZ
Syntax
MONTHS_BETWEEN ( date1 , date2 );
Parameters
date1
|
Specify the dates to evaluate where date1 and date2 evaluate to one of the following data types:
If date1 < date2, |
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)