An arithmetic operation combines two or more numeric expressions using the arithmetic operators to form a resulting numeric expression.

Before performing any arithmetic operation, the DBMS Server converts the participating expressions to identical data types. After the arithmetic operation is performed, the resulting expression has that storage format also. For details, see Default Type Conversion.

When two numeric expressions are combined, the DBMS Server converts as necessary to make the data types of the expressions identical and assigns that data type to the result. The expression having the data type of lower precedence to that of the higher is converted. The order of precedence among the numeric data types is, in highest-to-lowest order:

money
float4
float
i4
i2
i1

For example, in an operation that combines an integer and a floating point number, the integer is converted to a floating point number. If the DBMS Server operates on two integers of different sizes, the smaller is converted to the size of the larger. The conversions are done before the operation is performed.

The following table lists the data types that result from combining numeric data types in expressions:

For example, for the expression

the first operator (+) combines a float4 expression (job.lowsal) with a i2 constant (1000). The result is float4. The second operator (*) combines the float4 expression with a i2 constant (12), resulting in a float4 expression.

To convert one data type to another you must use data type conversion functions. For details, see Data Type Conversion Functions.

To specify error handling for numeric overflow, underflow and division by zero, use the connect statement -numeric_overflow=option flag. Error-handling options are:

This flag can also be specified on the command line for Ingres operating system commands that accept QUEL option flags. For details about QUEL option flags, see the quel command description in the Command Reference Guide.

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,

yields "6 days 5 hours," while

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:

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:

To convert the interval back to a date:

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:

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.