Arithmetic
Arithmetic functions reference.
abs
Calculates the absolute value of x. Has no effect if x is of an unsigned type. If x is of a signed type, it returns an unsigned number.
Syntax
abs(x)Arguments
x— Value to get the absolute value of
Returned value
The absolute value of x
Examples
Usage example
SELECT abs(-0.5)0.5Introduced in version 1.1.
avg2
Computes and returns the average value of the provided arguments. Supports numerical and temporal types.
Syntax
avg2(x1, x2])Arguments
x1, x2]— Accepts two values for averaging.
Returned value
Returns the average value of the provided arguments, promoted to the largest compatible type.
Examples
Numeric types
SELECT avg2(toUInt8(3), 1.0) AS result, toTypeName(result) AS type;
-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.┌─result─┬─type────┐
│ 2 │ Float64 │
└────────┴─────────┘Decimal types
SELECT avg2(toDecimal32(1, 2), 2) AS result, toTypeName(result) AS type;┌─result─┬─type──────────┐
│ 1.5 │ Decimal(9, 2) │
└────────┴───────────────┘Date types
SELECT avg2(toDate('2025-01-01'), toDate('2025-01-05')) AS result, toTypeName(result) AS type;┌─────result─┬─type─┐
│ 2025-01-03 │ Date │
└────────────┴──────┘DateTime types
SELECT avg2(toDateTime('2025-01-01 00:00:00'), toDateTime('2025-01-03 12:00:00')) AS result, toTypeName(result) AS type;┌──────────────result─┬─type─────┐
│ 2025-01-02 06:00:00 │ DateTime │
└─────────────────────┴──────────┘Time64 types
SELECT avg2(toTime64('12:00:00', 0), toTime64('14:00:00', 0)) AS result, toTypeName(result) AS type;┌───result─┬─type──────┐
│ 13:00:00 │ Time64(0) │
└──────────┴───────────┘Introduced in version 25.11.
byteSwap
Reverses the bytes of an integer, i.e. changes its endianness.
The below example can be worked out in the following manner:
- Convert the base-10 integer to its equivalent hexadecimal format in big-endian format, i.e. 3351772109 -> C7 C7 FB CD (4 bytes)
- Reverse the bytes, i.e. C7 C7 FB CD -> CD FB C7 C7
- Convert the result back to an integer assuming big-endian, i.e. CD FB C7 C7 -> 3455829959 One use case of this function is reversing IPv4s:
┌─toIPv4(byteSwap(toUInt32(toIPv4('205.251.199.199'))))─┐
│ 199.199.251.205 │
└───────────────────────────────────────────────────────┘Syntax
byteSwap(x)Arguments
x— An integer value.(U)Int*
Returned value
Returns x with bytes reversed. (U)Int*
Examples
Usage example
SELECT byteSwap(3351772109)34558299598-bit
SELECT byteSwap(54)5416-bit
SELECT byteSwap(4135)1000032-bit
SELECT byteSwap(3351772109)345582995964-bit
SELECT byteSwap(123294967295)18439412204227788800Introduced in version 23.10.
divide
Calculates the quotient of two values a and b. The result type is always Float64.
Integer division is provided by the intDiv function.
:::note
Division by 0 returns inf, -inf, or nan.
:::
Syntax
divide(x, y)Arguments
x— Dividend -y— Divisor
Returned value
The quotient of x and y
Examples
Dividing two numbers
SELECT divide(25,5) AS quotient, toTypeName(quotient)5 Float64Dividing by zero
SELECT divide(25,0)infIntroduced in version 1.1.
divideDecimal
Performs division on two decimals. Result value will be of type Decimal256.
Result scale can be explicitly specified by result_scale argument (const Integer in range [0, 76]). If not specified, the result scale is the max scale of given arguments.
:::note
These function work significantly slower than usual divide.
In case you don't really need controlled precision and/or need fast computation, consider using divide.
:::
Syntax
divideDecimal(x, y[, result_scale])Arguments
x— First value: Decimal. -y— Second value: Decimal. -result_scale— Scale of result. Type Int/UInt.
Returned value
The result of division with given scale. Decimal256
Examples
Example 1
divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)┌─divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)─┐
│ -5.7142857142 │
└──────────────────────────────────────────────────────────────┘Example 2
SELECT toDecimal64(-12, 1) / toDecimal32(2.1, 1);
SELECT toDecimal64(-12, 1) as a, toDecimal32(2.1, 1) as b, divideDecimal(a, b, 1), divideDecimal(a, b, 5);┌─divide(toDecimal64(-12, 1), toDecimal32(2.1, 1))─┐
│ -5.7 │
└──────────────────────────────────────────────────┘
┌───a─┬───b─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 1)─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 5)─┐
│ -12 │ 2.1 │ -5.7 │ -5.71428 │
└─────┴─────┴────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────┘Introduced in version 22.12.
divideOrNull
Same as divide but returns NULL when dividing by zero.
Syntax
divideOrNull(x, y)Arguments
x— Dividend -y— Divisor
Returned value
The quotient of x and y, or NULL.
Examples
Dividing by zero
SELECT divideOrNull(25, 0)\NIntroduced in version 25.5.
gcd
Returns the greatest common divisor of two values a and b.
An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.
Syntax
gcd(x, y)Arguments
x— First integer -y— Second integer
Returned value
The greatest common divisor of x and y.
Examples
Usage example
SELECT gcd(12, 18)6Introduced in version 1.1.
ifNotFinite
Checks whether a floating point value is finite.
You can get a similar result by using the ternary operator: isFinite(x) ? x : y.
Syntax
ifNotFinite(x,y)Arguments
Returned value
xifxis finite.yifxis not finite.
Examples
Usage example
SELECT 1/0 AS infimum, ifNotFinite(infimum,42)inf 42Introduced in version 20.3.
intDiv
Performs an integer division of two values x by y. In other words it
computes the quotient rounded down to the next smallest integer.
The result has the same width as the dividend (the first parameter).
An exception is thrown when dividing by zero, when the quotient does not fit in the range of the dividend, or when dividing a minimal negative number by minus one.
Syntax
intDiv(x, y)Arguments
x— Left hand operand. -y— Right hand operand.
Returned value
Result of integer division of x and y
Examples
Integer division of two floats
SELECT intDiv(toFloat64(1), 0.001) AS res, toTypeName(res)┌──res─┬─toTypeName(intDiv(toFloat64(1), 0.001))─┐
│ 1000 │ Int64 │
└──────┴─────────────────────────────────────────┘Quotient does not fit in the range of the dividend
SELECT
intDiv(1, 0.001) AS res,
toTypeName(res)Received exception from server (version 23.2.1):
Code: 153. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot perform integer division, because it will produce infinite or too
large number: While processing intDiv(1, 0.001) AS res, toTypeName(res).
(ILLEGAL_DIVISION)Introduced in version 1.1.
intDivOrNull
Same as intDiv but returns NULL when dividing by zero or when dividing a
minimal negative number by minus one.
Syntax
intDivOrNull(x, y)Arguments
Returned value
Result of integer division of x and y, or NULL.
Examples
Integer division by zero
SELECT intDivOrNull(1, 0)\NDividing a minimal negative number by minus 1
SELECT intDivOrNull(-9223372036854775808, -1)\NIntroduced in version 25.5.
intDivOrZero
Same as intDiv but returns zero when dividing by zero or when dividing a
minimal negative number by minus one.
Syntax
intDivOrZero(a, b)Arguments
Returned value
Result of integer division of a and b, or zero.
Examples
Integer division by zero
SELECT intDivOrZero(1, 0)0Dividing a minimal negative number by minus 1
SELECT intDivOrZero(0.05, -1)0Introduced in version 1.1.
isFinite
Returns 1 if the Float32 or Float64 argument not infinite and not a NaN,
otherwise this function returns 0.
Syntax
isFinite(x)Arguments
x— Number to check for finiteness.Float*
Returned value
1 if x is not infinite and not NaN, otherwise 0.
Examples
Test if a number is finite
SELECT isFinite(inf)0Introduced in version 1.1.
isInfinite
Returns 1 if the Float32 or Float64 argument is infinite, otherwise this function returns 0.
Note that 0 is returned for a NaN.
Syntax
isInfinite(x)Arguments
x— Number to check for infiniteness.Float*
Returned value
1 if x is infinite, otherwise 0 (including for NaN).
Examples
Test if a number is infinite
SELECT isInfinite(inf), isInfinite(NaN), isInfinite(10))1 0 0Introduced in version 1.1.
isNaN
Returns 1 if the Float32 and Float64 argument is NaN, otherwise returns 0.
Syntax
isNaN(x)Arguments
x— Argument to evaluate for if it isNaN.Float*
Returned value
1 if NaN, otherwise 0
Examples
Usage example
SELECT isNaN(NaN)1Introduced in version 1.1.
lcm
Returns the least common multiple of two values x and y.
An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.
Syntax
lcm(x, y)Arguments
Returned value
Returns the least common multiple of x and y. (U)Int*
Examples
Usage example
SELECT lcm(6, 8)24Introduced in version 1.1.
max2
Returns the bigger of two numeric values x and y.
Syntax
max2(x, y)Arguments
x— First value(U)Int8/16/32/64orFloat*orDecimaly— Second value(U)Int8/16/32/64orFloat*orDecimal
Returned value
Returns the bigger value of x and y. Float64
Examples
Usage example
SELECT max2(-1, 2)2Introduced in version 21.11.
midpoint
Computes and returns the average value of the provided arguments. Supports numerical and temporal types.
Syntax
midpoint(x1[, x2, ...])Arguments
x1[, x2, ...]— Accepts a single value or multiple values for averaging.
Returned value
Returns the average value of the provided arguments, promoted to the largest compatible type.
Examples
Numeric types
SELECT midpoint(1, toUInt8(3), 0.5) AS result, toTypeName(result) AS type;
-- The type returned is a Float64 as the UInt8 must be promoted to 64 bit for the comparison.┌─result─┬─type────┐
│ 1.5 │ Float64 │
└────────┴─────────┘Decimal types
SELECT midpoint(toDecimal32(1.5, 2), toDecimal32(1, 1), 2) AS result, toTypeName(result) AS type;┌─result─┬─type──────────┐
│ 1.5 │ Decimal(9, 2) │
└────────┴───────────────┘Date types
SELECT midpoint(toDate('2025-01-01'), toDate('2025-01-05')) AS result, toTypeName(result) AS type;┌─────result─┬─type─┐
│ 2025-01-03 │ Date │
└────────────┴──────┘DateTime types
SELECT midpoint(toDateTime('2025-01-01 00:00:00'), toDateTime('2025-01-03 12:00:00')) AS result, toTypeName(result) AS type;┌──────────────result─┬─type─────┐
│ 2025-01-02 06:00:00 │ DateTime │
└─────────────────────┴──────────┘Time64 types
SELECT midpoint(toTime64('12:00:00', 0), toTime64('14:00:00', 0)) AS result, toTypeName(result) AS type;┌───result─┬─type──────┐
│ 13:00:00 │ Time64(0) │
└──────────┴───────────┘Introduced in version 25.11.
min2
Returns the smaller of two numeric values x and y.
Syntax
min2(x, y)Arguments
x— First value(U)Int8/16/32/64orFloat*orDecimaly— Second value(U)Int8/16/32/64orFloat*orDecimal
Returned value
Returns the smaller value of x and y. Float64
Examples
Usage example
SELECT min2(-1, 2)-1Introduced in version 21.11.
minus
Calculates the difference of two values a and b. The result is always signed.
Similar to plus, it is possible to subtract an integer from a date or date with time.
Additionally, subtraction between date with time is supported, resulting in the time difference between them.
Syntax
minus(x, y)Arguments
x— Minuend. -y— Subtrahend.
Returned value
x minus y
Examples
Subtracting two numbers
SELECT minus(10, 5)5Subtracting an integer and a date
SELECT minus(toDate('2025-01-01'),5)2024-12-27Introduced in version 1.1.
modulo
Calculates the remainder of the division of two values a by b.
The result type is an integer if both inputs are integers. If one of the inputs is a floating-point number, the result type is Float64.
The remainder is computed like in C++. Truncated division is used for negative numbers.
An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.
Syntax
modulo(a, b)Arguments
a— The dividend -b— The divisor (modulus)
Returned value
The remainder of a % b
Examples
Usage example
SELECT modulo(5, 2)1Introduced in version 1.1.
moduloOrNull
Calculates the remainder when dividing a by b. Similar to function modulo except that moduloOrNull will return NULL
if the right argument is 0.
Syntax
moduloOrNull(x, y)Arguments
Returned value
Returns the remainder of the division of x by y, or null when the divisor is zero.
Examples
moduloOrNull by zero
SELECT moduloOrNull(5, 0)\NIntroduced in version 25.5.
moduloOrZero
Like modulo but returns zero when the divisor is zero, as opposed to an exception with the modulo function.
Syntax
moduloOrZero(a, b)Arguments
Returned value
Returns the remainder of a % b, or 0 when the divisor is 0.
Examples
Usage example
SELECT moduloOrZero(5, 0)0Introduced in version 20.3.
multiply
Calculates the product of two values x and y.
Syntax
multiply(x, y)Arguments
Returned value
Returns the product of x and y
Examples
Multiplying two numbers
SELECT multiply(5,5)25Introduced in version 1.1.
multiplyDecimal
Performs multiplication on two decimals. Result value will be of type Decimal256.
Result scale can be explicitly specified by result_scale argument (const Integer in range [0, 76]). If not specified, the result scale is the max scale of given arguments.
:::note
These functions work significantly slower than usual multiply.
In case you don't really need controlled precision and/or need fast computation, consider using multiply
:::
Syntax
multiplyDecimal(a, b[, result_scale])Arguments
Returned value
The result of multiplication with the given scale. Type: Decimal256
Examples
Usage example
SELECT multiplyDecimal(toDecimal256(-12, 0), toDecimal32(-2.1, 1), 1)25.2Difference with regular multiplication
SELECT multiplyDecimal(toDecimal256(-12, 0), toDecimal32(-2.1, 1), 1)┌─multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│ -26.8609633 │
└───────────────────────────────────────────────────────────┘
┌─multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│ -26.8609 │
└──────────────────────────────────────────────────────────────────┘Decimal overflow
SELECT
toDecimal64(-12.647987876, 9) AS a,
toDecimal64(123.967645643, 9) AS b,
multiplyDecimal(a, b);
SELECT
toDecimal64(-12.647987876, 9) AS a,
toDecimal64(123.967645643, 9) AS b,
a * b;┌─────────────a─┬─────────────b─┬─multiplyDecimal(toDecimal64(-12.647987876, 9), toDecimal64(123.967645643, 9))─┐
│ -12.647987876 │ 123.967645643 │ -1567.941279108 │
└───────────────┴───────────────┴───────────────────────────────────────────────────────────────────────────────┘
Received exception from server (version 22.11.1):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Decimal math overflow:
While processing toDecimal64(-12.647987876, 9) AS a, toDecimal64(123.967645643, 9) AS b, a * b. (DECIMAL_OVERFLOW)Introduced in version 22.12.
negate
Negates the argument x. The result is always signed.
Syntax
negate(x)Arguments
x— The value to negate.
Returned value
Returns -x from x
Examples
Usage example
SELECT negate(10)-10Introduced in version 1.1.
plus
Calculates the sum of two values x and y. Alias: x + y (operator).
It is possible to add an integer and a date or date with time. The former
operation increments the number of days in the date, the latter operation
increments the number of seconds in the date with time.
Syntax
plus(x, y)Arguments
x— Left hand operand. -y— Right hand operand.
Returned value
Returns the sum of x and y
Examples
Adding two numbers
SELECT plus(5,5)10Adding an integer and a date
SELECT plus(toDate('2025-01-01'),5)2025-01-06Introduced in version 1.1.
positiveModulo
Calculates the remainder when dividing x by y. Similar to function
modulo except that positiveModulo always return non-negative number.
Syntax
positiveModulo(x, y)Arguments
x— The dividend.(U)Int*orFloat*orDecimaly— The divisor (modulus).(U)Int*orFloat*orDecimal
Returned value
Returns the difference between x and the nearest integer not greater than
x divisible by y.
Examples
Usage example
SELECT positiveModulo(-1, 10)9Introduced in version 22.11.
positiveModuloOrNull
Calculates the remainder when dividing a by b. Similar to function positiveModulo except that positiveModuloOrNull will return NULL
if the right argument is 0.
Syntax
positiveModuloOrNull(x, y)Arguments
x— The dividend.(U)Int*/Float32/64. -x— The divisor (modulus).(U)Int*/Float32/64.
Returned value
Returns the difference between x and the nearest integer not greater than
x divisible by y, null when the divisor is zero.
Examples
positiveModuloOrNull
SELECT positiveModuloOrNull(5, 0)\NIntroduced in version 25.5.