Data Types Commands Conditional Expressions Type Functions Aggregate Functions String Functions Mathematical Functions Date And Time Functions Geographic Functions Array Functions Object Functions Hash Functions Text Search Functions Window Functions

Mathematical Functions

This page covers mathematical operators and functions available in Rockset.

All functions accept arguments of types int and float. The return type is float, unless otherwise specified.

Note: A null argument to any mathematical function will result in a null return value

Operators

Rockset supports the basic arithmetic operators shown below on any combination of int and float values.

Operator	Description
`+ x`	positive sign
`- x`	negative sign
`x + y`	addition
`x - y`	subtraction
`x * y`	multiplication
`x / y`	division
`x % y`	modulo (remainder)

Basic Functions

ABS

ABS(x)

Returns absolute value of x. Return type is the same as input.

SQL command	Result
`SELECT ABS(5)`	`5`
`SELECT ABS(-5)`	`5`
`SELECT ABS(0)`	`0`
`SELECT ABS(null)`	`null`

FLOOR

FLOOR(x)

Returns the largest integral value that is not greater than x.

SQL command	Result
`SELECT FLOOR(2.4)`	`2.0`
`SELECT FLOOR(2.6)`	`2.0`
`SELECT FLOOR(2)`	`2.0`
`SELECT FLOOR(null)`	`null`

TRUNCATE

TRUNCATE(x)

Rounds x toward zero, returning the nearest integral value that is not larger in magnitude than x.

SQL command	Result
`SELECT TRUNCATE(2.4)`	`2.0`
`SELECT TRUNCATE(2.6)`	`2.0`
`SELECT TRUNCATE(-2.4)`	`-2.0`

TRUNC

TRUNC(x)

Alias of TRUNCATE.

CEIL

CEIL(x)

Returns the smallest integral value that is not less than x.

SQL command	Result
`SELECT CEIL(2.4)`	`3.0`
`SELECT CEIL(2.6)`	`3.0`
`SELECT CEIL(3)`	`3.0`
`SELECT CEIL(null)`	`null`

CEILING

CEILING(x)

Alias of CEIL.

ROUND

ROUND(x)

Returns the integral value that is nearest to x, with halfway cases rounded away from zero.

SQL command	Result
`SELECT ROUND(2.4)`	`2.0`
`SELECT ROUND(2.6)`	`3.0`
`SELECT ROUND(3)`	`3.0`
`SELECT ROUND(null)`	`null`

GREATEST

GREATEST(a, b, c, ...)

Returns the argument that is greater than or equal to all other arguments.

SQL command	Result
`SELECT GREATEST(1)`	`1`
`SELECT GREATEST(1, 2, 3)`	`3`
`SELECT GREATEST(1, null, 3)`	`null`

LEAST

LEAST(a, b, c, ...)

Returns the argument that is less than or equal to all other arguments.

SQL command	Result
`SELECT LEAST(1)`	`1`
`SELECT LEAST(1, 2, 3)`	`1`
`SELECT LEAST(1, null, 3)`	`null`

SIGN

SIGN(x)

Returns sign of x as an integer: -1 if x is negative, 0 if x is zero, 1 if x is positive.

SQL command	Result
`SELECT SIGN(5)`	`1`
`SELECT SIGN(-5)`	`-1`
`SELECT SIGN(0)`	`0`
`SELECT SIGN(null)`	`null`

RAND

RAND()

Returns a pseudo-random value in the range [0.0, 1.0).

SQL command	Result
`SELECT RAND()`	`0.08367730533758584`
`SELECT RAND()`	`0.20026947414188864`
`SELECT RAND()`	`0.5600549036289146`

IS_NAN

IS_NAN(x)

Returns true if the input is a floating point Not-A-Number, for instance, due to dividing zero by zero or taking the square root of a negative number. Non-numeric inputs yield null.

SQL command	Result
`SELECT IS_NAN(CAST('NaN' as FLOAT))`	`true`
`SELECT IS_NAN(1.0)`	`false`

FROM_BASE

FROM_BASE(string, base)

Returns the value of string interpreted as a number in base.

SQL command	Result
`SELECT FROM_BASE('101', 2)`	`5`
`SELECT FROM_BASE('ab', 16)`	`171`
`SELECT FROM_BASE('-21', 8)`	`-17`

Log and Exponential Functions

EXP

EXP(x)

Computes e to the power of x.

SQL command	Result
`SELECT EXP(1)`	`2.718281828459045`
`SELECT EXP(0)`	`1.0`
`SELECT EXP(null)`	`null`

POW

POW(x, y)

Computes x to the power of y.

SQL command	Result
`SELECT POW(2, 5)`	`32.0`
`SELECT POW(5, 0)`	`1.0`
`SELECT POW(5, null)`	`null`

POWER

POWER(x, y)

Alias of POW.

SQRT

SQRT(x)

Computes the square root of x.

SQL command	Result
`SELECT SQRT(4)`	`2.0`
`SELECT SQRT(1)`	`1.0`
`SELECT SQRT(null)`	`null`

LN

LN(x)

Computes the natural logarithm of x.

SQL command	Result
`SELECT LN(10)`	`2.302585092994046`
`SELECT LN(EXP(5))`	`5.0`
`SELECT LN(null)`	`null`
`SELECT LN(0)`	`Error: 'division by zero'`

LOG

LOG(x)

Alias of LN.

LOG10

LOG10(x)

Computes the base-10 logarithm of x.

SQL command	Result
`SELECT LOG10(100)`	`2.0`
`SELECT LOG10(1)`	`0.0`
`SELECT LOG10(null)`	`null`
`SELECT LOG10(0)`	`Error: 'division by zero'`

LOG2

LOG2(x)

Computes the base-2 logarithm of x.

SQL command	Result
`SELECT LOG2(32)`	`5.0`
`SELECT LOG2(1)`	`0.0`
`SELECT LOG2(null)`	`null`
`SELECT LOG2(0)`	`Error: 'division by zero'`

Trigonometric Functions

ACOS

ACOS(x)

Computes the arc cosine of x.

SQL command	Result
`SELECT ACOS(0.7)`	`0.7953988301841436`

ACOSH

ACOSH(x)

Computes the inverse hyperbolic cosine of x.

SQL command	Result
`SELECT ACOSH(90)`	`5.192925985263684`

ASIN

ASIN(x)

Computes the arc sine of x.

SQL command	Result
`SELECT ASIN(0.7)`	`0.775397496610753`

ASINH

ASINH(x)

Computes the inverse hyperbolic sine of x.

SQL command	Result
`SELECT ASINH(0.7)`	`0.6526665660823557`

ATAN

ATAN(x)

Computes the arc tangent of x.

SQL command	Result
`SELECT ATAN(0.7)`	`0.6107259643892086`

ATAN2

ATAN2(y, x)

Computes the arc tangent of y / x, but with proper sign for quadrant correction. That is, correctly computes the angle θ when converting from the Cartesian coordinates (x, y) to the polar coordinates (r, θ).

SQL command	Result
`SELECT ATAN2(5, 3)`	`1.0303768265243125`

ATANH

ATANH(x)

Computes the inverse hyperbolic tangent of x.

SQL command	Result
`SELECT ATANH(0.7)`	`0.8673005276940531`

COS

COS(x)

Computes the cosine of x.

SQL command	Result
`SELECT COS(0.7)`	`0.7648421872844885`

COSH

COSH(x)

Computes the hyperbolic cosine of x.

SQL command	Result
`SELECT COSH(0.7)`	`1.255169005630943`

HYPOT

HYPOT(x, y)

Computes SQRT(x*x + y*y), that is, the length of the hypothenuse of a right-angled triangle with sides of lengths x and y. This is also the distance between the point at coordinates (x, y) and origin.

SQL command	Result
`SELECT HYPOT(3, 4)`	`5.0`

SIN

SIN(x)

Computes the sine of x.

SQL command	Result
`SELECT SIN(0.7)`	`0.644217687237691`

SINH

SINH(x)

Computes the hyperbolic sine of x.

SQL command	Result
`SELECT SINH(0.7)`	`0.7585837018395334`

TAN

TAN(x)

Computes the tangent of x.

SQL command	Result
`SELECT TAN(0.7)`	`0.8422883804630794`

TANH

TANH(x)

Computes the hyperbolic tangent of x.

SQL command	Result
`SELECT TANH(0.7)`	`0.6043677771171636`

Bitwise Functions

BIT_COUNT

BIT_COUNT(x, bits)

Count the number of bits set in x (treated as bits-bit signed integer) in 2’s complement representation.

SQL command	Result
`SELECT BIT_COUNT(15, 4)`	`4`

15 = '1111' in binary *

BITWISE_AND

BITWISE_AND(x, y)

Returns the bitwise AND of x and y in 2’s complement representation.

-- 10101 & 111 = 101

SELECT

    BITWISE_AND(37, 7)

BITWISE_OR

BITWISE_OR(x, y)

Returns the bitwise OR of x and y in 2’s complement representation.

-- 10001 | 111 = 10111

SELECT

    BITWISE_OR(33, 7)

BITWISE_NOT

BITWISE_NOT(x)

Returns the bitwise NOT of x in 2’s complement representation.

SQL command	Result
`SELECT BITWISE_NOT(4)`	`-5`

BITWISE_XOR

BITWISE_XOR(x, y)

Returns the bitwise XOR of x and y in 2’s complement representation.

SQL command	Result
`SELECT BITWISE_XOR(21, 10)`	`31`

10101 | 01010 = 11111

Ask the Community Contact Support Report Docs Issue