TIMESTAMPADD
The TIMESTAMPADD function returns the result of adding the specified number of the designated interval to the timestamp value.
The schema is SYSIBM.
- interval
- An expression that returns a value of a built-in SMALLINT or INTEGER
data type. The following values are valid values for interval:
Table 1. Valid values for intervals Valid values for interval equivalent intervals 1 Microseconds 2 Seconds 4 Minutes 8 Hours 16 Days 32 Weeks 64 Months 128 Quarters 256 Years - number
- An expression that returns a value of a built-in SMALLINT or INTEGER data type.
expression
- An
expression that returns a value of a built-in TIMESTAMP WITHOUT TIME
ZONE data type.
The
result of the function is the same timestamp data type with the same
timestamp precision as expression.
The result can be null; if any argument is null, the result is the null value.
The result is determined using the normal rules for datetime arithmetic. See Datetime arithmetic in SQL. When the interval to add is expressed as weeks, the result is calculated as if number x 7 days had been specified. When the interval to add is expressed as quarters, the result is calculated as if number x 3 months had been specified.
Example
1: The following example will add 40 years to the specified timestamp.
An interval of 256 designates years, while 40 specifies the number
of intervals to add. The following statement returns the value '2005-07-27-15.30.00.000000'.
SELECT TIMESTAMPADD(256,40,TIMESTAMP('1965-07-27-15.30.00'))
FROM SYSIBM.SYSDUMMY1;Example 2: The
following example will add 18 months to the specified timestamp. An
interval of 64 designates months, while 18 specifies the number of
intervals to add. The following statement returns the value '2008-07-20-08.08.00.000000'.
SELECT TIMESTAMPADD(64,18,TIMESTAMP('2007-01-20-08.08.00'))
FROM SYSIBM.SYSDUMMY1;Example 3: The
following example will subtract 16 quarters (4 years) from the specified
timestamp. An interval of 128 designates quarters, while -16 specifies
the number of intervals to add (the '-' adds a negative amount). The
following statement returns the value '2003-09-28-05.30.00.000000'.
SELECT TIMESTAMPADD(128,-16,TIMESTAMP('2007-09-28-05.30.00'))
FROM SYSIBM.SYSDUMMY1;Example 4: The
following example will add 18 weeks to the specified timestamp. An
interval of 32 designates weeks, while 18 specifies the number of
intervals to add. The following statement returns the value '2007-05-27-08.08.00.000000'.
SELECT TIMESTAMPADD(32,18,TIMESTAMP('2007-01-20-08.08.00'))
FROM SYSIBM.SYSDUMMY1;