Subtracting Times

In DB2 mode, when one time is subtracted from another, the result is a time duration that indicates the number of hours, minutes and seconds between the two times. For example, the result of:

TIME('10:22:16') - '00:34:46'

is 94730, (duration of 9 hours, 47 minutes and 30 seconds). The data type of the result is DECIMAL(6,0).

The rules that apply to the time subtraction calculation are as follows:

• If TIME1 is greater than or equal to TIME2, then TIME2 is subtracted from TIME1.
• If TIME1 is less than TIME2, TIME1 is subtracted from TIME2 and the result is negative.

Using the operation RESULT = TIME1 - TIME2, these rules can be illustrated as follows:

If  SECOND(TIME2) <= SECOND(TIME1)
  Then  SECOND(RESULT) = 
    SECOND(TIME1) - SECOND(TIME2)
If SECOND(TIME2) > SECOND(TIME1)
  Then  SECOND(RESULT) = 
    60 + SECOND(TIME1) - SECOND(TIME2)
  And  MINUTE(TIME2) is then incremented by 1
If MINUTE(TIME2) <= MINUTE(TIME1)
  Then  MINUTE(RESULT) = 
    MINUTE(TIME1) - MINUTE(TIME2)
If MINUTE(TIME2) > MINUTE(TIME1)
  Then  MINUTE(RESULT) = 
    60 + MINUTE(TIME1) - MINUTE(TIME2)
  And  HOUR(TIME2) is incremented by 1
HOUR(RESULT) = HOUR(TIME1) - HOUR(TIME2)