Timestamp arithmetic

Timestamps can be subtracted, incremented, or decremented.

If any of the operands are TIMESTAMP WITH TIME ZONE, any TIMESTAMP WITHOUT TIME ZONE values are implicitly cast to TIMESTAMP WITH TIME ZONE, and the datetime arithmetic operation is performed in UTC time (ignoring the time zone).

Subtracting timestamps: The result of subtracting one timestamp (TS2) from another (TS1) is a timestamp duration that specifies the number of years, months, days, hours, minutes, seconds, and fractional seconds between the two timestamps.

The data type of the result is DECIMAL(14+s,s), where s is the maximum timestamp precision of TS1 and TS2. If TS1 is greater than or equal to TS2, TS2 is subtracted from TS1. If TS1 is less than TS2. However, TS1 is subtracted from TS2 and the sign of the result is made negative. A subtraction that involves a timestamp with a time zone operand is based on the UTC value of the timestamp with the time zone. The time zone is ignored.

The following procedural description clarifies the steps involved in the operation RESULT = TS1 - TS2.

Timestamp subtraction: result = ts1 - ts2 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. If SECOND(TS2,s) <= SECOND(TS1,s) then SECOND(RESULT,s) = SECOND(TS1,s) - SECOND(TS2,s). If SECOND(TS2,s) > SECOND(TS1,s) then SECOND(RESULT,s) = 60 + SECOND(TS1,s) – SECOND(TS2,s). MINUTE(TS2) is incremented by 1. 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. The minutes part of the timestamps are subtracted as specified in the rules for subtracting times. The date part of the timestamps is subtracted as specified in the rules for subtracting dates.

Incrementing and decrementing timestamps: The result of adding a duration to a timestamp, or of subtracting a duration from a timestamp, is itself a timestamp. The precision of the result timestamp matches the precision of the timestamp operand. The date and time arithmetic is performed as previously defined, except that an overflow or underflow of hours is carried into the date part of the result, which must be within the range of valid dates. The time arithmetic portion is similar to time arithmetic, except that it also considers the fractional seconds included in the duration. For example, subtracting a duration, X, from a timestamp, TIMESTAMP1, where X is a DECIMAL(14+s,s) number, is equivalent to the expression:

TIMESTAMP1 - YEAR(X)  YEARS - MONTH(X)  MONTHS - DAY(X)  DAYS
           - HOUR(X)  HOURS - MINUTE(X)  MINUTES - SECOND(X, s)  SECONDS

When subtracting a duration with a non-zero scale or a labeled duration of SECOND or SECONDS with a value that includes fractions of a second, the subtraction is performed as if the timestamp value has up to 12 fractional second digits. The resulting value is assigned to a timestamp value with the timestamp precision of the timestamp operand, which could result in truncation of fractional second digits.

When the result of an operation is midnight, the time portion of the result can be '24.00.00' or '00.00.00'. A comparison of those two values does not result in 'equal'. Microseconds overflow into seconds.