Arithmetic operations

CQL offers a range of arithmetic operations for manipulating numeric and temporal values. These include simple operations like addition and subtraction and more complex operations involving dates and times.

In this reference, we explore the available operations, showcase practical examples, and provide a comprehensive guide to the syntax and semantics of each operation.

Operations

This is the summary of the available arithmetic operators for quick reference:

In the following sections, we visit each of these operations in detail, including their syntax, semantics, and practical examples.

Minus

The minus operation negates a numeric value. You can use it in scenarios where you need to change the sign of a number from positive to negative or vice versa.

You can use the minus operator by placing a - sign before a numeric value:

-/*<operand>*/

Here is a practical example:

-42

Plus

The plus operation indicates that a numeric value is positive. You can use it when you want to specify the sign of a number explicitly or convert a value implicitly to a numeric type.

You can use the plus operator by placing a + sign before a numeric value:

+/*<operand>*/

Here is a practical example:

+42

Note that, since it was already a number, the operation had no effect except to indicate the number's sign.

Percent

The percent operation converts a number from percentage to decimal format by dividing it by 100.

To use the percent operator, place a % sign after a number, variable, or expression:

/*<operand>*/%

Here is a practical example:

51% // 0.51

Addition

The addition operation adds two operands together, either numeric or temporal values, and returns the sum.

To use the addition operation, place a + sign between two numeric values:

/*<operand>*/ + /*<operand>*/

Alternatively, for a more natural language feel, you can write it as follows:

/*<operand>*/ plus /*<operand>*/

Here is a practical example:

5 + 3 // 8

For temporal values, the addition operation allows you to perform calculations involving dates, times, and durations:

today + 1 day

The example above adds one day to the current date, resulting in the date of tomorrow. And you can also chain multiple operations together to perform more complex calculations:

today + 1 month + 1 week

Because the addition operation is commutative, the order of the operands does not matter. This is confirmed by the expression below:

1 day + today is equal to today + 1 day

Note that the addition operation does not automatically convert temporal values. Be sure to use the cast modifier to convert a value to a date or time before adding it to another value, as shown below:

"2021-01-01" as date + 1 day

Subtraction

The subtraction operation calculates the difference between two numeric or temporal operands and returns the difference.

To use the subtraction operation, place a - sign between two numeric values:

/*<operand>*/ - /*<operand>*/

Alternatively, for a more natural language feel, you can write it as follows:

/*<operand>*/ minus /*<operand>*/

Here is a practical example:

5 - 3 // 2

For temporal values, the subtraction operation allows you to perform calculations involving dates, times, and durations:

today - 1 day

The above example subtracts one day from the current date, resulting in the date of yesterday. You can chain multiple operations together to perform more complex calculations:

today - 1 month - 1 week

In contrast to addition, subtraction is not commutative. This means that you cannot subtract a date from a period of time, but you can subtract a period of time from a date. For example, today - 1 day works, but not 1 day - today.

Finally, note that the subtraction operation does not perform any automatic conversions for temporal values. Be sure to use the cast modifier to convert a value to a date or time before subtracting it from another value, as shown below:

"2021-01-01" as date - 1 day

Multiplication

The multiplication operation multiplies two numeric values and returns the product.

To use the multiplication operator, place a * sign between two numeric values:

/*<operand>*/ * /*<operand>*/

For a more natural reading, you can alternatively write:

/*<operand>*/ times /*<operand>*/

Here is a practical example:

2 * 3 // 6

Because the multiplication operation is commutative, the order of the operands does not matter. This is confirmed by the expression below:

2 * 3 is equal to 3 * 2

Note that multiplication has higher precedence than addition and subtraction. This means that multiplication is performed before those operations, as shown below:

2 + 3 * 4 // 14, not 20

Division

The division operation divides one numeric value by another and returns the quotient.

To use the division operator, place a / sign between two numeric values:

/*<operand>*/ / /*<operand>*/

For a more natural reading, you can alternatively write:

/*<operand>*/ divided by /*<operand>*/

Here is a practical example:

6 / 2 // 3

Note that division has the same precedence as multiplication, which is higher than addition and subtraction. This means that division is performed before those operations, as shown below:

2 + 6 / 2 // 5, not 4

Modulo

The modulo operation calculates the modulus of a division, which is the rest of the division that cannot be expressed as an exact multiple of the divisor.

To use the modulo operator, place the mod keyword between two numeric values:

/*<operand>*/ mod /*<operand>*/

You can also use the full name of the operation if you prefer:

/*<operand>*/ modulo /*<operand>*/

Here is a practical example:

7 mod 3 // 1

In the above example, the number 7 cannot be completely divided by 3, and there is a remainder of 1.

It is important to emphasize that modulo calculates the modulus and not the remainder. Although they may appear to be the same in many cases, there is a difference when dealing with negative numbers.

Imagine a scenario where you are dividing -7 by 3:

• The remainder would be -1, as this is the difference between -7 and -6, which is the nearest multiple of 3 that is less than -7.
• The modulus would be 2, as this is the difference between -7 and -9, which is the nearest multiple of 3 that is greater than -7.

Since CQL uses the modulus definition, the result of the above operation is 2.

Exponentiation

The exponentiation operation raises a number to a power and returns the result.

To use the exponentiation operator, place a ** sign between two numeric values:

/*<operand>*/ ** /*<operand>*/

For a more natural reading, you can alternatively write:

/*<operand>*/ to power of /*<operand>*/

Here is a practical example:

2 ** 3 // 8

Note that exponentiation has higher precedence than multiplication, division, addition, and subtraction. This means that exponentiation is performed before those operations, as shown below:

2 + 3 ** 2 // 11, not 25

Also, note that exponentiation has different associativity than the other arithmetic operations. This means that exponentiation is performed from right to left, as shown below:

2 ** 3 ** 2 // 512, not 64

General aspects

This section delves into the general aspects of arithmetic operations in CQL, highlighting the subtle differences in their behavior across various scenarios.

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:

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.