ADD_MONTHS

Adds the specified number of months to a date and returns the sum as a DATE. In general, ADD_MONTHS returns a date with the same day component as the start date. For example:

=> SELECT ADD_MONTHS ('2015-09-15'::date, -2) "2 Months Ago";
 2 Months Ago
--------------
 2015-07-15
(1 row)

Two exceptions apply:

  • If the start date's day component is greater than the last day of the result month, ADD_MONTHS returns the last day of the result month. For example:

    => SELECT ADD_MONTHS ('31-Jan-2016'::TIMESTAMP, 1) "Leap Month";
 Leap Month
------------
 2016-02-29
(1 row)

  • If the start date's day component is the last day of that month, and the result month has more days than the start date month, ADD_MONTHS returns the last day of the result month. For example:

    => SELECT ADD_MONTHS ('2015-09-30'::date,-1) "1 Month Ago";
 1 Month Ago
-------------
 2015-08-31
(1 row)

Behavior Type

  • Immutable if the start‑date argument is a TIMESTAMP or DATE
  • Stable if the start‑date argument is a TIMESTAMPTZ

Syntax

ADD_MONTHS ( start‑date, num‑months );

Parameters

start‑date

The date to process, an expression that evaluates to one of the following data types:

  • DATE
  • TIMESTAMP
  • TIMESTAMPTZ
num‑months

An integer expression that specifies the number of months to add to or subtract from start‑date.

Examples

Add one month to the current date:

=> SELECT CURRENT_DATE Today;
   Today
------------
 2016-05-05
(1 row)

VMart=> SELECT ADD_MONTHS(CURRENT_TIMESTAMP,1);
 ADD_MONTHS
------------
 2016-06-05
(1 row)

Subtract four months from the current date:

=> SELECT ADD_MONTHS(CURRENT_TIMESTAMP, -4);
 ADD_MONTHS
------------
 2016-01-05
(1 row)

Add one month to January 31 2016:

=> SELECT ADD_MONTHS('31-Jan-2016'::TIMESTAMP, 1) "Leap Month";
 Leap Month
------------
 2016-02-29
(1 row)

The following example sets the timezone to EST; it then adds 24 months to a TIMESTAMPTZ that specifies a PST time zone, so ADD_MONTHS takes into account the time change: 

=> SET TIME ZONE 'America/New_York';
SET
VMart=> SELECT ADD_MONTHS('2008-02-29 23:30 PST'::TIMESTAMPTZ, 24);
 ADD_MONTHS
------------
 2010-03-01
(1 row)