TIMESTAMPADD

The TIMESTAMPADD function adds a specified number of intervals to a timestamp and returns the modified timestamp.

SQL syntax

TIMESTAMPADD (Interval, IntegerExpression, TimestampExpression)

Parameters

TIMESTAMPADD has the parameters:

Parameter Description

Interval

Specified interval. Must be expressed as literal. Valid values are listed in the description section.

IntegerExpression

Expression that evaluates to TT_BIGINT.

TimestampExpression

Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid). TT_TIME is not supported.

Description

  • Valid values for Interval are:

    • 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

  • SQL_TSI_FRAC_SECOND is expressed in billionths of a second.

  • The return type is the same as the original data type. For example, if your expression is of type TIMESTAMP, then the resulting data type is TIMESTAMP. Only positive timestamp expressions (0001-01-01) are allowed both in the query and the result. For TT_DATE and TT_TIMESTAMP, because the starting range for these data types is 1753-01-01, the timestamp expression must be equal to or greater than this date.

  • If IntegerExpression or TimestampExpression is NULL, then the result is NULL.

  • The function computes the total time interval as a product of the IntegerExpression and the interval and adds it to the specified TimestampExpression. Adding a year advances the timestamp by 12 months and adding a week advances the timestamp by seven days. If the IntegerExpression is negative, the specified interval is subtracted from the TimestampExpression.

  • There is a possibility of precision loss depending on your use of the specified interval and timestamp expression. For example, if your interval is SQL_TSI_HOUR, and you specify 2 for IntegerExpression and TT_DATE for TimestampExpression, TimesTen treats the two hours as zero days and returns the sum of the original date plus zero days resulting in some loss of precision. If however, your IntegerExpression is 48, TimesTen treats the 48 hours as two days and returns the sum of the original date plus two days. In this case, there is no loss of precision.

  • If the addition of the timestamp results in an overflow of the specified component (such as more than 60 seconds, or more than 24 hours, or more than 12 months), then the overflow is carried over to the next component. For example, if the seconds component overflows, then the minutes component is advanced.

Examples

Use the TIMESTAMPADD function to add 3 months to timestamp '2009-11-30 10:00:00'. TimesTen increments the year and adjusts the day component to accommodate the 28 days in the month of February. 

Command> SELECT TIMESTAMPADD (SQL_TSI_MONTH, 3, TIMESTAMP '2010-11-30 10:00:00') FROM dual;
< 2011-02-28 10:00:00 >
1 row found.

Use the TIMESTAMPADD function to add 1 second to timestamp '2010-12-31 23:59:59'. TimesTen propagates the overflow through all components of the timestamp and advances the components appropriately. 

Command> SELECT TIMESTAMPADD (SQL_TSI_SECOND, 1, TIMESTAMP '2010-12-31 23:59:59' FROM dual;
< 2011-01-01 00:00:00 >
1 row found.