This documentation contains work-in-progress information for future Elastic Stack and Cloud releases. Use the version selector to view supported release docs. It also contains some Elastic Cloud serverless information. Check out our serverless docs for more details.

« Full-Text Search Functions String Functions »

› › ›

Mathematical Functions

Mathematical Functions

All math and trigonometric functions require their input (where applicable) to be numeric.

Generic

`ABS`

Synopsis:

ABS(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: numeric

Description: Returns the absolute value of numeric_exp. The return type is the same as the input type.

SELECT ABS(-123.5), ABS(55);

  ABS(-123.5)  |    ABS(55)
---------------+---------------
123.5          |55

`CBRT`

Synopsis:

CBRT(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cube root of numeric_exp.

SELECT CBRT(-125.5);

   CBRT(-125.5)
-------------------
-5.0066577974783435

`CEIL/CEILING`

Synopsis:

CEIL(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: integer or long numeric value

Description: Returns the smallest integer greater than or equal to numeric_exp.

SELECT CEIL(125.01), CEILING(-125.99);

 CEIL(125.01)  |CEILING(-125.99)
---------------+----------------
126            |-125

`E`

Synopsis:

E()

Input: none

Output: 2.718281828459045

Description: Returns Euler’s number.

SELECT E(), CEIL(E());

       E()       |   CEIL(E())
-----------------+---------------
2.718281828459045|3

`EXP`

Synopsis:

EXP(numeric_exp)

Input:

float numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns Euler’s number at the power of numeric_exp e^numeric_exp.

SELECT EXP(1), E(), EXP(2), E() * E();

     EXP(1)      |       E()       |     EXP(2)     |     E() * E()
-----------------+-----------------+----------------+------------------
2.718281828459045|2.718281828459045|7.38905609893065|7.3890560989306495

`EXPM1`

Synopsis:

EXPM1(numeric_exp)

Input:

float numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns Euler’s number at the power of numeric_exp minus 1 (e^numeric_exp - 1).

SELECT E(), EXP(2), EXPM1(2);

       E()       |     EXP(2)     |    EXPM1(2)
-----------------+----------------+----------------
2.718281828459045|7.38905609893065|6.38905609893065

`FLOOR`

Synopsis:

FLOOR(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: integer or long numeric value

Description: Returns the largest integer less than or equal to numeric_exp.

SELECT FLOOR(125.01), FLOOR(-125.99);

 FLOOR(125.01) |FLOOR(-125.99)
---------------+---------------
125            |-126

`LOG`

Synopsis:

LOG(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the natural logarithm of numeric_exp.

SELECT EXP(3), LOG(20.085536923187668);

      EXP(3)      |LOG(20.085536923187668)
------------------+-----------------------
20.085536923187668|3.0

`LOG10`

Synopsis:

LOG10(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the base 10 logarithm of numeric_exp.

SELECT LOG10(5), LOG(5)/LOG(10);

     LOG10(5)     |    LOG(5)/LOG(10)
------------------+-----------------------
0.6989700043360189|0.6989700043360187

`PI`

Synopsis:

PI()

Input: none

Output: 3.141592653589793

Description: Returns PI number.

SELECT PI();

      PI()
-----------------
3.141592653589793

`POWER`

Synopsis:

POWER(
    numeric_exp, 
    integer_exp)

Input:

	numeric expression. If `null`, the function returns `null`.
	integer expression. If `null`, the function returns `null`.

Output: double numeric value

Description: Returns the value of numeric_exp to the power of integer_exp.

SELECT POWER(3, 2), POWER(3, 3);

  POWER(3, 2)  |  POWER(3, 3)
---------------+---------------
9.0            |27.0

SELECT POWER(5, -1), POWER(5, -2);

  POWER(5, -1) |  POWER(5, -2)
---------------+---------------
0.2            |0.04

`RANDOM/RAND`

Synopsis:

RANDOM(seed)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns a random double using the given seed.

SELECT RANDOM(123);

   RANDOM(123)
------------------
0.7231742029971469

`ROUND`

Synopsis:

ROUND(
    numeric_exp      
    [, integer_exp])

Input:

	numeric expression. If `null`, the function returns `null`.
	integer expression; optional. If `null`, the function returns `null`.

Output: numeric

Description: Returns numeric_exp rounded to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is rounded to |integer_exp| places to the left of the decimal point. If integer_exp is omitted, the function will perform as if integer_exp would be 0. The returned numeric data type is the same as the data type of numeric_exp.

SELECT ROUND(-345.153, 1) AS rounded;

    rounded
---------------
-345.2

SELECT ROUND(-345.153, -1) AS rounded;

    rounded
---------------
-350.0

`SIGN/SIGNUM`

Synopsis:

SIGN(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: [-1, 0, 1]

Description: Returns an indicator of the sign of numeric_exp. If numeric_exp is less than zero, –1 is returned. If numeric_exp equals zero, 0 is returned. If numeric_exp is greater than zero, 1 is returned.

SELECT SIGN(-123), SIGN(0), SIGN(415);

  SIGN(-123)   |    SIGN(0)    |   SIGN(415)
---------------+---------------+---------------
-1             |0              |1

`SQRT`

Synopsis:

SQRT(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns square root of numeric_exp.

SELECT SQRT(EXP(2)), E(), SQRT(25);

  SQRT(EXP(2))   |       E()       |   SQRT(25)
-----------------+-----------------+---------------
2.718281828459045|2.718281828459045|5.0

`TRUNCATE/TRUNC`

Synopsis:

TRUNCATE(
    numeric_exp      
    [, integer_exp])

Input:

	numeric expression. If `null`, the function returns `null`.
	integer expression; optional. If `null`, the function returns `null`.

Output: numeric

Description: Returns numeric_exp truncated to integer_exp places right of the decimal point. If integer_exp is negative, numeric_exp is truncated to |integer_exp| places to the left of the decimal point. If integer_exp is omitted, the function will perform as if integer_exp would be 0. The returned numeric data type is the same as the data type of numeric_exp.

SELECT TRUNC(-345.153, 1) AS trimmed;

    trimmed
---------------
-345.1

SELECT TRUNCATE(-345.153, -1) AS trimmed;

    trimmed
---------------
-340.0

Trigonometric

`ACOS`

Synopsis:

ACOS(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arccosine of numeric_exp as an angle, expressed in radians.

SELECT ACOS(COS(PI())), PI();

 ACOS(COS(PI())) |      PI()
-----------------+-----------------
3.141592653589793|3.141592653589793

`ASIN`

Synopsis:

ASIN(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arcsine of numeric_exp as an angle, expressed in radians.

SELECT ROUND(DEGREES(ASIN(0.7071067811865475))) AS "ASIN(0.707)", ROUND(SIN(RADIANS(45)), 3) AS "SIN(45)";

  ASIN(0.707)  |    SIN(45)
---------------+---------------
45.0           |0.707

`ATAN`

Synopsis:

ATAN(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the arctangent of numeric_exp as an angle, expressed in radians.

SELECT DEGREES(ATAN(TAN(RADIANS(90))));

DEGREES(ATAN(TAN(RADIANS(90))))
-------------------------------
90.0

`ATAN2`

Synopsis:

ATAN2(
    ordinate, 
    abscisa)

Input:

	numeric expression. If `null`, the function returns `null`.
	numeric expression. If `null`, the function returns `null`.

Output: double numeric value

Description: Returns the arctangent of the ordinate and abscisa coordinates specified as an angle, expressed in radians.

SELECT ATAN2(5 * SIN(RADIANS(45)), 5 * COS(RADIANS(45))) AS "ATAN2(5*SIN(45), 5*COS(45))", RADIANS(45);

ATAN2(5*SIN(45), 5*COS(45))|   RADIANS(45)
---------------------------+------------------
0.7853981633974483         |0.7853981633974483

`COS`

Synopsis:

COS(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cosine of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT COS(RADIANS(180)), POWER(SIN(RADIANS(54)), 2) + POWER(COS(RADIANS(54)), 2) AS pythagorean_identity;

COS(RADIANS(180))|pythagorean_identity
-----------------+--------------------
-1.0             |1.0

`COSH`

Synopsis:

COSH(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the hyperbolic cosine of numeric_exp.

SELECT COSH(5), (POWER(E(), 5) + POWER(E(), -5)) / 2 AS "(e^5 + e^-5)/2";

     COSH(5)     | (e^5 + e^-5)/2
-----------------+-----------------
74.20994852478785|74.20994852478783

`COT`

Synopsis:

COT(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the cotangent of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT COT(RADIANS(30)) AS "COT(30)", COS(RADIANS(30)) / SIN(RADIANS(30)) AS "COS(30)/SIN(30)";

     COT(30)      | COS(30)/SIN(30)
------------------+------------------
1.7320508075688774|1.7320508075688776

`DEGREES`

Synopsis:

DEGREES(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Convert from radians to degrees.

SELECT DEGREES(PI() * 2), DEGREES(PI());

DEGREES(PI() * 2)| DEGREES(PI())
-----------------+---------------
360.0            |180.0

`RADIANS`

Synopsis:

RADIANS(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Convert from degrees to radians.

SELECT RADIANS(90), PI()/2;

   RADIANS(90)    |      PI()/2
------------------+------------------
1.5707963267948966|1.5707963267948966

`SIN`

Synopsis:

SIN(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the sine of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT SIN(RADIANS(90)), POWER(SIN(RADIANS(67)), 2) + POWER(COS(RADIANS(67)), 2) AS pythagorean_identity;

SIN(RADIANS(90))|pythagorean_identity
----------------+--------------------
1.0             |1.0

`SINH`

Synopsis:

SINH(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the hyperbolic sine of numeric_exp.

SELECT SINH(5), (POWER(E(), 5) - POWER(E(), -5)) / 2 AS "(e^5 - e^-5)/2";

     SINH(5)     | (e^5 - e^-5)/2
-----------------+-----------------
74.20321057778875|74.20321057778874

`TAN`

Synopsis:

TAN(numeric_exp)

Input:

numeric expression. If null, the function returns null.

Output: double numeric value

Description: Returns the tangent of numeric_exp, where numeric_exp is an angle expressed in radians.

SELECT TAN(RADIANS(66)) AS "TAN(66)", SIN(RADIANS(66))/COS(RADIANS(66)) AS "SIN(66)/COS(66)=TAN(66)";

     TAN(66)      |SIN(66)/COS(66)=TAN(66)
------------------+-----------------------
2.2460367739042164|2.246036773904216

« Full-Text Search Functions String Functions »