SECOND scalar function

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

The schema is SYSIBM.

expression

expression must be an expression that returns a value of one of the following built-in data types: a time, a timestamp, a character string, a graphic string, or a numeric data type.

If expression is a character or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a time or timestamp with an actual length that is not greater than 255 bytes. For the valid formats of string representations of times and timestamps, see String representations of datetime values.
If expression is a number, it must be a time or timestamp duration. For the valid formats of time and timestamp durations, see Datetime operands.

integer-constant

integer-constant must be an integer constant that represents the scale for the fractional seconds portion of expression. The value must be in the range 0 through 12. If integer-constant is not specified, the result does not include fractional seconds.

If expression is a timestamp with a time zone, or a valid string representation of a timestamp with a time zone, the result is determined from the UTC representation of the datetime value.

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 integer-constant.

The result can be null; if the first 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 time, timestamp, or string representation of a time or a timestamp:: The result is the seconds part of the value (0 to 59) and any fractional seconds that are included in the value. If the second argument is specified, the result includes integer-constant digits of the fractional seconds part of the value where applicable. If there are no fractional seconds in the value, zeros are returned.
If the argument is a time duration or timestamp duration:: The result is the seconds part of the value (-99 to 99) and any fractional seconds that are included in the value. If the second argument is specified, the result includes integer-constant digits of the fractional seconds part of the value where applicable. If there are no fractional seconds in the value, zeros are returned. A nonzero result has the same sign as the expression.

Example 1: Assume that the variable TIME_DUR is declared in a PL/I program as DECIMAL(6,0) and can therefore be interpreted as a time duration. When TIME_DUR has the value 153045, the following function returns the value 45.

   SECOND(:TIME_DUR)

Example 2: Assume that RECEIVED is a TIMESTAMP column and that one of its values is the internal equivalent of '1988-12-25-17.12.30.000000'. The following function returns the value 30.

   SECOND(RECEIVED)

Example 3: The following invocations of the SECOND function returns the same result:

SELECT SECOND('2003-01-02-20.10.05.123456'), 
			SECOND('2003-01-02-12.10.05.123456-08:00'), 
			SECOND('2003-01-03-05.10.05.123456+09:00') 
		FROM SYSIBM.SYSDUMMY1;

For each invocation of the SECOND function in this SELECT statement, the result is 5.

When the input argument contains a time zone, the result is determined from the UTC representation of the input value. The string representations of a timestamp with a time zone in the SELECT statement all have the same UTC representation: 2003-01-02-20.10.05.123456. The second portion of the UTC representation is 5.

Example 4: Return the seconds with fractional seconds from a current timestamp with milliseconds.

SELECT SECOND(CURRENT_TIMESTAMP(3),3)
  FROM SYSIBM.SYSDUMMY1;

The SELECT statement returns a DECIMAL(5,3) value that is based on the current timestamp and could be something like 54.321.