MONTHS_BETWEEN¶

Syntax¶

MONTHS_BETWEEN( <date_expr1> , <date_expr2> )

Arguments¶

date_expr1

The date to subtract from.

date_expr2

The date to subtract.

Returns¶

A FLOAT representing the number of months between the two dates.

The number is calculated as described below:

• The integer portion of the FLOAT is calculated using the year and month parts of the input values.

• In most situations, the fractional portion is calculated using the day and time parts of the input values. (When calculating the fraction of a month, the function considers each month to be 31 days long.)

  However, there are two exceptions:

  • If the days of the month are the same (e.g. February 28 and March 28), the fractional portion is zero, even if one or both input values are timestamps and the times differ.

  • If the days of the month are both the last day of the month (e.g. February 28 and March 31), the fractional portion is zero, even if the days of the month are not the same.

  For example, the function considers each of the following pairs of dates/timestamps to be exactly 1.0 months apart:

  Date/Timestamp 1	Date/Timestamp 2	Notes
  2019-03-01 02:00:00	2019-02-01 13:00:00	Same day of each month.
  2019-03-28	2019-02-28	Same day of each month.
  2019-03-31	2019-02-28	Last day of each month.
  2019-03-31 01:00:00	2019-02-28 13:00:00	Last day of each month.

Usage notes¶

• If date (or timestamp) d1 represents an earlier point in time than d2, then MONTHS_BETWEEN(d1, d2) returns a negative value; otherwise it returns a positive value. More generally, swapping the inputs reverses the sign: MONTHS_BETWEEN(d1, d2) = -MONTHS_BETWEEN(d2, d1).

• You can use a DATE value for one input parameter and a TIMESTAMP for the other.

• If you use one or more TIMESTAMP values but do not want fractional differences based on time of day, then cast your TIMESTAMP expressions to DATE.

• If you only want integer values, you can truncate, round, or cast the value. For example:

  SELECT
      ROUND(MONTHS_BETWEEN('2019-03-31 12:00:00'::TIMESTAMP,
                           '2019-02-28 00:00:00'::TIMESTAMP)) AS MonthsBetween1;
  +----------------+
  | MONTHSBETWEEN1 |
  |----------------|
  |              1 |
  +----------------+
  

  Copy

• If any input is NULL, the result is NULL.

Examples¶

This example shows differences in whole months. The first pair of dates have the same day of the month (the 15th). The second pair of dates are both the last days in their respective months (February 28th and March 31st).

SELECT
    MONTHS_BETWEEN('2019-03-15'::DATE,
                   '2019-02-15'::DATE) AS MonthsBetween1,
    MONTHS_BETWEEN('2019-03-31'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween2;
+----------------+----------------+
| MONTHSBETWEEN1 | MONTHSBETWEEN2 |
|----------------+----------------|
|       1.000000 |       1.000000 |
+----------------+----------------+

Copy

The next example shows differences in fractional months.

• For the first column, the function is passed two dates.

• For the second column, the function is passed two timestamps that represent the same two dates as were used for the first column, but with different times. The difference in the second column is larger than the first column due to the differences in time.

• For the third column, the function is passed two timestamps that represent the same day of their respective months. This causes the function to ignore any time differences between the timestamps, so the fractional part is 0.

SELECT
    MONTHS_BETWEEN('2019-03-01'::DATE,
                   '2019-02-15'::DATE) AS MonthsBetween1,
    MONTHS_BETWEEN('2019-03-01 02:00:00'::TIMESTAMP,
                   '2019-02-15 01:00:00'::TIMESTAMP) AS MonthsBetween2,
    MONTHS_BETWEEN('2019-02-15 02:00:00'::TIMESTAMP,
                   '2019-02-15 01:00:00'::TIMESTAMP) AS MonthsBetween3
    ;
+----------------+----------------+----------------+
| MONTHSBETWEEN1 | MONTHSBETWEEN2 | MONTHSBETWEEN3 |
|----------------+----------------+----------------|
|       0.548387 |       0.549731 |       0.000000 |
+----------------+----------------+----------------+

Copy

The fact that the function returns an integer number of months both when the days of the month are the same (e.g. February 28 and March 28) and when the days of the month are the last day of the month (e.g. February 28 and March 31) can lead to unintuitive behavior; specifically, increasing the first date in the pair does not always increase the output value. In this example, as the first date increases from March 28th to March 30th and then to March 31st, the difference increases from 1.0 to a larger number and then decreases back to 1.0.

• For the first column, the input dates represent the same day in different months, so the function returns 0 for the fractional part of the result.

• For the second column, the input dates represent different days in different months (and are not both the last day of the month), so the function calculates the fractional part of the result.

• For the third column, the input dates represent the last days in each of two different months, so the function again returns 0 for the fractional part of the result.

SELECT
    MONTHS_BETWEEN('2019-03-28'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween1,
    MONTHS_BETWEEN('2019-03-30'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween2,
    MONTHS_BETWEEN('2019-03-31'::DATE,
                   '2019-02-28'::DATE) AS MonthsBetween3
    ;
+----------------+----------------+----------------+
| MONTHSBETWEEN1 | MONTHSBETWEEN2 | MONTHSBETWEEN3 |
|----------------+----------------+----------------|
|       1.000000 |       1.064516 |       1.000000 |
+----------------+----------------+----------------+

Copy

This example shows that reversing the order of the parameters reverses the sign of the result:

SELECT
    MONTHS_BETWEEN('2019-03-01'::DATE,
                   '2019-02-01'::DATE) AS MonthsBetween1,
    MONTHS_BETWEEN('2019-02-01'::DATE,
                   '2019-03-01'::DATE) AS MonthsBetween2
    ;
+----------------+----------------+
| MONTHSBETWEEN1 | MONTHSBETWEEN2 |
|----------------+----------------|
|       1.000000 |      -1.000000 |
+----------------+----------------+

Copy