ADD_MONTHS

Adds the specified number of months to a date and returns the sum as a DATE.

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)