All Products
Search
Document Center

InfluxQL Mathematical Operators

Last Updated: Aug 18, 2020

Mathematical operators follow the standard order of arithmetic operations. For more information, see Operator precedence. Operations in parentheses take precedence over division and multiplication operations. Division and multiplication operations take precedence over addition and subtraction operations. The following examples are used to explain the order: 5/2 + 3 × 2 = (5/2) + (3 × 2) and 5 + 2 × 3 - 2 = 5 + (2 × 3) - 2.

Overview

  • Mathematical operators
    • Addition
    • Subtraction
    • Multiplication
    • Division
    • Modulo
    • Bitwise AND
    • Bitwise OR
    • Bitwise exclusive OR
    • FAQ about mathematical operators
  • Unsupported operators

Mathematical operators

Addition

Add a constant to the specified values.

  1. SELECT "A" + 5 FROM "add"
  1. SELECT * FROM "add" WHERE "A" + 5 > 10

Add the values of one field to the values of another field.

  1. SELECT "A" + "B" FROM "add"
  1. SELECT * FROM "add" WHERE "A" + "B" >= 10

Subtraction

Perform subtraction operations on a constant and field values.

  1. SELECT 1 - "A" FROM "sub"
  1. SELECT * FROM "sub" WHERE 1 - "A" <= 3

Subtract the values of a field from the values of another field.

  1. SELECT "A" - "B" FROM "sub"
  1. SELECT * FROM "sub" WHERE "A" - "B" <= 1

Multiplication

Multiply the specified values by a constant.

  1. SELECT 10 * "A" FROM "mult"
  1. SELECT * FROM "mult" WHERE "A" * 10 >= 20

Multiply the values of a field by the values of another field.

  1. SELECT "A" * "B" * "C" FROM "mult"
  1. SELECT * FROM "mult" WHERE "A" * "B" <= 80

Use the multiplication operator in conjunction with other mathematical operators

  1. SELECT 10 * ("A" + "B" + "C") FROM "mult"
  1. SELECT 10 * ("A" - "B" - "C") FROM "mult"
  1. SELECT 10 * ("A" + "B" - "C") FROM "mult"

Division

Perform division operations on a constant and field values.

  1. SELECT 10 / "A" FROM "div"
  1. SELECT * FROM "div" WHERE "A" / 10 <= 2

Divide the values of a field by the values of another field.

  1. SELECT "A" / "B" FROM "div"
  1. SELECT * FROM "div" WHERE "A" / "B" >= 10

Use the division operator in conjunction with other mathematical operators

  1. SELECT 10 / ("A" + "B" + "C") FROM "div"

Modulo

Perform modulo operations on a constant and field values.

  1. SELECT "B" % 2 FROM "modulo"
  1. SELECT "B" FROM "modulo" WHERE "B" % 2 = 0

Perform modulo operations on the values of two fields.

  1. SELECT "A" % "B" FROM "modulo"
  1. SELECT "A" FROM "modulo" WHERE "A" % "B" = 0

Bitwise AND

You can apply the bitwise AND operator to integers or Boolean values. The integers or Boolean values may be field values or constants. The bitwise AND operator does not apply to floating-point numbers or strings. You cannot apply the bitwise AND operator to integers and Boolean values at the same time.

  1. SELECT "A" & 255 FROM "bitfields"
  1. SELECT "A" & "B" FROM "bitfields"
  1. SELECT * FROM "data" WHERE "bitfield" & 15 > 0
  1. SELECT "A" & "B" FROM "booleans"
  1. SELECT ("A" ^ true) & "B" FROM "booleans"

Bitwise OR

You can apply the bitwise OR operator on integers or Boolean values. The integers or Boolean values may be field values or constants. The bitwise OR operator does not apply to floating-point numbers or strings. You cannot apply the bitwise OR operator to integers and Boolean values at the same time.

  1. SELECT "A" | 5 FROM "bitfields"
  1. SELECT "A" | "B" FROM "bitfields"
  1. SELECT * FROM "data" WHERE "bitfield" | 12 = 12

Bitwise exclusive OR

You can apply the bitwise exclusive OR operator on integers or Boolean values. The integers or Boolean values may be field values or constants. The bitwise exclusive OR operator does not apply to floating-point numbers or strings. You cannot apply the bitwise exclusive OR operator to integers and Boolean values at the same time.

  1. SELECT "A" ^ 255 FROM "bitfields"
  1. SELECT "A" ^ "B" FROM "bitfields"
  1. SELECT * FROM "data" WHERE "bitfield" ^ 6 > 0

FAQ about mathematical operators

Question 1: Can I use mathematical operators in conjunction with wildcards or regular expressions?

No, the mathematical operators cannot be used in conjunction with wildcards (*) or regular expressions in the SELECT clauses in TSDB for InfluxDB®. The following queries are invalid and the system returns error messages:

Use wildcards in mathematical operations.

  1. > SELECT * + 2 FROM "nope"
  2. ERR: unsupported expression with wildcard: * + 2

Use wildcards in the functions that perform mathematical operations.

  1. > SELECT COUNT(*) / 2 FROM "nope"
  2. ERR: unsupported expression with wildcard: count(*) / 2

Use regular expressions in mathematical operations.

  1. > SELECT /A/ + 2 FROM "nope"
  2. ERR: error parsing query: found +, expected FROM at line 1, char 12

Use regular expressions in the functions that perform mathematical operations.

  1. > SELECT COUNT(/A/) + 2 FROM "nope"
  2. ERR: unsupported expression with regex field: count(/A/) + 2

Question 2: Can I use mathematical operators in the functions?

No, mathematical operators cannot be used in the functions. Note that TSDB for InfluxDB® allows you to use functions in only the SELECT clause of your query.

The following example is provided to explain this rule.

  1. SELECT 10 * mean("value") FROM "cpu"

The statement is valid. However, a parsing error occurs.

  1. SELECT mean(10 * "value") FROM "cpu"

InfluxQL supports subqueries. You can use subqueries as an alternative if you need to use mathematical operators in functions. For more information, see the “Data exploration” topic.

Unsupported operators

Equal and not equal operators

If you use =, ! =, <, >, <=, >=, or <> operators in the SELECT clause to query data, no result is returned.

Logical operators

If you use ! |, NAND, XOR, or NOR operators, parsing errors occur.

If you use the AND or OR operator in the SELECT clause, you cannot obtain the same query results as those for the corresponding queries where mathematical operators are used. In this scenario, no result is returned because AND and OR are tokens in InfluxQL. However, you can apply the following bitwise operators to Boolean values: &, |, and ^.

Bitwise Not

InfluxQL does not support the bitwise NOT operator. Your expected query results depend on the width of your bit field, and InfluxQL cannot obtain the information about the width of your bit field. Therefore, InfluxQL cannot implement the bitwise NOT operator as expected.

For example, if the width of your bit field is 8 bits, the integer 1 represents the bits 0000 0001. The bitwise NOT operation returns the bits 1111 1110 and the bits indicate the integer 254.

If the width of your bit field is 16 bits, the integer 1 represents the bits 0000 0000 0000 0001. The bitwise NOT operation returns the bits 1111 1111 1111 1110 and the bits indicate the integer 65534.

Solution

To implement a bitwise NOT operation, use the ^ bitwise XOR operator in conjunction with a number where each bit is 1. Note that the number of bits (1) indicates the width of your bit field.

If you are using 8-bit data, execute the following statement:

  1. SELECT "A" ^ 255 FROM "data"

If you are using 16-bit data, execute the following statement:

  1. SELECT "A" ^ 65535 FROM "data"

If you are using 32-bit data, execute the following statement:

  1. SELECT "A" ^ 4294967295 FROM "data"

In each of the three preceding scenarios, you can raise 2 to the power of 8, 16, or 32 based on your width of the bit field and subtract 1 from the result to obtain the required constant. The corresponding formula is Required constant = (2 ** width) - 1. In the formula, the width element indicates the width of the bit field.


InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.