TIMESTAMPADD

Adds the specified number of intervals to a TIMESTAMP or TIMESTAMPTZ value and returns a result of the same data type.

Adds the specified number of intervals to a TIMESTAMP or TIMESTAMPTZ value and returns a result of the same data type.

Behavior type

  • Immutable if the input date is a TIMESTAMP

  • Stable if the input date is a TIMESTAMPTZ

Syntax

TIMESTAMPADD ( datepart, count, start-date );

Parameters

datepart
Specifies the type of time intervals that TIMESTAMPADD adds to the specified start date. If datepart is an expression, it must be enclosed in parentheses: 
TIMESTAMPADD((expression), interval, start;

datepart must evaluate to one of the following string literals, either quoted or unquoted:

  • year | yy | yyyy

  • quarter | qq | q

  • month | mm | m

  • day | dayofyear | dd | d | dy | y

  • week | wk | ww

  • hour | hh

  • minute | mi | n

  • second | ss | s

  • millisecond | ms

  • microsecond | mcs | us

count
Integer or integer expression that specifies the number of datepart intervals to add to start-date.
start-date
TIMESTAMP or TIMESTAMPTZ value.

Examples

Add two months to the current date:

=> SELECT CURRENT_TIMESTAMP AS Today;
           Today
-------------------------------
 2016-05-02 06:56:57.923045-04
(1 row)

=> SELECT TIMESTAMPADD (MONTH, 2, (CURRENT_TIMESTAMP)) AS TodayPlusTwoMonths;;
      TodayPlusTwoMonths
-------------------------------
 2016-07-02 06:56:57.923045-04
(1 row)

Add 14 days to the beginning of the current month:

=> SELECT TIMESTAMPADD (DD, 14, (SELECT TRUNC((CURRENT_TIMESTAMP), 'MM')));
    timestampadd
---------------------
 2016-05-15 00:00:00
(1 row)