Arithmetic Functions | ClickHouse Docs

Overview

Arithmetic functions work for any two operands of type UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64.

Before performing the operation, both operands are cast to the result type. The result type is determined as follows (unless specified differently in the function documentation below):

If both operands are up to 32 bits wide, the size of the result type will be the size of the next bigger type following the bigger of the two operands (integer size promotion). For example, UInt8 + UInt16 = UInt32 or Float32 * Float32 = Float64.
If one of the operands has 64 or more bits, the size of the result type will be the same size as the bigger of the two operands. For example, UInt32 + UInt128 = UInt128 or Float32 * Float64 = Float64.
If one of the operands is signed, the result type will also be signed, otherwise it will be unsigned. For example, UInt32 * Int32 = Int64 or UInt32 * UInt32 = UInt64.

These rules make sure that the result type will be the smallest type which can represent all possible results. While this introduces a risk of overflows around the value range boundary, it ensures that calculations are performed quickly using the maximum native integer width of 64 bit. This behavior also guarantees compatibility with many other databases which provide 64 bit integers (BIGINT) as the biggest integer type.

Example:

SELECT toTypeName(0), toTypeName(0 + 0), toTypeName(0 + 0 + 0), toTypeName(0 + 0 + 0 + 0)

┌─toTypeName(0)─┬─toTypeName(plus(0, 0))─┬─toTypeName(plus(plus(0, 0), 0))─┬─toTypeName(plus(plus(plus(0, 0), 0), 0))─┐
│ UInt8         │ UInt16                 │ UInt32                          │ UInt64                                   │
└───────────────┴────────────────────────┴─────────────────────────────────┴──────────────────────────────────────────┘

Overflows are produced the same way as in C++.

abs

Introduced in: v1.1.0

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.5

avg2

Introduced in: v25.11.0

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) │
└──────────┴───────────┘

byteSwap

Introduced in: v23.10.0

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)

3455829959

8-bit

SELECT byteSwap(54)

16-bit

SELECT byteSwap(4135)

32-bit

SELECT byteSwap(3351772109)

3455829959

64-bit

SELECT byteSwap(123294967295)

18439412204227788800

divide

Introduced in: v1.1.0

Calculates the quotient of two values a and b. The result type is always Float64. Integer division is provided by the intDiv function.

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	Float64

Dividing by zero

SELECT divide(25,0)

inf

divideDecimal

Introduced in: v22.12.0

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.

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

SELECT 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(a, b, 1)─┬─divideDecimal(a, b, 5)─┐
│ -12 │ 2.1 │                   -5.7 │               -5.71428 │
└─────┴─────┴────────────────────────┴────────────────────────┘

divideOrNull

Introduced in: v25.5.0

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)

\N

gcd

Introduced in: v1.1.0

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)

ifNotFinite

Introduced in: v20.3.0

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

x — Value to check if infinite. Float*
y — Fallback value. Float*

Returned value

x if x is finite.
y if x is not finite.

Examples

Usage example

SELECT 1/0 AS infimum, ifNotFinite(infimum,42)

inf	42

intDiv

Introduced in: v1.1.0

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(res)─┐
│ 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)

intDivOrNull

Introduced in: v25.5.0

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

x — Left hand operand. (U)Int*
y — Right hand operand. (U)Int*

Returned value

Result of integer division of x and y, or NULL.

Examples

Integer division by zero

SELECT intDivOrNull(1, 0)

\N

Dividing a minimal negative number by minus 1

SELECT intDivOrNull(-9223372036854775808, -1)

\N

intDivOrZero

Introduced in: v1.1.0

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

a — Left hand operand. (U)Int*
b — Right hand operand. (U)Int*

Returned value

Result of integer division of a and b, or zero.

Examples

Integer division by zero

SELECT intDivOrZero(1, 0)

Dividing a minimal negative number by minus 1

SELECT intDivOrZero(0.05, -1)

isFinite

Introduced in: v1.1.0

Returns 1 if the Float32 or Float64, or BFloat16 argument not infinite and not a NaN, otherwise this function returns 0.

Syntax

isFinite(x)

Arguments

x — Number to check for finiteness. Float* or BFloat16

Returned value

1 if x is not infinite and not NaN, otherwise 0.

Examples

Test if a number is finite

SELECT isFinite(inf)

isInfinite

Introduced in: v1.1.0

Returns 1 if the Float32 or Float64, or BFloat16 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* or BFloat16

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	0

isNaN

Introduced in: v1.1.0

Returns 1 if the Float32 or Float64, or BFloat16 argument is NaN, otherwise returns 0.

Syntax

isNaN(x)

Arguments

x — Argument to evaluate for if it is NaN. Float* or BFloat16

Returned value

1 if NaN, otherwise 0

Examples

Usage example

SELECT isNaN(NaN)

kqlBin

Introduced in: v26.8.0

Rounds a value down to a multiple of roundTo, as the Kusto Query Language’s bin() does.

The rule depends on the argument types: a number is rounded arithmetically, a timespan (which is an Interval) is rounded by a timespan, and a datetime is rounded by a timespan. A KQL datetime is a DateTime64; the narrower DateTime and Date carriers are rejected, because they cannot represent every bin a KQL datetime can produce.

This function backs bin() when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlBin(value, roundTo)

Arguments

value — A number, a timespan, or a datetime (a DateTime64). - roundTo — The bin size.

Returned value

value rounded down to the nearest multiple of roundTo.

Examples

number

SELECT kqlBin(4.5, 1)

timespan

SELECT kqlBin(toIntervalNanosecond(16 * 86400000000000), toIntervalNanosecond(7 * 86400000000000))

1209600000000000

datetime

SELECT kqlBin(toDateTime64('2026-08-01 12:34:56', 7, 'UTC'), toIntervalHour(1))

2026-08-01 12:00:00.0000000

kqlBinAt

Introduced in: v26.8.0

Rounds a value down to a multiple of binSize counted from fixedPoint, as the Kusto Query Language’s bin_at() does. The bins may align before or after the fixed point.

The rule depends on the argument types: a number is rounded arithmetically, a timespan (which is an Interval) is rounded by a timespan from a timespan, and a datetime is rounded by a timespan counted from a datetime fixed point. A KQL datetime is a DateTime64; the narrower DateTime and Date carriers are rejected, because they cannot represent every bin a KQL datetime can produce.

This function backs bin_at() when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlBinAt(value, binSize, fixedPoint)

Arguments

value — A number, a timespan, or a datetime (a DateTime64). - binSize — The bin size. - fixedPoint — The point the bins are counted from.

Returned value

value rounded down to the nearest multiple of binSize counted from fixedPoint.

Examples

number

SELECT kqlBinAt(6.5, 2.5, -0.5)

4.5

datetime

SELECT kqlBinAt(toDateTime64('2026-08-01 12:34:56', 7, 'UTC'), toIntervalHour(1), toDateTime64('2026-08-01 00:30:00', 7, 'UTC'))

2026-08-01 12:30:00.0000000

kqlDateTimeBinAt

Introduced in: v26.8.0

Rounds a datetime down to a timespan multiple counted from a datetime fixed point.

Syntax

kqlDateTimeBinAt(value, binSize, fixedPoint)

Arguments

value — The datetime to round. - binSize — The timespan bin size. - fixedPoint — The datetime fixed point.

Returned value

The rounded datetime.

Examples

kqlDivide

Introduced in: v26.8.0

Division as the Kusto Query Language defines it: two integer operands divide to an integer, so 7 / 2 is 3, and two timespan operands (which are Interval values) divide to their real-valued ratio, so 15ms / 10ms is 1.5. Any other combination of operand types divides as divide does.

This function backs the / operator when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlDivide(x, y)

Arguments

x — The dividend. - y — The divisor.

Returned value

intDiv(x, y) when both arguments are integers, the ratio of the intervals’ ticks when both are intervals, divide(x, y) otherwise.

Examples

integers

SELECT kqlDivide(7, 2)

reals

SELECT kqlDivide(7.0, 2)

3.5

timespans

SELECT kqlDivide(toIntervalNanosecond(15000000), toIntervalNanosecond(10000000))

1.5

kqlMultiply

Introduced in: v26.8.0

Multiplication as the Kusto Query Language defines it: a timespan (an Interval) scales by a number on either side, so 2 * 1h is two hours. Two arguments without an interval multiply as multiply does.

This function backs the * operator when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlMultiply(x, y)

Arguments

x — A number or a timespan. - y — A number, or a timespan when x is a number.

Returned value

The product; an interval of the same kind when either argument is one.

Examples

timespan

SELECT kqlMultiply(2, toIntervalNanosecond(3600000000000))

7200000000000

numbers

SELECT kqlMultiply(6, 7)

kqlRangeCount

Introduced in: v26.8.0

The number of rows the range source of the Kusto Query Language produces: floor((to - from) / step) + 1, and never less than zero. The bounds and the step are numbers, or datetimes stepped by a timespan (an Interval), or timespans; the temporal forms are counted in integer nanoseconds, which no single ClickHouse division expresses. Integers and decimals are counted exactly, not through Float64.

This function backs the range source when dialect = 'kusto'. It is not meant to be called directly from SQL.

Syntax

kqlRangeCount(from, to, step)

Arguments

from — The first value of the range. - to — The value the range does not go past. - step — The difference between two consecutive values.

Returned value

The number of values in the range.

Examples

numbers

SELECT kqlRangeCount(1, 7, 2)

datetimes

SELECT kqlRangeCount(toDateTime64('2026-08-01 00:00:00', 7, 'UTC'), toDateTime64('2026-08-01 12:00:00', 7, 'UTC'), toIntervalHour(5))

lcm

Introduced in: v1.1.0

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

x — First integer. (U)Int*
y — Second integer. (U)Int*

Returned value

Returns the least common multiple of x and y. (U)Int*

Examples

Usage example

SELECT lcm(6, 8)

max2

Introduced in: v21.11.0

Returns the bigger of two numeric values x and y.

Syntax

max2(x, y)

Arguments

x — First value (U)Int8/16/32/64 or Float* or BFloat16 or Decimal
y — Second value (U)Int8/16/32/64 or Float* or BFloat16 or Decimal

Returned value

Returns the bigger value of x and y. Float64

Examples

Usage example

SELECT max2(-1, 2)

midpoint

Introduced in: v25.11.0

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) │
└──────────┴───────────┘

min2

Introduced in: v21.11.0

Returns the smaller of two numeric values x and y.

Syntax

min2(x, y)

Arguments

x — First value (U)Int8/16/32/64 or Float* or BFloat16 or Decimal
y — Second value (U)Int8/16/32/64 or Float* or BFloat16 or Decimal

Returned value

Returns the smaller value of x and y. Float64

Examples

Usage example

SELECT min2(-1, 2)

-1

minus

Introduced in: v1.1.0

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. It is also possible to subtract a Time or Time64 from a DateTime or DateTime64; the time value is applied as an offset in seconds. DateTime minus Time produces a DateTime, any combination involving DateTime64 or Time64 produces a DateTime64 with the maximum scale of the two arguments.

Syntax

minus(x, y)

Arguments

x — Minuend. - y — Subtrahend.

Returned value

x minus y

Examples

Subtracting two numbers

SELECT minus(10, 5)

Subtracting an integer and a date

SELECT minus(toDate('2025-01-01'),5)

2024-12-27

modulo

Introduced in: v1.1.0

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)

Aliases: mod

Arguments

a — The dividend - b — The divisor (modulus)

Returned value

The remainder of a % b

Examples

Usage example

SELECT modulo(5, 2)

moduloLegacy

Introduced in: v1.1.0

Calculates the remainder of a division. This is the legacy modulo implementation that uses the C++ % operator, which may produce negative results for negative arguments. This function exists for backward compatibility with old table partitioning logic. Use modulo or positiveModulo for standard behavior.

Syntax

moduloLegacy(a, b)

Arguments

a — The dividend. (U)Int* or Float*
b — The divisor. (U)Int* or Float*

Returned value

Returns the remainder of the division. (U)Int* or Float*

Examples

Basic usage

SELECT moduloLegacy(10, 3)

moduloOrNull

Introduced in: v25.5.0

Calculates the remainder when dividing a by b. Similar to function modulo except that moduloOrNull returns NULL when the operation would otherwise raise a floating-point exception. For floating-point arguments this happens only when the divisor is 0; for integer arguments it additionally covers the minimal negative value modulo -1 (e.g. -128 % -1 for Int8).

Syntax

moduloOrNull(x, y)

Aliases: modOrNull

Arguments

x — The dividend. (U)Int* or Float*
y — The divisor (modulus). (U)Int* or Float*

Returned value

Returns the remainder of the division of x by y, or NULL when the operation would raise a floating-point exception: when the divisor is zero, or, for integer arguments, when computing the minimal negative value modulo -1.

Examples

moduloOrNull by zero

SELECT moduloOrNull(5, 0)

\N

moduloOrNull of the minimal negative integer by -1

SELECT moduloOrNull(toInt8(-128), toInt8(-1))

\N

moduloOrZero

Introduced in: v20.3.0

Like modulo but returns zero when the divisor is zero, as opposed to an exception with the modulo function.

Syntax

moduloOrZero(a, b)

Arguments

a — The dividend. (U)Int* or Float*
b — The divisor (modulus). (U)Int* or Float*

Returned value

Returns the remainder of a % b, or 0 when the divisor is 0.

Examples

Usage example

SELECT moduloOrZero(5, 0)

multiply

Introduced in: v1.1.0

Calculates the product of two values x and y.

Syntax

multiply(x, y)

Arguments

x — factor. (U)Int* or Float* or Decimal
y — factor. (U)Int* or Float* or Decimal

Returned value

Returns the product of x and y

Examples

Multiplying two numbers

SELECT multiply(5,5)

multiplyDecimal

Introduced in: v22.12.0

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.

Syntax

multiplyDecimal(a, b[, result_scale])

Arguments

a — First value. Decimal
b — Second value. Decimal
result_scale — Scale of result. (U)Int*

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.2

Difference with regular multiplication

SELECT multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4));
SELECT multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4));

┌─multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                               -26.8609633 │
└───────────────────────────────────────────────────────────┘
┌─multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                                         -26.8609 │
└──────────────────────────────────────────────────────────────────┘

No overflow with multiplyDecimal

SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    multiplyDecimal(a, b);

┌─────────────a─┬─────────────b─┬─multiplyDecimal(a, b)─┐
│ -12.647987876 │ 123.967645643 │       -1567.941279108 │
└───────────────┴───────────────┴───────────────────────┘

Decimal overflow with regular multiplication

SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    a * b;

Received exception:
Code: 407. DB::Exception: Decimal math overflow. (DECIMAL_OVERFLOW)

negate

Introduced in: v1.1.0

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)

-10

plus

Introduced in: v1.1.0

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. It is also possible to add a date and a time. Adding a Date and a Time produces a DateTime. Adding a Date and a Time64, or a Date32 and a Time or Time64, produces a DateTime64. Adding a Time or Time64 to a DateTime or DateTime64 applies the time value as an offset in seconds. DateTime plus Time produces a DateTime, any combination involving DateTime64 or Time64 produces a DateTime64 with the maximum scale of the two arguments.

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)

Adding an integer and a date

SELECT plus(toDate('2025-01-01'),5)

2025-01-06

Adding a date and time

SELECT toDate('2025-01-01') + CAST('14:30:25', 'Time')

2025-01-01 14:30:25

positiveModulo

Introduced in: v22.11.0

Calculates the remainder when dividing x by y. Similar to function modulo except that positiveModulo always return non-negative number.

Syntax

positiveModulo(x, y)

Aliases: positive_modulo, pmod

Arguments

x — The dividend. (U)Int* or Float* or Decimal
y — The divisor (modulus). (U)Int* or Float* or Decimal

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)

positiveModuloOrNull

Introduced in: v25.5.0

Calculates the remainder when dividing a by b. Similar to function positiveModulo except that positiveModuloOrNull returns NULL when the operation would otherwise raise a floating-point exception. For floating-point arguments this happens only when the divisor is 0; for integer arguments it additionally covers the minimal negative value modulo -1 (e.g. -128 % -1 for Int8).

Syntax

positiveModuloOrNull(x, y)

Aliases: positive_modulo_or_null, pmodOrNull

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, or NULL when the operation would raise a floating-point exception: when the divisor is zero, or, for integer arguments, when computing the minimal negative value modulo -1.

Examples

positiveModuloOrNull by zero

SELECT positiveModuloOrNull(5, 0)

\N

positiveModuloOrNull of the minimal negative integer by -1

SELECT positiveModuloOrNull(toInt8(-128), toInt8(-1))

\N

sqr

Introduced in: v26.7.0

Calculates the square of a value x.

Syntax

sqr(x)

Arguments

x — Value to square. (U)Int* or Float* or Decimal

Returned value

Returns the product of x multiplied by itself.

Examples

Squaring a number

SELECT sqr(5)