Numeric functions

This page describes the available functions to assist with performing numeric calculations.

abs

abs(value) return the absolute value. The behavior of abs is as follows:

• When the input value is positive, abs returns value
• When the input value is negative, abs returns - value
• When the input value is 0, abs returns 0

Arguments:

• value is any numeric value.

Return value:

Return value type is the same as the type of the argument.

Examples:

SELECT
    x - 2 a,
    abs(x -2)
FROM long_sequence(3);

ceil / ceiling

ceil(value) or ceiling(value) returns the smallest integer greater than, or equal to, a specified numeric expression.

Arguments:

• value is any numeric value.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT ceil(15.75) as RoundedUp;

exp

exp(value) returns the exponential value of a specified numeric expression.

Arguments:

• value is any numeric value, except decimal.

Return value:

Return value type is double.

Examples:

SELECT exp(2) as Exponent;

floor

floor(value) returns the largest integer less than or equal to a specified numeric expression.

Arguments:

• value is any numeric value.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT floor(15.75) as RoundedDown;

greatest

greatest(args...) returns the largest entry in a series of numbers.

null will be returned only if all of the arguments are null.

Arguments:

• args... is a variable-size list of long, double or decimal values.

Return value:

Return value type is double, long or decimal.

Examples:

SELECT greatest(11, 3, 8, 15)

least

least(args...) returns the smallest entry in a series of numbers.

null will be returned only if all of the arguments are null.

Arguments:

• args... is a variable-size list of long, double or decimal values.

Return value:

Return value type is double, long or decimal.

Examples:

SELECT least(11, 3, 8, 15)

ln

ln(value) return the natural logarithm (loge) of a given number.

Arguments:

• value is any numeric value, except decimal.

Return value:

Return value type is double.

Examples:

SELECT ln(4.123)

log

log(value) return the base 10 logarithm of a given number.

Arguments:

• value is any numeric value, except decimal.

Return value:

Return value type is double.

Examples:

SELECT log(100)

:::note

Some databases use LOG to refer to the natural logarithm and LOG10 for the base 10 logarithm. QuestDB follows PostgreSQL conventions and uses LOG for base 10 and LN for natural logarithm.

:::

power

power(base, exponent) returns the value of a number base raised to the power defined by exponent.

Arguments:

• base is any numeric value, except decimal.
• exponent is any numeric value, except decimal.

Return value:

Return value type is double.

Examples:

SELECT power(2, 3);

round

round(value, scale) returns the closest value in the specified scale. It uses the "half up" tie-breaking method when the value is exactly halfway between the round_up and round_down values.

round(value) is equivalent to round(value, 0).

Arguments:

• value is any numeric value.
• scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT
    d,
    round(d, -2),
    round(d, -1),
    round(d,0),
    round(d,1),
    round(d,2)
FROM dbl;

round_down

round_down(value, scale) - rounds a value down to the specified scale

Arguments:

• value is any numeric value.
• scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT
    d,
    round_down(d, -2),
    round_down(d, -1),
    round_down(d,0),
    round_down(d,1),
    round_down(d,2)
FROM dbl;

round_half_even

round_half_even(value, scale) - returns the closest value in the specified scale. It uses the "half even" tie-breaking method when the value is exactly halfway between the round_up and round_down values.

Arguments:

• value is any numeric value.
• scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT
    round_half_even(5.55, 1),
    round_half_even(5.65, 1)
FROM long_sequence(1);

SELECT
    d,
    round_half_even(d, -2),
    round_half_even(d, -1),
    round_half_even(d,0),
    round_half_even(d,1),
    round_half_even(d,2)
FROM dbl;

round_up

round_up(value, scale) - rounds a value up to the specified scale

Arguments:

• value is any numeric value.
• scale is the number of decimal points returned. A negative scale means the rounding will occur to a digit to the left of the decimal point. For example, -1 means the number will be rounded to the nearest tens and +1 to the nearest tenths.

Return value:

Returns double, or decimal if the operand is decimal.

Examples:

SELECT
    d,
    round_up(d, -2),
    round_up(d, -1),
    round_up(d,0),
    round_up(d,1),
    round_up(d,2)
FROM dbl;

sign

sign(value) returns sign of the argument, that is:

• -1 for negative value
• 0 for zero
• +1 for positive value

Arguments:

• value is any numeric value.

Return value:

Return value type is the same as argument's.

Examples:

SELECT x-3 arg, sign(x-3) from long_sequence(5)

size_pretty

size_pretty(value) returns a human-readable string equivalent to the input value.

Arguments:

• value is a long value that represents size in bytes.

Return value:

Return value type is string. The string contains the size as a floating point with one significant figure followed by the scale in base 1024.

Examples:

SELECT size_pretty(400032);

sqrt

sqrt(value) return the square root of a given number.

Arguments:

• value is any numeric value, except decimal.

Return value:

Return value type is double.

Examples:

SELECT sqrt(4000.32)