Time arithmetic

Times can be subtracted, incremented, or decremented.

Subtracting times: The result of subtracting one time (TIME2) from another (TIME1) is a time duration that specifies the number of hours, minutes, and seconds between the two times. The data type of the result is DECIMAL(6,0). If TIME1 is greater than or equal to TIME2, TIME2 is subtracted from TIME1. If TIME1 is less than TIME2, however, TIME1 is subtracted from TIME2, and the sign of the result is made negative. The following procedural description clarifies the steps involved in the operation RESULT = TIME1 - TIME2.

Time subtraction: result = time1 - time2
  • 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 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).

For example, the result of TIME('11:02:26') - '00:32:56' is '102930' (a duration of 10 hours, 29 minutes, and 30 seconds). In this example, notice that the second operand did not need to be converted to a time. According to one of the rules for subtraction, described under Datetime arithmetic in SQL, the second operand can be a string representation of a time if the first operand is a time.

Incrementing and decrementing times: The result of adding a duration to a time, or of subtracting a duration from a time, is itself a time. Any overflow or underflow of hours is discarded, thereby ensuring that the result is always a time. If a duration of hours is added or subtracted, only the hours portion of the time is affected. Adding 24 hours to the time '00:00:00' results in the time '24:00:00'. However, adding 24 hours to any other time results in the same time; for example, adding 24 hours to the time '00:00:59' results in the time '00:00:59'. The minutes and seconds are unchanged.

Similarly, if a duration of minutes is added or subtracted, only minutes and, if necessary, hours are affected. The seconds portion of the time is unchanged.

Adding or subtracting a duration of seconds affects the seconds portion of the time and might affect the minutes and hours.

Time durations, whether positive or negative, can also be added to and subtracted from times. The result is a time that has been incremented or decremented by the specified number of hours, minutes, and seconds, in that order. Thus, TIME1 + X, where X is a positive DECIMAL(6,0) number, is equivalent to the expression 
  TIME1 + HOUR(X) HOURS + MINUTE(X) MINUTES + SECOND(X) SECONDS