SECOND

The SECOND function returns the seconds part of a value with optional fractional seconds.

Read syntax diagramSkip visual syntax diagramSECOND (expression,0,precision-constant)
expression
An expression that returns a value of one of the following built-in data types: a date, a time, a timestamp, a character string, a graphic string, or a numeric data type.
  • If expression is a character or graphic string, its value must be a valid string representation of a datetime value. If expression is a valid string representation of a timestamp, it is first converted to a TIMESTAMP(12) value. If expression is a valid string representation of a date, it must be in one of the IBM® SQL standard formats. For the valid formats of string representations of datetime values, see String representations of datetime values.
  • If the argument is a DATE, it is first converted to a TIMESTAMP(0) value, assuming a time of exactly midnight (00.00.00).
  • If expression is a number, it must be a time duration or timestamp duration. For the valid formats of datetime durations, see Datetime operands and durations.
precision-constant
An integer constant representing the number of fractional seconds. The value must be in the range 0 through 12.

The result of the function with a single argument is a large integer. The result of the function with two arguments is DECIMAL(2+s,s) where s is the value of the precision-constant. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

The other rules depend on the data type of the argument:

  • If the argument is a date, a time, a timestamp, or a valid character-string representation of a date, time, or timestamp:

    If only one argument is specified, the result is the seconds part of the value (0 to 59).

    If both arguments are specified, the result is the seconds part of the value (0 to 59) and precision-constant digits of the fractional seconds part of the value. If there are no fractional seconds in the value, then zeroes are returned.

  • If the argument is a time duration or timestamp duration:

    If only one argument is specified, the result is the seconds part of the value (-99 to 99). A nonzero result has the same sign as the argument.

    If both arguments are specified, the result is the seconds part of the value (-99 to 99) and precision-constant digits of the fractional seconds part of the value. If there are no fractional seconds in the value, then zeroes are returned. A nonzero result has the same sign as the argument.

Examples

  • Assume that the host variable TIME_DUR (DECIMAL(6,0)) has the value 153045. 
      SELECT SECOND(:TIME_DUR)
    FROM SYSIBM.SYSDUMMY1
    Returns the value 45.
  • Assume that the column RECEIVED (TIMESTAMP) has an internal value equivalent to 1988-12-25-17.12.30.000000. 
      SELECT SECOND(RECEIVED)
    FROM IN_TRAY
    Returns the value 30.
  • Get the seconds with fractional seconds from a current timestamp in milliseconds.
      SELECT SECOND(CURRENT TIMESTAMP(3),3)
    FROM SYSIBM.SYSDUMMY1
    Returns a DECIMAL(5,3) value based on the current timestamp that could be something like 54.321.