Timestamp arithmetic
Timestamps can be subtracted, incremented, or decremented.
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. The following procedural description
clarifies the steps involved in the operation RESULT = TS1 - TS2.
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,)
and MINUTE(TS2) is incremented by 1.
The minutes part of the timestamps are subtracted as specified
in the rules for subtracting times.
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 date part of the timestamps is subtracted as specified
in the rules for subtracting dates.
The result of subtracting a date (D1) from a
timestamp (TS1) is the same as subtracting TIMESTAMP(D1) from TS1.
Similarly, the result of subtracting one timestamp (TS1) from a date
(D2) is the same as subtracting TS1 from TIMESTAMP(D2).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. 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. Fractional seconds overflow into seconds. Thus, subtracting a duration, X, from a timestamp, TIMESTAMP1,
where X is a DECIMAL(14+s,s) number is equivalent to the expression:When subtracting
a duration with non-zero scale, a labeled duration
of MICROSECOND or MICROSECONDS, 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.
The precision
of the result timestamp matches the precision of the timestamp operand. 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. Fractional seconds overflow into seconds. Thus, 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