Start of change

ADD_SECONDS

Edit online

The ADD_SECONDS function returns a timestamp that represents the first argument plus a specified number of seconds and fractional seconds.

Read syntax diagramSkip visual syntax diagramADD_SECONDS(expression ,numeric-expression)
expression
An expression that specifies the starting timestamp. The expression must return a value of one of the following built-in data types: a timestamp, a character string, or a graphic string.

If expression is a character or graphic string, its value must be a valid string representation of a timestamp. For the valid formats of string representations of timestamps, see String representations of datetime values.

numeric-expression
An expression that specifies the number of seconds and fractional seconds to add to expression. numeric-expression must return a value that is a built-in numeric data type. If the expression is not a DECIMAL(27,12), it is cast to DECIMAL(27,12) before the function is evaluated. A negative numeric value can be used to subtract seconds and fractional seconds.

If expression is a timestamp, the result of the function is a timestamp with the same precision as expression. Otherwise, the result of the function is TIMESTAMP(12). If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Examples

  • Assume that the current timestamp is January 31, 2007, 01:02:03.123456. Set the host variable ADD_SECOND with the current timestamp plus 1 second.
    SET :ADD_SECOND = ADD_SECONDS(CURRENT TIMESTAMP, 1)

    The host variable ADD_SECOND is set with the value representing 2007-01-31 01:02:04.123456.

  • Assume that TIMESTAMP is a host variable with the value July 27, 1965 23:59:59.123456. Set the host variable ADD_SECOND with the value of that timestamp plus 3.123 seconds.
    SET :ADD_SECOND = ADD_SECONDS(:TIMESTAMP,3.123)

    The host variable ADD_SECOND is set with the value representing the timestamp plus 3.123 seconds, 1965-07-28 00:00:02.246456.

  • The ADD_SECONDS function and datetime arithmetic can be used to achieve the same results. The following examples demonstrate this.
    SET :TIMESTAMPHV = TIMESTAMP '2008-02-28 23:58:59.123456' + 61.654321 SECONDS  
SET :TIMESTAMPHV = ADD_SECONDS(
      TIMESTAMP '2008-02-28 23:58:59.123456', 61.654321)

    In both cases, the host variable TIMESTAMPHV is set with the value '2008-02-29 00:00:00.777777'.

    Now consider the same examples but with the timestamp '2008-02-29 23:59:59.123456' as the argument. 

    SET :TIMESTAMPHV = TIMESTAMP '2008-02-29 23:59:59.123456' + 61.654321 SECONDS
SET :TIMESTAMPHV = ADD_SECONDS(
      TIMESTAMP '2008-02-29 23:59:59.123456', 61.654321)

    In both cases, the host variable TIMESTAMPHV is set with the value '2008-03-01 00:01:00.777777'.

  • Assume that TIMESTAMP is a host variable with the value July 27, 1965 23:59:59.123456. Set the host variable ADD_SECOND with the value of that timestamp minus 3.123 seconds.
    SET :ADD_SECOND = ADD_SECONDS(:TIMESTAMP,-3.123)

    The host variable ADD_SECOND is set to 1965-07-27 23:59:56.000456; the value representing July 27, 1965 23:59:59.123456 minus 3.123 seconds.

End of change