TIMESTAMPADD

The TIMESTAMPADD function adds the value of an interval to a timestamp value and returns the sum as a new timestamp. You can supply a negative interval value to substract from a timestamp.

Syntax

TIMESTAMPADD ( interval, count, timeStamp1 )

interval

One of the following timestamp interval constants:

  • SQL_TSI_FRAC_SECOND
  • SQL_TSI_SECOND
  • SQL_TSI_MINUTE
  • SQL_TSI_HOUR
  • SQL_TSI_DAY
  • SQL_TSI_WEEK
  • SQL_TSI_MONTH
  • SQL_TSI_QUARTER
  • SQL_TSI_YEAR

count

An integer specifying the number of times the interval is to be added to the timestamp. Use a negative integer value to subtract.

timeStamp1

The timestamp value to which the count of intervals is added.

If you use a datetime column inside the TIMESTAMPADD function in a WHERE clause, the optimizer cannot use indexes on that column. We strongly recommend not doing this!

Results

The TIMESTAMPADD function returns a timestamp value that is the result of adding count intervals to timeStamp1.

Examples

The following example displays the current timestamp, and the timestamp value two months from now:

splice> VALUES ( CURRENT_TIMESTAMP, TIMESTAMPADD(SQL_TSI_MONTH, 2, CURRENT_TIMESTAMP ));
1                            |2
-----------------------------------------------------------
2015-11-23 13:54:16.728      |2016-01-23 13:54:16.728

1 row selected

See Also