Overview
Learn general aspects of arithmetic operations.
Arithmetic operations in CQL follow well-defined rules depending on whether you are working with numeric or temporal values.
Numeric arithmetic
The following sections discuss the behavior of arithmetic operations when working with numeric values.
Numeric conversion
For your convenience, all numeric operations automatically convert non-numeric values to numbers whenever possible.
For example, consider the following operation:
1 + "2"
Without type conversion, you would have to handle this case manually, which would make your queries more verbose and less readable.
Note that automatic conversions are only applied in a limited number of cases. This is one of the reasons why there is no such thing as a not-a-number value (NaN) in CQL. Whenever a value cannot be converted to a number, the operation simply fails.
For more information, see number conversion.
Type generalization
For operations involving multiple numeric operands, CQL automatically converts the values to a common type before performing the calculation. This process is called generalization, and consists of converting all operands to the most specific type that can represent them all.
Here is how generalization works in a nutshell:
- If some of the operands are floats, the result is a float.
- If some of the operands are decimals, the result is a decimal.
- If all the operands are integers, the result is an integer.
Therefore, if you add a float and a decimal, the result is a float. This is because floats can represent all decimals but not vice versa. Similarly, if you add an integer and a decimal, the result is a decimal.
Overflow handling
Arithmetic operations can sometimes result in a value that exceeds the maximum or minimum representable value. This is called an overflow.
Float and decimal values have no upper or lower limits but maximum precision. This means that the result of an operation may not be accurate enough to represent the exact value, but the operation will not fail.
However, when working with integers, the behavior is slightly different. If the result is outside the valid range, it is approximated to the nearest float. This is a compromise to ensure that you still get a result, even if it is less accurate.
Temporal arithmetic
The following sections discuss the behavior of arithmetic operations when working with temporal values.
Operation chaining
When performing arithmetic operations on temporal values, you can add multiple time spans sequentially, as long as the left side of the operation is a date-time value.
This works because addition and subtraction operations are left-associative, which means that the leftmost operand is evaluated first, and then the result is used as the left operand for the next operation, and so on.
For example, the following expression is perfectly valid:
today + 2 days + 1 month
The leftmost sub-expression today + 2 days is evaluated first, resulting in a new date. Finally, the second sub-expression + 1 month is evaluated, yielding the final result. As you can see, the time spans are never combined.
It becomes clearer when we add parentheses to the expression:
(today + 2 days) + 1 month
In contrast, the following expression is invalid:
2 days + 1 month + today // 🚨 Invalid
If this expression were allowed, it would first add the time spans together, resulting in a combined period that is then added to the date. However, this could be misleading because it may not produce the same result as the previous example.
To see how this can lead to unexpected results, consider the following example:
today + 1 month + 2 days
The number of days per month varies, and daylight saving time can also affect the calculation of dates. As a result, the final date may differ depending on the order in which you add days and months.
For example, let's say today is August 30. If you add a month to this date first, you will end up with September 30. If you then add two more days, the date becomes October 2.
However, if you switch the order and first add two days to August 30, you will get September 1. Then, if you add a month to that date, you get October 1.
So, when chaining units together, make sure to consider the order in which they are combined and how this may affect the result.
Overflow handling
Temporal values, such as dates and times, have upper and lower limits that define the range of possible values.
The following table shows the valid range for each temporal type:
Type | Minimum value | Maximum value |
---|---|---|
Time | 00:00:00 | 23:59:59 |
Date | -999999999-01-01 | 999999999-12-31 |
Date time | -999999999-01-01T00:00:00 | 999999999-12-31T23:59:59 |
It is unlikely that you will ever need to worry about these limits. However, be aware that any operation that results in a value outside this range will fail.