SQL math functions

The InfluxDB 3 Core SQL implementation supports the following math functions for performing mathematic operations:

• abs
• acos
• acosh
• asin
• asinh
• atan
• atanh
• atan2
• cbrt
• ceil
• cos
• cosh
• cot
• degrees
• exp
• factorial
• floor
• gcd
• isnan
• iszero
• lcm
• ln
• log
• log10
• log2
• nanvl
• pi
• power
• pow
• radians
• random
• round
• signum
• sin
• sinh
• sqrt
• tan
• tanh
• trunc

abs

Returns the absolute value of a number.

abs(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT abs(temp) AS abs FROM home LIMIT 3

acos

Returns the arc cosine or inverse cosine of a number.

acos(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT acos(temp * .01) AS acos FROM home LIMIT 3

acosh

Returns the area hyperbolic cosine or inverse hyperbolic cosine of a number.

acosh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT acosh(temp) AS acosh FROM home LIMIT 3

asin

Returns the arc sine or inverse sine of a number.

asin(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT asin(temp * .01) AS asin FROM home LIMIT 3

asinh

Returns the area hyperbolic sine or inverse hyperbolic sine of a number.

asinh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT asinh(temp) AS asinh FROM home LIMIT 3

atan

Returns the arc tangent or inverse tangent of a number.

atan(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT atan(temp * .01) AS atan FROM home LIMIT 3

atanh

Returns the area hyperbolic tangent or inverse hyperbolic tangent of a number.

atanh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT atanh(temp * .01) AS atanh FROM home LIMIT 3

atan2

Returns the arc tangent or inverse tangent of expression_y / expression_x.

atan2(expression_y, expression_x)

Arguments

• expression_y: First numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_x: Second numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT atan2(temp, hum) AS atan2 FROM home LIMIT 3

cbrt

Returns the cube root of a number.

cbrt(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT cbrt(temp) AS cbrt FROM home LIMIT 3

ceil

Returns the nearest integer greater than or equal to a number.

ceil(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT ceil(temp) AS ceil FROM home LIMIT 3

cos

Returns the cosine of a number.

cos(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT cos(temp) AS cos FROM home LIMIT 3

cosh

Returns the hyperbolic cosine of a number.

cosh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT cosh(temp) AS cosh FROM home LIMIT 3

cot

Returns the cotangent of a number.

cot(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of operators.

SELECT cot(a) AS cot FROM numbers LIMIT 3

degrees

Converts radians to degrees.

degrees(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT degrees(a) AS degrees FROM numbers LIMIT 3

exp

Returns the base-e exponential of a number.

exp(numeric_expression)

Arguments

• numeric_expression: Numeric expression to use as the exponent. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT exp(temp) AS exp FROM home LIMIT 3

factorial

Returns 1 if value is less than 2.

factorial(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Must be an integer (BIGINT). Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT factorial((b + 2)::BIGINT) AS factorial FROM numbers LIMIT 3

floor

Returns the nearest integer less than or equal to a number.

floor(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT floor(temp) AS floor FROM home LIMIT 3

gcd

Returns the greatest common divisor of expression_x and expression_y. Returns 0 if both inputs are zero.

gcd(expression_x, expression_y)

Arguments

• expression_x: First numeric expression to operate on. Must be an integer (BIGINT). Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_y: Second numeric expression to operate on. Must be an integer (BIGINT). Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT gcd((a * 5)::BIGINT, (b * 5)::BIGINT) AS gcd FROM numbers LIMIT 3

isnan

Returns true if a given number is ±NaN, otherwise returns false.

isnan(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Must be a float (DOUBLE). Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT isnan(a)
FROM
  (VALUES (4.56),
          ('NaN'::DOUBLE),
          (16.2)
  ) AS data(a)

iszero

Returns true if the given number is ±0.0, otherwise returns false.

iszero(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT iszero(a)
FROM
  (VALUES (0),
          (1),
          (2)
  ) AS data(a)

lcm

Returns the least common multiple of expression_x and expression_y. Returns 0 if either input is zero.

lcm(expression_x, expression_y)

Arguments

• expression_x: First numeric expression to operate on. Must be an integer (BIGINT). Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_y: Second numeric expression to operate on. Must be an integer (BIGINT). Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT lcm((a * 10)::BIGINT, (b * 10)::BIGINT) AS lcm FROM numbers LIMIT 3

ln

Returns the natural logarithm of a number.

ln(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT ln(temp) AS ln FROM home LIMIT 3

log

Returns the base-x logarithm of a number.

log([base, ]numeric_expression)

Arguments

• base: Base numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators. Default is 10.
• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT
  temp,
  log(2, temp) AS temp_log2,
  log(4, temp) AS temp_log4,
  log(temp) AS temp_log10
FROM home
LIMIT 3

log10

Returns the base-10 logarithm of a number.

log10(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT log10(temp) AS log10 FROM home LIMIT 3

log2

Returns the base-2 logarithm of a number.

log2(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT log2(temp) AS log2 FROM home LIMIT 3

nanvl

Returns the first argument if it’s not ±NaN. Otherwise returns the second argument.

nanvl(expression_x, expression_y)

Arguments

• expression_x: Numeric expression to return if it’s not NaN. Can be a constant, column, or function, and any combination of arithmetic operators.
• expression_y: Numeric expression to return if the first expression is NaN. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT nanvl(a, 0.0) AS nanvl
FROM
  (VALUES (4.56),
          ('NaN'::DOUBLE),
          (16.2)
  ) AS data(a)

pi

Returns an approximate value of π.

pi()

SELECT pi() AS pi

power

Returns a base expression raised to the power of an exponent.

power(base, exponent)

Aliases

• pow

Arguments

• base: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
• exponent: Exponent numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT power(temp, hum * .1) AS power FROM home LIMIT 3

pow

Alias of power.

radians

Converts degrees to radians.

radians(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT radians(b) AS radians FROM numbers LIMIT 3

random

Returns a random float value between 0 and 1. The random seed is unique to each row.

random()

SELECT temp * random() AS random FROM home LIMIT 3

round

Rounds a number to the nearest integer.

round(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT round(temp) AS round FROM home LIMIT 3

signum

Returns the sign of a number. Negative numbers return -1. Zero and positive numbers return 1.

signum(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT signum(temp - 23) AS signum FROM home LIMIT 3

sin

Returns the sine of a number.

sin(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT sin(temp) AS sin FROM home LIMIT 3

sinh

Returns the hyperbolic sine of a number.

sinh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT sinh(temp) AS sinh FROM home LIMIT 3

sqrt

Returns the square root of a number.

sqrt(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT sqrt(temp) AS sqrt FROM home LIMIT 3

tan

Returns the tangent of a number.

tan(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT tan(temp) AS tan FROM home LIMIT 3

tanh

Returns the hyperbolic tangent of a number.

tanh(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT tanh(a) AS tanh FROM numbers LIMIT 3

trunc

Truncates a number toward zero (at the decimal point).

trunc(numeric_expression)

Arguments

• numeric_expression: Numeric expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

SELECT trunc(temp) AS trunc FROM home LIMIT 3