TIMESTAMPADD

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)