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:
- The start‑date argument specifies the last day of the month. For example,
2015‑01‑28 +12
returns2016‑01‑29
. - The start‑date argument's day component is greater than the last day of the month returned by
ADD_MONTHS
. For example,2015‑01‑29 +1
returns2015‑02‑28
.
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
- Immutable if the start‑date argument is a
TIMESTAMP
orDATE
- 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:
|
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)