ADD_MONTHS

Adds the specified number of months to a date and returns the sum as a DATE. The function returns the last day of the month if one of the following conditions is true:

Otherwise, ADD_MONTHS returns a date with the same day component as start‑date. Thus, 2016‑03‑15 +2 returns 2016‑05‑15.

Behavior Type

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)