InfluxQL transformation functions

InfluxQL transformation functions modify and return values in each row of queried data.

• ABS()
• ACOS()
• ASIN()
• ATAN()
• ATAN2()
• CEIL()
• COS()
• CUMULATIVE_SUM()
• DERIVATIVE()
• DIFFERENCE()
• ELAPSED()
• EXP()
• FLOOR()
• LN()
• LOG()
• LOG2()
• LOG10()
• MOVING_AVERAGE()
• NON_NEGATIVE_DERIVATIVE()
• NON_NEGATIVE_DIFFERENCE()
• POW()
• ROUND()
• SIN()
• SQRT()
• TAN()

Notable behaviors of transformation functions

Must use aggregate or selector functions when grouping by time

Most transformation functions support GROUP BY clauses that group by tags, but do not directly support GROUP BY clauses that group by time. To use transformation functions with with a GROUP BY time() clause, apply an aggregate or selector function to the field_expression argument. The transformation operates on the result of the aggregate or selector operation.

ABS()

Returns the absolute value of the field value.

ABS(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  a,
  ABS(a)
FROM numbers
LIMIT 6

SELECT ABS(*) FROM numbers LIMIT 6

SELECT
  ABS(MEAN(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ACOS()

Returns the arccosine (in radians) of the field value. Field values must be between -1 and 1.

ACOS(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  a,
  ACOS(a)
FROM numbers
LIMIT 6

SELECT ACOS(*) FROM numbers LIMIT 6

SELECT
  ACOS(MEAN(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ASIN()

Returns the arcsine (in radians) of the field value. Field values must be between -1 and 1.

ASIN(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  a,
  ASIN(a)
FROM numbers
LIMIT 6

SELECT ASIN(*) FROM numbers LIMIT 6

SELECT
  ASIN(MEAN(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ATAN()

Returns the arctangent (in radians) of the field value.

ATAN(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  a,
  ATAN(a)
FROM numbers
LIMIT 6

SELECT ATAN(*) FROM numbers LIMIT 6

SELECT
  ATAN(MEAN(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ATAN2()

Returns the the arctangent of y/x in radians.

ATAN2(expression_y, expression_x)

Arguments

• expression_y: Expression to identify the y numeric value or one or more fields to operate on. Can be a number literal, field key, constant, or wildcard (*). Supports numeric field types.
• expression_x: Expression to identify the x numeric value or one or more fields to operate on. Can be a number literal, field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT ATAN2(a, b) FROM numbers LIMIT 6

SELECT ATAN2(*, 2) FROM numbers LIMIT 6

SELECT
  ATAN2(MEAN(a), MEAN(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

CEIL()

Returns the subsequent value rounded up to the nearest integer.

CEIL(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  CEIL(b)
FROM numbers
LIMIT 6

SELECT CEIL(*) FROM numbers LIMIT 6

SELECT
  CEIL(MEAN(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

COS()

Returns the cosine of the field value.

COS(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  COS(b)
FROM numbers
LIMIT 6

SELECT COS(*) FROM numbers LIMIT 6

SELECT
  COS(MEAN(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

CUMULATIVE_SUM()

Returns the running total of subsequent field values.

CUMULATIVE_SUM(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT CUMULATIVE_SUM(b) FROM numbers LIMIT 6

SELECT CUMULATIVE_SUM(*) FROM numbers LIMIT 6

SELECT CUMULATIVE_SUM(/[ab]/) FROM numbers LIMIT 6

SELECT
  CUMULATIVE_SUM(SUM(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

DERIVATIVE()

Returns the rate of change between subsequent field values per unit.

SELECT DERIVATIVE(field_expression[, unit])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.
• unit: Unit of time to use to calculate the rate of change. Supports duration literals. Default is 1s (per second).

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Related functions

• NON_NEGATIVE_DERIVATIVE()

Examples

The following examples use the Random numbers sample data.

SELECT DERIVATIVE(b) FROM numbers LIMIT 6

SELECT DERIVATIVE(b, 5m) FROM numbers LIMIT 6

SELECT DERIVATIVE(*) FROM numbers LIMIT 6

SELECT DERIVATIVE(/[ab]/) FROM numbers LIMIT 6

SELECT
  DERIVATIVE(MEAN(b), 1m)
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

DIFFERENCE()

Returns the result of subtraction between subsequent field values.

DIFFERENCE(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Related functions

• NON_NEGATIVE_DIFFERENCE()

Examples

The following examples use the Random numbers sample data.

SELECT DIFFERENCE(b) FROM numbers LIMIT 6

SELECT DIFFERENCE(*) FROM numbers LIMIT 6

SELECT DIFFERENCE(/[ab]/) FROM numbers LIMIT 6

SELECT
  DIFFERENCE(MEAN(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ELAPSED()

Returns the difference between subsequent field value’s timestamps in a specified unit of time.

ELAPSED(field_expression[, unit ])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports all field types.
• unit: Unit of time to return the elapsed time in. Supports duration literals. Default is 1ns (nanoseconds).

Notable behaviors

• If the unit is greater than the elapsed time between points, ELAPSED() returns 0.
• ELAPSED() supports the GROUP BY time() clause but the query results aren’t very useful. An ELAPSED() query with a nested function and a GROUP BY time() clause returns the interval specified in the GROUP BY time() clause.

Examples

The following examples use the Random numbers sample data.

SELECT ELAPSED(b) FROM numbers LIMIT 6

SELECT ELAPSED(b, 1s) FROM numbers LIMIT 6

SELECT ELAPSED(*) FROM numbers LIMIT 6

SELECT ELAPSED(/[ab]/, 1s) FROM numbers LIMIT 6

EXP()

Returns the exponential of the field value.

EXP(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  a,
  EXP(a)
FROM numbers
LIMIT 6

SELECT EXP(*) FROM numbers LIMIT 6

SELECT
  EXP(MEAN(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

FLOOR()

Returns the subsequent value rounded down to the nearest integer.

FLOOR(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  FLOOR(b)
FROM numbers
LIMIT 6

SELECT FLOOR(*) FROM numbers LIMIT 6

SELECT
  FLOOR(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

LN()

Returns the natural logarithm of the field value. Field values must be greater than or equal to 0.

LN(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  LN(b)
FROM numbers
LIMIT 6

SELECT LN(*) FROM numbers LIMIT 6

SELECT
  LN(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

LOG()

Returns the logarithm of the field value with base b. Field values must be greater than or equal to 0.

LOG(field_expression, b)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.
• b: Logarithm base to use in the operation.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  LOG(b, 3)
FROM numbers
LIMIT 6

SELECT LOG(*, 5) FROM numbers LIMIT 6

SELECT
  LOG(SUM(a), 10)
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

LOG2()

Returns the logarithm of the field value to the base 2. Field values must be greater than or equal to 0.

LOG2(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  LOG2(b)
FROM numbers
LIMIT 6

SELECT LOG2(*) FROM numbers LIMIT 6

SELECT
  LOG2(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

LOG10()

Returns the logarithm of the field value to the base 10. Field values must be greater than or equal to 0.

LOG10(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  LOG10(b)
FROM numbers
LIMIT 6

SELECT LOG10(*) FROM numbers LIMIT 6

SELECT
  LOG10(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

MOVING_AVERAGE()

Returns the rolling average across a window of subsequent field values.

MOVING_AVERAGE(field_expression, N)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports all field types.
• N: Number of field values to use when calculating the moving average.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT MOVING_AVERAGE(a, 3) FROM numbers LIMIT 6

SELECT MOVING_AVERAGE(*, 3) FROM numbers LIMIT 6

SELECT MOVING_AVERAGE(/[ab]/, 3) FROM numbers LIMIT 6

SELECT
  MOVING_AVERAGE(SUM(a), 3)
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

NON_NEGATIVE_DERIVATIVE()

Returns only non-negative rate of change between subsequent field values. Negative rates of change return null.

NON_NEGATIVE_DERIVATIVE(field_expression[, unit])

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.
• unit: Unit of time to use to calculate the rate of change. Supports duration literals. Default is 1s (per second).

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Related functions

• DERIVATIVE()

Examples

The following examples use the Random numbers sample data.

SELECT NON_NEGATIVE_DERIVATIVE(b) FROM numbers LIMIT 6

SELECT NON_NEGATIVE_DERIVATIVE(b, 5m) FROM numbers LIMIT 6

SELECT NON_NEGATIVE_DERIVATIVE(*) FROM numbers LIMIT 6

SELECT NON_NEGATIVE_DERIVATIVE(/[ab]/) FROM numbers LIMIT 6

SELECT
  NON_NEGATIVE_DERIVATIVE(MEAN(b), 1m)
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

NON_NEGATIVE_DIFFERENCE()

Returns only non-negative result of subtraction between subsequent field values. Negative differences return null.

NON_NEGATIVE_DIFFERENCE(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, regular expression, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Related functions

• DIFFERENCE()

Examples

The following examples use the Random numbers sample data.

SELECT NON_NEGATIVE_DIFFERENCE(b) FROM numbers LIMIT 6

SELECT NON_NEGATIVE_DIFFERENCE(*) FROM numbers LIMIT 6

SELECT NON_NEGATIVE_DIFFERENCE(/[ab]/) FROM numbers LIMIT 6

SELECT
  NON_NEGATIVE_DIFFERENCE(MEAN(b))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

POW()

Returns the field value to the power of x.

POW(field_expression, x)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.
• x: Power to raise to.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  POW(b, 3)
FROM numbers
LIMIT 6

SELECT POW(*, 5) FROM numbers LIMIT 6

SELECT
  POW(SUM(a), 10)
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

ROUND()

Returns a field value rounded to the nearest integer.

ROUND(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  ROUND(b)
FROM numbers
LIMIT 6

SELECT ROUND(*) FROM numbers LIMIT 6

SELECT
  ROUND(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

SIN()

Returns the sine of a field value.

SIN(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  SIN(b)
FROM numbers
LIMIT 6

SELECT SIN(*) FROM numbers LIMIT 6

SELECT
  SIN(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

SQRT()

Returns the square root of a field value. Field values must be greater than or equal to 0. Negative field values return null.

SQRT(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  SQRT(b)
FROM numbers
LIMIT 6

SELECT SQRT(*) FROM numbers LIMIT 6

SELECT
  SQRT(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)

TAN()

Returns the tangent of the field value.

TAN(field_expression)

Arguments

• field_expression: Expression to identify one or more fields to operate on. Can be a field key, constant, or wildcard (*). Supports numeric field types.

Notable behaviors

• Must use aggregate or selector functions when grouping by time.

Examples

The following examples use the Random numbers sample data.

SELECT
  b,
  TAN(b)
FROM numbers
LIMIT 6

SELECT TAN(*) FROM numbers LIMIT 6

SELECT
  TAN(SUM(a))
FROM numbers
WHERE
  time >= '2023-01-01T00:00:00Z'
  AND time < '2023-01-01T01:00:00Z'
GROUP BY time(10m)