Arithmetic Operations on Dates
QUEL supports the following arithmetic operations for the date data type:
You cannot multiply or divide date values.
When adding intervals, each of the units is added. For example,
date("6 days") + date("5 hours")
yields "6 days 5 hours," while
date("4 years 20 minutes") + date("6 months 80 minutes")
yields "4 years 6 months 1 hour 40 minutes."
In the preceding example, 20 minutes and 80 minutes are added and the result is "1 hour 40 minutes." 20 minutes plus 80 minutes is 100 minutes; however, because there are only 60 minutes in one hour, this result is considered to have overflowed the minute time unit. With one exception, whenever you add intervals, the DBMS Server propagates all overflows upward. In the above example, the result is returned as "1 hour 40 minutes." However, days are not propagated to months. For example, if you add 25 days to 23 days, the result is 48 days.
When you subtract intervals or absolute dates, the result is returned in appropriate time units. For example, if you perform the following subtraction:
date("2 days") - date("4 hours")
the result is "1 day 20 hours."
You can convert date constants into numbers of days relative to an absolute date. For example, to convert today's date to the number of days since January 1, 1900:
num_days = int4(interval("days", "today" -
date("1/1/00")))
To convert the interval back to a date:
(date("1/1/00") + concat(char(num_days), " days"))
where "num_days" is the number of days added to the date constant.
In comparisons, a blank (default) date is less than any interval date. All interval dates are less than all absolute dates. Intervals are converted to comparable units before they are compared. For instance, before comparing date("5 hours") and date("200 minutes"), the DBMS Server converts both the hours and minutes to milliseconds internally before comparing the values. Dates are stored in Greenwich Mean Time (GMT). For this reason, "5:00 pm" Pacific Standard Time is equal to "8:00 pm" Eastern Standard Time.
Adding a month to a date always yields the same date in the next month. For example:
date("1-feb-89") + "1 month"
yields March 1.
If the result month has fewer days, the resulting date is the last day of the next month. For instance, adding a month to May 31 yields June 30, instead of June 31, which does not exist. Similar rules hold for subtracting a month and for adding and subtracting years.