Arithmetic Operations
An arithmetic operation combines two or more numeric expressions using the
arithmetic operators (see
Arithmetic Operators) to form a resulting numeric expression.
Before an arithmetic operation is performed, the participating expressions are converted to identical data types. After the arithmetic operation is performed, the resulting expression also has that storage format.
Default Type Conversion
When two numeric expressions are combined, the data types of the expressions are converted to be identical; this conversion determines the data type of the result. The expression having the data type of lower precedence is converted to the data type of higher precedence.
The order of precedence among the numeric data types, in highest-to-lowest order, is as follows:
• money
• float4
• float
• decimal
• integer8 (bigint)
• integer4 (integer)
• integer2 (smallint)
• integer1 (tinyint)
For example, in an operation that combines an integer and a floating point number, the integer is converted to a floating point number before the operation is performed. If the operands are two integers of different sizes, the smaller is converted to the size of the larger.
For example, for the expression:
(job.lowsal + 1000) * 12
the first operator (+) combines a float4 expression (job.lowsal) with a smallint constant (1000). The result is float4. The second operator (*) combines the float4 expression with a smallint constant (12), resulting in a float4 expression.
The following table lists the data types that result from combining numeric data types in expressions:
To convert one data type to another, use
data type conversion functions (see
Conversion Functions).
Arithmetic Operations on Decimal Data Types
Standard decimal handling rules are used by default (the decimal_rule parameter in config.dat is set to Standard rather than Classic).
In expressions that combine decimal values and return decimal results, the precision (total number of digits) and scale (number of digits to the right of the decimal point) of the Standard result can be determined, as shown in the following table:
If the result precision is larger than 39, the result scale is reduced by the amount of the precision excess, and the result precision is set to 39. The result scale is not reduced below 4 if the input scales are 4 or more; if both input scales are less than 4, the result scale is not reduced to less than the larger.
For example, in the following decimal addition operation:
1.234 + 567.89
the scale and precision of the result is calculated as follows:
Precision = 7, calculated as 3 (largest number of fractional digits) + 3 (largest number of non-fractional digits) + 1 = 7.
Scale = 3. The first operand has the largest number of digits to the right of the decimal point.
Result: 0569.124
If exponentiation is performed on a decimal value, the resulting data type is float.
Comparison of Decimal Handling Settings
Decimal arithmetic is handled according to the setting on the decimal_rule parameter in config.dat--either Classic or Standard.
The following examples compare Classic and Standard decimal handling. Given the input precision and scale, the result precision and scale for Classic and Standard settings are shown.
The Classic rules follow the SQL Standard specifications for result scale, even when the precision has to be limited to the maximum of 39. The effect is that in a complex expression, the result scale tends to be large, which reduces the number of available non-fractional digits and makes numeric overflow more likely.
The Standard rules reduce both precision and scale when precision gets too large, which favors non-fractional digits at the expense of fractional digits and makes numeric overflow less likely.
Specify Error Handling for Arithmetic
To specify error handling for numeric overflow, underflow, and division by zero, use one of these methods:
• Set the ‑NUMERIC_OVERFLOW=option flag:
– On the CONNECT statement or
– On the command line for commands that accept SQL option flags. For details about SQL option flags, see the sql command in the Command Reference Guide.
where option is one of the following:
IGNORE
Issues no error
WARN
Issues a warning message
FAIL
(Default) Issues an error message and aborts the statements that caused the error. To obtain ANSI‑compliant behavior, specify this option (or omit the ‑NUMERIC_OVERFLOW flag).
Date and Time Arithmetic
The following arithmetic is supported for date and time data types.
Notes:
• INGRESDATE can store both absolute values and interval values.
• “interval type” can be any of the ANSI forms or the INGRESDATE interval form
• “absolute type” is a non-interval form
Operators that return absolute date forms:
The combining rules are: If either term is INGRESDATE, the result will also be INGRESDATE; otherwise, the result will be the same as the absolute term.
Operators used with intervals:
The combining rules are:
• ANSI absolute types must match to take their difference.
• You cannot combine ANSI Year To Month and ANSI Day To Second intervals.
• If an operand is INGRESDATE the result will also be INGRESDATE.
Ingresdate Interval Arithmetic
Ingresdate intervals can be added, subtracted, and divided, and can be multiplied or divided with a numeric expression. The following lists the results of date arithmetic:
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 above example, 20 minutes and 80 minutes are added and the result is 1 hour 40 minutes. 20 minutes plus 80 minutes equals 100 minutes, but this result overflows the minute time unit because there are 60 minutes in an hour. Overflows are propagated upward except when intervals are added. In the above example, the result is 1 hour 40 minutes. However, days are not propagated to months. For example, if 25 days is added to 23 days, the result is 48 days.
When intervals or absolute dates are subtracted, the result is returned in appropriate time units. For example, if the following subtraction is performed:
date('2 days') - date('4 hours')
the result is 1 day 20 hours.
Date constants can be converted 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.
Adding a month to a date always yields the same date in the next month. For example:
date('1-feb-98') + '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.
Dates that are stored without time values can acquire time values as a result of date arithmetic. For example, the following SQL statements create a table with one date column and store today’s date (with no time) in the column:
create table dtest (dcolumn date);
insert into dtest (dcolumn) values (date('today'));
If the contents of the date column is selected using the following query:
select dcolumn from dtest;
a date with no time is returned. For example:
09-aug-2001
If date arithmetic is used to adjust the value of the date column, the values in the column acquire a time. For example:
update dtest set dcolumn=dcolumn-date('1 hour');
select dcolumn from dtest;
returns the value:
08-aug-1998 23:00:00
ANSI Date and Time Comparisons
ANSI date and time values can be compared only to values of the same type. When the time zone setting of a time or timestamp value does not match that of its comparand, time or timestamp values WITH TIME ZONE or WITH LOCAL TIME ZONE are considered to be stored in GMT and can be compared directly. If one comparand is time or timestamp WITH TIME ZONE or WITH LOCAL TIME ZONE and the other comparand is WITHOUT TIME ZONE, the value of the WITHOUT TIME ZONE comparand is assumed to be in local time of the session default time zone. It is converted to GMT based on that assumption and the comparison proceeds.
Ingresdate Comparisons
In comparisons, a blank (default) ingresdate is less than any interval ingresdate. All interval ingresdates are less than all absolute ingresdates. Intervals are converted to comparable units before they are compared. For instance, before comparing ingresdate('5 hours') and ingresdate('200 minutes'), both the hours and minutes are converted to milliseconds internally before comparing the values. Ingresdates 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.
Note: Support of blank date values and the ability to compare absolute and interval values are available only with the ingresdate type and not with the ANSI datetime data types.
Operator Coercion Rules
The implicit coercion rules for operators are as follows:
1. Generally, where a numeric type is required, a string type can be supplied. This is the case for c, text, char, varchar, nchar, and nvarchar. If the character data turns out not to be numeric in form, a conversion error will occur.
2. Generally, where a string type is required, a numeric type can be supplied. This is the case for all integers, decimal, and all floats.
3. When an operator combines two values of the same data class, string or number, then the following precedence, shown from highest to lowest, is followed:
nchar
nvarchar
c
text
money
char
varchar
float
decimal
int
The resolver coerces to types that retain data without over inflating it. For example, coercion of nchar to char is avoided due to loss of richness of data form or collation. Similarly, coercing to a long type might work but would be grossly inefficient. Associated with this is the practical notion that most operators have a type themselves, some are arithmetic (*, /, and so on) and some are string (||, LIKE, and so on).
4. The exception to Rule 3 is the “+” operator, which is an arithmetic operator as well as a concatenation operator for strings. The two modes coexist, but if a number is added to a string, then the result type is a number.
5. Mixed type comparisons between character and numeric data are virtually coerced into Numeric String data before being compared.