The following are the built-in arithmetic operators available in the MSQL language.
They are divided into three groups:
+, - and binary +, -, *, /, % operators.&, OR |, XOR ^ and NOT ~ operators, bit-shift-left << and bit-shift-right >> operators.** and absolute value operator @.+ (x: int64) -> int64+ (x: float64) -> float64+ (x: timeperiod)Returns the given operand unchanged. Available just for consistency.
Return type
If the operand is an integer, the result will have the int64 type. If the operand is a floating-point number, the result will be a float64. timeperiod operands are unchanged. This upcasting is the only actual thing this operator does.
Null handling
If the operand is NULL, the result will also be NULL.
Examples
SELECT +(10);
--> 10
SELECT +(-3.5);
--> -3.5
- (x: int64) -> int64- (x: float64) -> float64- (p: timeperiod) -> timeperiodReturns the operand with its sign inverted.
Return type
If the operand is an integer, the result will have the int64 type. If the operand is a floating-point number, the result will be a float64. If the operand is a timeperiod, the result will also be a timeperiod.
Null handling
If the operand is NULL, the result will also be NULL.
Notes
The - operator can produce an overflow if the operand was a 64-bit unsigned integer, e.g. 2**64 - 1.
Examples
SELECT -(10);
--> -10
SELECT -(-3.5);
--> 3.5
SELECT -(timeperiod '3h');
--> -10800::timeperiod
See also
(x: int64) + (y: int64) -> int64(x: float64) + (y: float64) -> float64(t: timestamp) + (p: timeperiod) -> timestamp(p: timeperiod) + (t: timestamp) -> timestamp(p1: timeperiod) + (p2: timeperiod) -> timeperiod(p1: point) + (p2: point) -> pointAdds two numbers/timeperiods/points, or a timestamp and a timeperiod.
Return type
In case of numeric operands, if both operands are integers, the result will be an int64, otherwise a float64. Adding a timeperiod to a timestamp produces a timestamp.
Null handling
If any of the operands is NULL, the result will also be NULL.
Notes
When the arguments are point values, they're summed as 2D-vectors, i.e. their coordinates are summed independently.
The result of the operation might overflow/underflow and in that case it would be incorrect.
Adding months or years to a timestamp isn't possible using this operator as timeperiods cannot be specified in months or years.
For this purpose there is a special function called TS_ADDM.
Examples
SELECT 2 + 2;
--> 4
SELECT 3.5 + (-5.2);
--> -1.7
SELECT timestamp '2020-01-15' + timeperiod '20d';
--> '2020-02-04 00:00:00.000000'::timestamp
SELECT timeperiod '3h' + timeperiod '25min';
--> 12300::timeperiod
SELECT point '(10 20)' + point '(30 -10)';
--> POINT(40 10)
See also
(x: int64) - (y: int64) -> int64(x: float64) - (y: float64) -> float64(t1: timestamp) - (t2: timestamp) -> timeperiod(t: timestamp) - (p: timeperiod) -> timestamp(p1: timeperiod) - (p2: timeperiod) -> timeperiod(p1: point) - (p2: point) -> pointSubtracts one number/timestamp/timeperiod/point from another, or a timeperiod from a timestamp.
Return type
In case of numeric operands, if both operands are integers, the result will be an int64, otherwise a float64. Subtracting two timestamp values or two timeperiod values produces a timeperiod. Subtracting a timeperiod from a timestamp produces a timestamp.
Null handling
If any of the operands is NULL, the result will also be NULL.
Notes
The result of the operation might overflow/underflow and in that case it would be incorrect.
Subtracting months or years from a timestamp isn't possible using this operator as timeperiods cannot be specified in months or years.
For this purpose there is a special function called TS_ADDM.
Examples
SELECT 2 - 3;
--> -1
SELECT 3.5 - (-5.2);
--> 8.7
SELECT timestamp '2020-01-15' - timestamp '2020-01-14';
--> 86400::timeperiod
SELECT timestamp '2020-01-15 08:00:00' - timeperiod '5h';
--> '2020-01-15 03:00:00.000000'::timestamp
SELECT timeperiod '3h' - timeperiod '25min';
--> 9300::timeperiod
See also
(x: int64) * (y: int64) -> int64(x: float64) * (y: float64) -> float64(n: float64) * (p: timeperiod) -> timeperiod(p: timeperiod) * (n: float64) -> timeperiodMultiplies two numbers, or a timeperiod by a number.
Return type
If any of the operands is a timeperiod, the result will also be a timeperiod. Else, if both operands are integers, the result will be an int64, otherwise a float64.
Null handling
If any of the operands is NULL, the result will also be NULL.
Notes
The result of the operation might overflow/underflow and in that case it would be incorrect.
Examples
SELECT 5*5;
--> 25
SELECT -1.5 * 3.5;
--> -5.25
SELECT 10 * timeperiod '3 hours';
--> 108000::timeperiod
See also
(x: int64) / (y: int64) -> int64(x: float64) / (y: float64) -> float64(p: timeperiod) / (n: numeric) -> timeperiod(p1: timeperiod) / (p2: timeperiod) -> float64Divides one number by the other. Scales a timeperiod by division with a floating-point number, or calculates a ratio between two timeperiods.
Notes
If both operands are integers, the result will be an int64. If any (or both) operands are floating-point, the result will be a float64.
:null-handling: simple-op
:error-handling: When the second operand is 0, the operation will return an error: Division by zero.
:error-handling: If instead of an error, a NULL result is desired, this can be accomplished using the NULLIF function.
:example: SELECT 10 / 3; :example-result: 3
:example: SELECT 2.25 / 0.5; :example-result: 4.5
:example: SELECT timeperiod '25 minutes' / 3; :example-result: 500::timeperiod
:example: SELECT timeperiod '5 minutes' / timeperiod '42 seconds'; :example-result: 7.142857
:see-also: multiplication-operator, remainder-operator, modulo-operator, timeperiod_division-operator
As timeperiods are internally stored as int64 values, there is a possibility of overflow when performing arithmetic operations with them.
(x: int64) % (y: int64) -> int64Calculates the remainder of division of x by y.
Arguments
The modulo operator accepts only integral operands (as the operation is not well-defined for real numbers).
Return type
The result is always of the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Error handling
When the second operand is 0, the operation will return an error: Division by zero.
If instead of an error, a NULL result is desired, this can be accomplished using the NULLIF function.
Notes
Unlike the MOD operator, the result of the operation will always have the same sign as the first operand (dividend).
Examples
SELECT 10 % 3;
--> 1
SELECT -5 % 2;
--> -1
SELECT 5 % -2;
--> 1
See also
(x: int64) MOD (y: int64) -> int64Calculates the modulo of x by y.
Arguments
The modulo operator accepts only integral operands (as the operation is not well-defined for real numbers).
Return type
The result is always of the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Error handling
When the second operand is 0, the operation will return an error: Division by zero.
If instead of an error, a NULL result is desired, this can be accomplished using the NULLIF function.
Notes
Unlike the % operator, the result of the operation will always have the same sign as the second operand (divisor).
Examples
SELECT 10 MOD 3;
--> 1
SELECT -5 MOD 2;
--> 1
SELECT 5 MOD -2;
--> -1
See also
(x: int64) & (y: int64) -> int64Computes the bitwise AND of the operands.
Return type
The operands and the result are always upcasted to the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Examples
SELECT 45 & 31;
--> 13
(x: int64) | (y: int64) -> int64Computes the bitwise OR of the operands.
Return type
The operands and the result are always upcasted to the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Examples
SELECT 45 | 31;
--> 63
(x: int64) ^ (y: int64) -> int64Computes the bitwise XOR of the operands.
Return type
The operands and the result are always upcasted to the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Examples
SELECT 45 ^ 31;
--> 50
~ (x: int64) -> int64Returns the operand with its every bit inverted.
Return type
The operand and the result are always upcasted to the int64 type.
Null handling
If the operand is NULL, the result will also be NULL.
Examples
SELECT ~15;
--> -16
(x: int64) << (n: int64) -> int64Returns the left operand shifted left by the number of bits specified in the right operand.
Return type
The operands and the result are always upcasted to the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Examples
SELECT 10 << 4;
--> 160
(x: int64) >> (n: int64) -> int64Returns the left operand shifted right by the number of bits specified in the right operand.
Return type
The operands and the result are always upcasted to the int64 type.
Null handling
If any of the operands is NULL, the result will also be NULL.
Notes
This operator performs the arithmetic right shift, i.e. it keeps the sign of the number intact (see the second example).
Examples
SELECT 10 >> 2;
--> 2
SELECT -10 >> 2;
--> -3
(x: float64) ** (y: float64) -> float64Returns the result of raising the first operand to the power of second.
Null handling
If any of the operands is NULL, the result will also be NULL.
Notes
The ** operator is just a shorthand for the POWER function.
All the specificities of the POWER function apply here as well (e.g. possible errors and return values for specific arguments).
Examples
SELECT 2**2;
--> 4.0
SELECT 2**-1;
--> 0.5
(r1: range[T]) & (r2: range[T]) -> range[T]Range intersection
Notes
This operator is not yet implemented.
(r1: range[T]) | (r2: range[T]) -> multirange[T]Range union
Notes
This operator is not yet implemented.
(r1: range[T]) / (r2: range[T]) -> multirange[T]Range difference
Notes
This operator is not yet implemented.