Oracle PL/SQL Programming, 5th Edition

Datetime Arithmetic

Datetime arithmetic in an Oracle database can be reduced to the following types of operations:

• Adding or subtracting an interval to or from a datetime value.

• Subtracting one datetime value from another in order to determine the interval between the two values.

• Adding or subtracting one interval to or from another interval.

• Multiplying or dividing an interval by a numeric value.

For historical reasons, because of the way in which the database has been developed over the years, I draw a distinction between datetime arithmetic involving the DATE type and that involving the family of TIMESTAMP and INTERVAL types.

Date Arithmetic with Intervals and Datetimes

Arithmetic with day to second intervals is easy when working with the TIMESTAMP family of datatypes. Simply create an INTERVAL DAY TO SECOND value and add or subtract it. For example, to add 1500 days, 4 hours, 30 minutes, and 2 seconds to the current date and time:

DECLARE
   current_date TIMESTAMP;
   result_date TIMESTAMP;
BEGIN
   current_date := SYSTIMESTAMP;
   result_date:= current_date + INTERVAL '1500 4:30:2' DAY TO SECOND;
   DBMS_OUTPUT.PUT_LINE(result_date);
END;

Date arithmetic with year and month values is not quite as straightforward. All days can be measured as 24 hours or 1440 minutes or even 86,400 seconds, but not all months have the same number of days. A month may have 28, 29, 30, or 31 days. (I’ll ignore the goofy month when the Gregorian calendar was adopted). Because of this disparity in the number of days in ...