Subtracting Timestamps

Restriction: This topic applies to Windows environments only.

In DB2 mode and XDB mode, when one timestamp is subtracted from another, the result is a timestamp duration that indicates the number of years, months, days, hours, minutes, and seconds. The data type of the result is DECIMAL(20).

The rules that apply to timestamp subtraction are as follows:

If TIMESTAMP1 is greater than or equal to TIMESTAMP2, then TIMESTAMP2 is subtracted from TIMESTAMP1.
If TIMESTAMP1 is less than TIMESTAMP2, TIMESTAMP1 is subtracted from TIMESTAMP2 and the result is negative.

Using the operation RESULT = TS1 - TS2, these rules can be illustrated as follows:

If  MICROSECOND(TS2) <= MICROSECOND(TS1)
  Then  MICROSECOND(RESULT) = MICROSECOND(TS1) - 
      MICROSECOND(TS2)
If  MICROSECOND(TS2) > MICROSECOND(TS1)
  Then  MICROSECOND(RESULT) = 1000000 +
      MICROSECOND(TS1) - MICROSECOND(TS2)
  And  SECOND(TS2) is incremented by 1

Note:

The rules for subtracting times determine how the time portions of the timestamps are subtracted.

If  HOUR(TS2) <= HOUR(TS1)
  Then  HOUR(RESULT) = HOUR(TS1) - HOUR(TS2)
If  HOUR(TS2) > HOUR(TS1)
  Then  HOUR(RESULT) = 24 + HOUR(TS1) - HOUR(TS2)
  And  DAY(TS2) is incremented by 1

Note:

The rules for subtracting dates determine how the date portions of the timestamps are subtracted.