Documentation

SQL reference documentation

InfluxDB Cloud Dedicated uses the Apache Arrow DataFusion implementation of SQL.

Identifiers

An identifier is a token which refers to the name of an InfluxDB database object, such as a measurement or a column name (time, tag keys, or field keys).

Quoting

Use double quotes on identifiers to treat them as case-sensitive. Use single quotes on string literals.

General quoting guidelines:

  • Single quote RFC3339 and RFC3339-like time values.
  • Do not quote Unix epoch time values (integers cast to a timestamp).
  • Double-quote mixed case, camel case or case-sensitive identifiers.
  • Double-quote identifiers that contain special characters or whitespace characters.
Quoting examples
-- Double-quote identifiers that contain whitespace
SELECT "water temperature", "buoy location" FROM buoy

-- Double-quote measurement names with special characters
SELECT * FROM "h2o-temperature"

-- Double-quote identifiers that should be treated as case-sensitive
SELECT "pH" FROM "Water"

Note: We recommend always double-quoting identifiers, regardless of case-sensitivity.

Unquoted identifiers are not case-sensitive and match any measurement, tag key, or field key with the same characters, despite case. For example, if you have two fields in a measurement named ph and pH, the unquoted identifier, pH will match both. To query in a case-sensitive manner, double-quote identifiers.

Literals

A literal is an explicit value not represented by an identifier.

String literals

String literals are surrounded by single quotes.

'santa_monica'
'pH'
'average temperature'

Numeric literals

Number literals are positive or negative numbers that are either exact numbers or floats.

-- Integers
10
+10
-10

-- Unsigned integers
10::BIGINT UNSIGNED
+10::BIGINT UNSIGNED

-- Floats
10.78654
-100.56

Date and time literals

The following date and time literals are supported:

'2022-01-31T06:30:30.123Z'     -- (RFC3339) 
'2022-01-31T06:30:30.123'      -- (RFC3339-like)
'2022-01-31 06:30:30.123'      -- (RFC3339-like)
'2022-01-31 06:30:30'          -- ((RFC3339-like, no fractional seconds) 
1643610630123000000::TIMESTAMP -- (Unix epoch nanosecond cast to a timestamp)

Boolean literals

Boolean literals are either TRUE or FALSE.

Duration units

Interval literals specify a length or unit of time.

INTERVAL '4 minutes'
INTERVAL '12 days 6 hours 30 minutes'

The following units of time are supported:

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years
  • century

Operators

Operators are reserved words or characters which perform certain operations, including comparisons and arithmetic.

Arithmetic operators

Arithmetic operators take two numeric values (either literals or variables) and perform a calculation that returns a single numeric value.

OperatorDescriptionExampleResult
+Addition2 + 24
-Subtraction4 - 22
*Multiplication2 * 36
/Division6 / 32
%Modulo7 % 21

Comparison operators

Comparison operators evaluate the relationship between the left and right operands and TRUE or FALSE.

OperatorMeaningExample
=Equal to123 = 123
<>Not equal to123 <> 456
!=Not equal to123 != 456
>Greater than3 > 2
>=Greater than or equal to3 >= 2
<Less than1 < 2
<=Less than or equal to1 <= 2
~Matches a regular expression'abc' ~ 'a.*'
~\*Matches a regular expression (case-insensitive)'Abc' ~\* 'A.*'
!~Does not match a regular expression'abc' !~ 'd.*'
!~\*Does not match a regular expression (case-insensitive)'Abc' !~\* 'a.*'

Logical operators

OperatorMeaning
ANDReturns true if both operands are true. Otherwise, returns false.
BETWEENReturns true if the left operand is within the range of the right operand.
EXISTSReturns true if the operand is not null.
INReturns true if the left operand is in the right operand list.
LIKEReturns true if the left operand matches the right operand pattern string.
NOTNegates the subsequent expression.
ORReturns true if any operand is true. Otherwise, returns false.

Bitwise operators

Bitwise operators perform bitwise operations on bit patterns or binary numerals.

OperatorMeaningExampleResult
&Bitwise and5 & 31
|Bitwise or5 | 37
^Bitwise xor5 ^ 36
>>Bitwise shift right5 >> 30
<<Bitwise shift left5 << 340

Other operators

OperatorMeaningExampleResult
||Concatenates strings'Hello' || ' world'Hello world

Keywords

The following reserved keywords cannot be used as identifiers.

AND
ALL
ANALYZE
AS
ASC
BETWEEN
BOTTOM
CASE
DESC
DISTINCT
EXISTS
EXPLAIN
FROM
GROUP BY
HAVING
IN
INNER JOIN
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
EXISTS
NOT IN
OR
ORDER BY
FULL OUTER JOIN
RIGHT JOIN
SELECT
TOP
TYPE
UNION
UNION ALL
WHERE
WITH

Conditional expressions

Conditional expressions evaluate conditions based on input values. The following conditional expressions are supported:

ExpressionDescription
CASEAllows for use of WHEN-THEN-ELSE statements.
COALESCEReturns the first non-NULL expression in a specified list.
NULLIFReturns a NULL value if value1 = value2. Otherwise returns value1.

Statements and clauses

InfluxDB SQL supports the following basic syntax for queries:

[ WITH with_query [, ] ]  
SELECT [ ALL | DISTINCT ] select_expr [, ]  
[ FROM from_item [, ] ]  
[ JOIN join_item [, ] ]  
[ WHERE condition ]  
[ GROUP BY grouping_element [, ] ]  
[ HAVING condition]  
[ UNION [ ALL ] ]
[ ORDER BY expression [ ASC | DESC ][, ] ]  
[ LIMIT count ]  

SELECT statement and FROM clause

Use the SQL SELECT statement to query data from a specific measurement or measurements. The FROM clause always accompanies the SELECT statement.

Examples

SELECT * FROM "h2o_feet"

WHERE clause

Use the WHERE clause to filter results based on fields, tags, and timestamps. Use predicates to evaluate each row. Rows that evaluate as TRUE are returned in the result set. Rows that evaluate as FALSE are omitted from the result set.

Examples

SELECT * FROM "h2o_feet" WHERE "water_level" <= 9
SELECT 
  * 
FROM 
  "h2o_feet" 
WHERE 
  "location" = 'santa_monica' 
  AND "level description" = 'below 3 feet'

JOIN clause

Use the JOIN clause to join data from multiple measurements (tables). The following joins are supported:

Inner join

Left outer join

Right outer join

Full outer join

The INNER JOIN clause gathers data where there is a match between the two measurements being joined.

SELECT 
  * 
FROM 
  h2o_feet 
  INNER JOIN h2o_temperature ON h2o_feet.location = h2o_temperature.location 
  AND h2o_feet.time = h2o_temperature.time

The LEFT JOIN and LEFT OUTER JOIN clauses gather data from all rows in the left table regardless of whether there is a match in the right table.

SELECT 
  * 
FROM 
  h2o_feet 
  LEFT OUTER JOIN h2o_temperature ON h2o_feet.location = h2o_temperature.location 
  AND h2o_feet.time = h2o_temperature.time

The RIGHT JOIN and RIGHT OUTER JOIN clauses gather data from all rows in the right table regardless of whether there is a match in the left table

SELECT 
  * 
FROM 
  h2o_feet 
  RIGHT OUTER JOIN h2o_temperature ON h2o_feet.location = h2o_temperature.location 
  AND h2o_feet.time = h2o_temperature.time

The FULL JOIN and FULL OUTER JOIN clauses return all rows from the left and the right side of the join with NULL values where there is no match.

SELECT 
  * 
FROM 
  h2o_feet
  FULL JOIN h2o_temperature ON h2o_feet.location = h2o_temperature.location 
  AND h2o_feet.time = h2o_temperature.time

GROUP BY clause

Use the GROUP BY clause to group query results based on specified column values. GROUP BY requires an aggregate or selector function in the SELECT statement.

Examples

SELECT 
  MEAN("water_level"), 
  "location" 
FROM 
  "h2o_feet" 
GROUP BY 
  "location"

HAVING clause

Use the HAVING clause to filter query results based on a specified condition. The HAVING clause must follow the GROUP BY clause, but precede the ORDER BY clause.

Examples

SELECT 
  MEAN("water_level"), 
  "location" 
FROM 
  "h2o_feet" 
GROUP BY 
  "location" 
HAVING 
  MEAN("water_level") > 4
ORDER BY
  "location"

UNION clause

The UNION clause combines the results of two or more SELECT statements without returning any duplicate rows. UNION ALL returns all results, including duplicates.

Examples

SELECT 
  'pH' 
FROM 
  "h2o_pH" 
UNION ALL 
SELECT 
  "location" 
FROM 
  "h2o_quality"

ORDER BY clause

The ORDER BY clause orders results by specified columns and order. Sort data based on fields, tags, and timestamps. The following orders are supported:

  • ASC: ascending (default)
  • DESC: descending

Examples

SELECT 
  "water_level", 
  "location" 
FROM 
  "h2o_feet" 
ORDER BY 
  "location", 
  "time" DESC

LIMIT clause

The LIMIT clause limits the number of rows to return. The defined limit should be a non-negative integer.

Examples

SELECT 
  "water_level", 
  "location" 
FROM 
  "h2o_feet" 
LIMIT 
  10

WITH clause

The WITH clause provides a way to write auxiliary statements for use in a larger query. It can help break down large, complicated queries into simpler forms.

WITH summary_data as
(SELECT degrees, location, time 
  FROM average_temperature)
SELECT * FROM summary_data

OVER clause

The OVER clause is used with SQL window functions. A window function performs a calculation across a set of table rows that are related in some way to the current row. While similar to aggregate functions, window functions output results into rows retaining their separate identities.

SELECT 
  time, 
  water_level 
FROM 
  (
    SELECT 
      time, 
      "water_level", 
      row_number() OVER (
        order by 
          water_level desc
      ) as rn 
    FROM 
      h2o_feet
  ) 
WHERE 
  rn <= 3;

Comments

Use comments to describe and add detail or notes to your queries.

  • Single line comments use the double hyphen -- symbol. Single line comments end with a line break.
  • Multi-line comments begin with /* and end with */.
-- Single-line comment

/* 
 * Multi-line comment
 */

Schema information

InfluxDB Cloud Dedicated supports the following metadata schema queries:

SHOW tables

SHOW columns FROM <measurement>

Functions

Following is a list of supported functions by type.

Aggregate functions

An aggregate function performs a calculation or computation on a set of data values in a column and returns a single value.

FunctionDescription
COUNT()Returns returns the number of rows from a field or tag key
AVG()Returns the average value of a column
SUM()Returns the summed value of a column
MEAN()Returns the mean value of a column
MIN()Returns the smallest value of the selected column
MAX()Returns the largest value of the selected column

Examples


SELECT COUNT("water_level") 
FROM "h2o_feet"

SELECT AVG("water_level"), "location"
FROM "h2o_feet" 
GROUP BY "location"

SELECT SUM("water_level"), "location"
FROM "h2o_feet" 
GROUP BY "location"

Selector functions

Selector functions are unique to InfluxDB. They behave like aggregate functions in that they take a row of data and compute it down to a single value. However, selectors are unique in that they return a time value in addition to the computed value. In short, selectors return an aggregated value along with a timestamp.

FunctionDescription
SELECTOR_FIRST()Returns the first value of a selected column and timestamp.
SELECTOR_LAST()Returns the last value of a selected column and timestamp.
SELECTOR_MIN()Returns the smallest value of a selected column and timestamp.
SELECTOR_MAX()Returns the largest value of a selected column and timestamp.

Examples

SELECT 
SELECTOR_MAX("pH", time)['value'],
SELECTOR_MAX("pH", time)['time']
FROM "h2o_pH"

SELECT 
SELECTOR_LAST("water_level", time)['value'],
SELECTOR_LAST("water_level", time)['time']
FROM "h2o_feet"
WHERE time >= timestamp '2019-09-10T00:00:00Z' AND time <= timestamp '2019-09-19T00:00:00Z'

Date and time functions

FunctionDescription
DATE_BIN()Bins the input timestamp into a specified interval.
DATE_TRUNC()Truncates a timestamp expression based on the date part specified, such as hour, day, or month.
DATE_PART()Returns the specified part of a date.
NOW()Returns the current time (UTC).

Examples

SELECT DATE_BIN(INTERVAL '1 hour', time, '2019-09-18T00:00:00Z'::timestamp) AS "_time",
SUM(water_level)
FROM "h2o_feet"
GROUP BY "_time"
SELECT DATE_TRUNC('month',time) AS "date",
SUM(water_level)
FROM "h2o_feet"
GROUP BY time

Approximate functions

FunctionDescription
APPROX_MEDIANReturns the approximate median of input values.
APPROX_DISTINCTReturns the approximate count of the number of distinct values. Implemented only for strings.
APPROX_PERCENTILE_CONTReturns the approximate percentile of input values.
APPROX_PERCENTILE_CONT_WITH_WEIGHTReturns the approximate percentile of input values with weight.

Math functions

FunctionDescription
ABS()Absolute value
ACOS()Inverse cosine
ASIN()Inverse sine
ATAN()Inverse tangent
ATAN2()Inverse tangent of y / x
CEIL()Returns the smallest integer value greater than or equal to the specified number
COS()Cosine
EXP()Exponential
FLOOR()Nearest integer less than or equal to the specified number
LN()Natural logarithm
LOG10()Base 10 logarithm
LOG2()Base 2 logarithm
POWER()Returns the value of a number raised to the power of the number
ROUND()Round to the nearest integer
SIGNUM()Sign of the argument (-1, 0, +1)
SINE()Sine
SQRT()Returns the square root of a number
TAN()Tangent
TRUNC()Truncates a number to the specified number of decimal places

Conditional functions

FunctionDescription
COALESCEReturns the first argument that is not null. If all arguments are null, then COALESCE will return nulls.
NULLIFReturns a null value if value1 equals value2, otherwise returns value1.

Regular expression functions

FunctionDescription
REGEXP_MATCHMatches a regular expression against a string and returns matched substrings.
REGEXP_REPLACEReplaces substrings that match a regular expression by a new substring.

Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB Clustered

A highly available InfluxDB 3.0 cluster on your own infrastructure.

InfluxDB Clustered is a highly available InfluxDB 3.0 cluster built for high write and query workloads on your own infrastructure.

InfluxDB Clustered is currently in limited availability and is only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Learn more
Contact InfluxData Sales

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following:

State of the InfluxDB Cloud Serverless documentation

InfluxDB Cloud Serverless documentation is a work in progress.

The new documentation for InfluxDB Cloud Serverless is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.