Implicit CASTs for arithmetic operations

This topic lists the implicit CASTs available for arithmetic operations.

Normally the arithmetic operators (+, -, *, and /) operate on operands of the same data type, and return a value of the same data type as the operands. Cases where it is acceptable for the operands to be of different data types, or where the data type of the resulting value is different from the type of the operands, are shown in the following table.

The following table lists the implicit CASTs for arithmetic operation.
Left operand data type Right operand data type Supported operators Result data type
INTEGER FLOAT +, -, *, / FLOAT1
INTEGER DECIMAL +, -, *, / DECIMAL1
INTEGER INTERVAL * INTERVAL4
FLOAT INTEGER +, -, *, / FLOAT1
FLOAT DECIMAL +, -, *, / FLOAT1
FLOAT INTERVAL * INTERVAL4
DECIMAL INTEGER +, -, *, / DECIMAL1
DECIMAL FLOAT +, -, *, / FLOAT1
DECIMAL INTERVAL * INTERVAL4
TIME TIME - INTERVAL2
TIME GMTTIME - INTERVAL2
TIME INTERVAL +, - TIME3
GMTTIME TIME - INTERVAL2
GMTTIME GMTTIME - INTERVAL2
GMTTIME INTERVAL +, - GMTTIME3
DATE DATE - INTERVAL2
DATE INTERVAL +, - DATE3
TIMESTAMP TIMESTAMP - INTERVAL2
TIMESTAMP GMTTIMESTAMP - INTERVAL2
TIMESTAMP INTERVAL +, - TIMESTAMP3
GMTTIMESTAMP TIMESTAMP - INTERVAL2
GMTTIMESTAMP GMTTIMESTAMP - INTERVAL2
GMTTIMESTAMP INTERVAL +, - GMTTIMESTAMP3
INTERVAL INTEGER *, / INTERVAL4
INTERVAL FLOAT *, / INTERVAL4
INTERVAL DECIMAL *, / INTERVAL4
INTERVAL TIME + TIME3
INTERVAL GMTTIME + GMTTIME3
INTERVAL DATE + DATE3
INTERVAL TIMESTAMP + TIMESTAMP3
INTERVAL GMTTIMESTAMP + GMTTIMESTAMP3
Notes:
  1. The operand that does not match the data type of the result is cast to the data type of the result before the operation proceeds. For example, if the left operand to an addition operator is an INTEGER, and the right operand is a FLOAT, the left operand is cast to a FLOAT before the addition operation is performed.
  2. Subtracting a (GMT)TIME value from a (GMT)TIME value, a DATE value from a DATE value, or a (GMT)TIMESTAMP value from a (GMT)TIMESTAMP value, results in an INTERVAL value representing the time interval between the two operands.
  3. Adding or subtracting an INTERVAL from a (GMT)TIME, DATE or (GMT)TIMESTAMP value results in a new value of the data type of the non-INTERVAL operand, representing the point in time represented by the original non-INTERVAL, plus or minus the length of time represented by the INTERVAL.
  4. Multiplying or dividing an INTERVAL by an INTEGER, FLOAT, or DECIMAL value results in a new INTERVAL representing the length of time represented by the original, multiplied or divided by the factor represented by the non-INTERVAL operand. For example, an INTERVAL value of 2 hours 16 minutes multiplied by a FLOAT value of 2.5 results in a new INTERVAL value of 5 hours 40 minutes. The intermediate calculations involved in multiplying or dividing the original INTERVAL are carried out in the data type of the non-INTERVAL, but the individual fields of the INTERVAL (such as HOUR, YEAR, and so on) are always integral, so some rounding errors might occur.