MICROSECOND scalar function

The MICROSECOND function returns the microsecond part of a value.

Read syntax diagramSkip visual syntax diagramMICROSECOND( expression)

The schema is SYSIBM.

The argument must be an expression that returns a value of one of the following built-in data types: 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 timestamp with an actual length of 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 timestamp duration. For the valid formats of timestamp durations, see Datetime operands.

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 is a large integer.

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 timestamp or string representation of a timestamp, the result is the microsecond part of the value, which is an integer in the range 0–999999. If the precision of the timestamp exceeds 6, the value is truncated.
  • If the argument is a duration, the result is the microsecond part of the value, which is an integer between -999999 and 999999. A nonzero result has the same sign as the argument.
Example 1: Assume that table TABLEX contains a TIMESTAMP column named TSTMPCOL and a SMALLINT column named INTCOL. Select the microseconds part of the TSTMPCOL column of the rows where the INTCOL value is 1234:
   SELECT MICROSECOND(TSTMPCOL) FROM TABLEX
     WHERE INTCOL = 1234;
Example 2: The following invocations of the MICROSECOND function returns the same result:
SELECT MICROSECOND('2003-01-02-20.00.00.123456'), 
			MICROSECOND('2003-01-02-12.00.00.123456-08:00'), 
			MICROSECOND('2003-01-03-05.00.00.123456+09:00') 
		FROM SYSIBM.SYSDUMMY1;
For each invocation of the MICROSECOND function in this SELECT statement, the result is 123456.

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.00.00.123456.