Time Series Database:InfluxQL mathematical operators

Addition

With a constant:

SELECT "A" + 5 FROM "add"

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

With two fields:

SELECT "A" + "B" FROM "add"

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

Subtraction

With a constant:

SELECT 1 - "A" FROM "sub"

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

With two fields:

SELECT "A" - "B" FROM "sub"

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

Multiplication

With a constant:

SELECT 10 * "A" FROM "mult"

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

With two fields:

SELECT "A" * "B" * "C" FROM "mult"

SELECT * FROM "mult" WHERE "A" * "B" <= 80

Multiplication distributes across other operators:

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

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

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

Division

With a constant:

SELECT 10 / "A" FROM "div"

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

With two fields:

SELECT "A" / "B" FROM "div"

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

Division distributes across other operators:

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

Modulo

With a constant:

SELECT "B" % 2 FROM "modulo"

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

With two fields:

SELECT "A" % "B" FROM "modulo"

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

Bitwise AND

Works with INTEGER and BOOLEAN fields or constants. Does not support floating-point numbers or strings. You cannot mix integers and Booleans in the same expression.

SELECT "A" & 255 FROM "bitfields"

SELECT "A" & "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" & 15 > 0

SELECT "A" & "B" FROM "booleans"

SELECT ("A" ^ true) & "B" FROM "booleans"

Bitwise OR

Works with INTEGER and BOOLEAN fields or constants. Does not support floating-point numbers or strings. You cannot mix integers and Booleans in the same expression.

SELECT "A" | 5 FROM "bitfields"

SELECT "A" | "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" | 12 = 12

Bitwise exclusive OR

Works with INTEGER and BOOLEAN fields or constants. Does not support floating-point numbers or strings. You cannot mix integers and Booleans in the same expression.

SELECT "A" ^ 255 FROM "bitfields"

SELECT "A" ^ "B" FROM "bitfields"

SELECT * FROM "data" WHERE "bitfield" ^ 6 > 0

Issue 1: Math operators with wildcards or regular expressions

Time Series Database (TSDB) for InfluxDB® does not support combining math operators with wildcards (*) or regular expressions in the SELECT clause. These queries all return errors:

-- Wildcard
> SELECT * + 2 FROM "nope"
ERR: unsupported expression with wildcard: * + 2

-- Wildcard inside a function
> SELECT COUNT(*) / 2 FROM "nope"
ERR: unsupported expression with wildcard: count(*) / 2

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

-- Regular expression inside a function
> SELECT COUNT(/A/) + 2 FROM "nope"
ERR: unsupported expression with regex field: count(/A/) + 2

Issue 2: Math operators inside function calls

Math operators inside function calls are not supported. TSDB for InfluxDB® only allows functions in SELECT clauses.

This query works:

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

This query yields a parse error:

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

Equality and inequality operators

Using =, !=, <, >, <=, >=, or <> in a SELECT clause always returns an empty result, regardless of the data type.

Logical operators

Using !|, NAND, XOR, or NOR in a statement causes a parser error.

Using AND or OR in a SELECT clause returns an empty result without performing any mathematical operation. This is because AND and OR are reserved tokens in InfluxQL. To process Boolean data with bitwise logic, use &, |, or ^ instead.

Bitwise NOT

InfluxQL does not support the bitwise NOT operator. Bitwise NOT results depend on the bitfield width, and InfluxQL has no way to determine that width.

For example, for an 8-bit bitfield, the integer 1 is 0000 0001. A bitwise NOT yields 1111 1110, which equals 254. For a 16-bit bitfield, the same integer 1 is 0000 0000 0000 0001, and bitwise NOT yields 1111 1111 1111 1110, which equals 65534.

Workaround: Use the ^ (XOR) operator with a constant equal to (2 ** width) - 1—the value where all bits in your bitfield are set to 1.

8-bit example:

SELECT "A" ^ 255 FROM "data"

16-bit example:

SELECT "A" ^ 65535 FROM "data"

32-bit example:

SELECT "A" ^ 4294967295 FROM "data"