Explore data using InfluxQL

InfluxQL is an SQL-like query language for interacting with data in InfluxDB. The following sections detail InfluxQL’s SELECT statement and useful query syntax for exploring your data.

The Basics:Configure Query Results:General Tips on Query Syntax:
The SELECT statementORDER BY time DESCTime Syntax
The WHERE clauseThe LIMIT and SLIMIT clausesRegular Expressions
The GROUP BY clauseThe OFFSET and SOFFSET clausesData types and cast operations
The INTO clauseThe Time Zone clauseMerge behavior
Multiple statements
Subqueries

Sample data

This document uses publicly available data from the National Oceanic and Atmospheric Administration’s (NOAA) Center for Operational Oceanographic Products and Services. See the Sample Data page to download the data and follow along with the example queries in the sections below.

Start by logging into the Influx CLI:

  1. $ influx -precision rfc3339 -database NOAA_water_database
  2. Connected to http://localhost:8086 version 1.8.x
  3. InfluxDB shell 1.8.x
  4. >

Next, get acquainted with this subsample of the data in the h2o_feet measurement:

name: h2o_feet

timelevel descriptionlocationwater_level
2015-08-18T00:00:00Zbetween 6 and 9 feetcoyote_creek8.12
2015-08-18T00:00:00Zbelow 3 feetsanta_monica2.064
2015-08-18T00:06:00Zbetween 6 and 9 feetcoyote_creek8.005
2015-08-18T00:06:00Zbelow 3 feetsanta_monica2.116
2015-08-18T00:12:00Zbetween 6 and 9 feetcoyote_creek7.887
2015-08-18T00:12:00Zbelow 3 feetsanta_monica2.028

The data in the h2o_feet measurement occur at six-minute time intervals. The measurement has one tag key (location) which has two tag values: coyote_creek and santa_monica. The measurement also has two fields: level description stores string field values and water_level stores float field values. All of these data is in the NOAA_water_database database.

Disclaimer: The level description field isn’t part of the original NOAA data - we snuck it in there for the sake of having a field key with a special character and string field values.

The basic SELECT statement

The SELECT statement queries data from a particular measurement or measurements.

Syntax

  1. SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

The SELECT statement requires a SELECT clause and a FROM clause.

SELECT clause

The SELECT clause supports several formats for specifying data:

SELECT * Returns all fields and tags.

SELECT "<field_key>" Returns a specific field.

SELECT "<field_key>","<field_key>" Returns more than one field.

SELECT "<field_key>","<tag_key>" Returns a specific field and tag. The SELECT clause must specify at least one field when it includes a tag.

SELECT "<field_key>"::field,"<tag_key>"::tag Returns a specific field and tag. The ::[field | tag] syntax specifies the identifier’s type. Use this syntax to differentiate between field keys and tag keys that have the same name.

Other supported features: Arithmetic operations, Functions, Basic cast operations, Regular expressions

Note: The SELECT statement cannot include an aggregate function and a non-aggregate function, field key, or tag key. For more information, see error about mixing aggregate and non-aggregate queries.

FROM clause

The FROM clause supports several formats for specifying a measurement(s):

FROM <measurement_name> Returns data from a single measurement. If you’re using the CLI InfluxDB queries the measurement in the USEd database and the DEFAULT retention policy. If you’re using the InfluxDB API InfluxDB queries the measurement in the database specified in the db query string parameter and the DEFAULT retention policy.

FROM <measurement_name>,<measurement_name> Returns data from more than one measurement.

FROM <database_name>.<retention_policy_name>.<measurement_name> Returns data from a fully qualified measurement. Fully qualify a measurement by specifying its database and retention policy.

FROM <database_name>..<measurement_name> Returns data from a measurement in a user-specified database and the DEFAULT retention policy.

Other supported features: Regular Expressions

Quoting

Identifiers must be double quoted if they contain characters other than [A-z,0-9,_], if they begin with a digit, or if they are an InfluxQL keyword. While not always necessary, we recommend that you double quote identifiers.

Note: The quoting syntax for queries differs from the line protocol. Please review the rules for single and double-quoting in queries.

Examples

Select all fields and tags from a single measurement

  1. > SELECT * FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The query selects all fields and tags from the h2o_feet measurement.

If you’re using the CLI be sure to enter USE NOAA_water_database before you run the query. The CLI queries the data in the USEd database and the DEFAULT retention policy. If you’re using the InfluxDB API be sure to set the db query string parameter to NOAA_water_database. If you do not set the rp query string parameter, the InfluxDB API automatically queries the database’s DEFAULT retention policy.

Select specific tags and fields from a single measurement

  1. > SELECT "level description","location","water_level" FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The query selects the level description field, the location tag, and the water_level field. Note that the SELECT clause must specify at least one field when it includes a tag.

Select specific tags and fields from a single measurement, and provide their identifier type

  1. > SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The query selects the level description field, the location tag, and the water_level field from the h2o_feet measurement. The ::[field | tag] syntax specifies if the identifier is a field or tag. Use ::[field | tag] to differentiate between an identical field key and tag key . That syntax is not required for most use cases.

Select all fields from a single measurement

  1. > SELECT *::field FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   4.938

The query selects all fields from the h2o_feet measurement. The SELECT clause supports combining the * syntax with the :: syntax.

Select a specific field from a measurement and perform basic arithmetic

  1. > SELECT ("water_level" * 2) + 4 FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-18T00:00:00Z   20.24
  7. 2015-08-18T00:00:00Z   8.128
  8. [...]
  9. 2015-09-18T21:36:00Z   14.132
  10. 2015-09-18T21:42:00Z   13.876

The query multiplies water_level’s field values by two and adds four to those values. Note that InfluxDB follows the standard order of operations. See Mathematical Operators for more on supported operators.

Select all data from more than one measurement

  1. > SELECT * FROM "h2o_feet","h2o_pH"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       pH   water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938
  12. name: h2o_pH
  13. ------------
  14. time                   level description   location       pH   water_level
  15. 2015-08-18T00:00:00Z                       santa_monica   6
  16. 2015-08-18T00:00:00Z                       coyote_creek   7
  17. [...]
  18. 2015-09-18T21:36:00Z                       santa_monica   8
  19. 2015-09-18T21:42:00Z                       santa_monica   7

The query selects all fields and tags from two measurements: h2o_feet and h2o_pH. Separate multiple measurements with a comma (,).

Select all data from a fully qualified measurement

  1. > SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The query selects data in the NOAA_water_database, the autogen retention policy, and the measurement h2o_feet.

In the CLI, fully qualify a measurement to query data in a database other than the USEd database and in a retention policy other than the DEFAULT retention policy. In the InfluxDB API, fully qualify a measurement in place of using the db and rp query string parameters if desired.

Select all data from a measurement in a particular database

  1. > SELECT * FROM "NOAA_water_database".."h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
  7. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  8. [...]
  9. 2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
  10. 2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

The query selects data in the NOAA_water_database, the DEFAULT retention policy, and the h2o_feet measurement. The .. indicates the DEFAULT retention policy for the specified database.

In the CLI, specify the database to query data in a database other than the USEd database. In the InfluxDB API, specify the database in place of using the db query string parameter if desired.

Common issues with the SELECT statement

Selecting tag keys in the SELECT clause

A query requires at least one field key in the SELECT clause to return data. If the SELECT clause only includes a single tag key or several tag keys, the query returns an empty response. This behavior is a result of how the system stores data.

Example

The following query returns no data because it specifies a single tag key (location) in the SELECT clause:

  1. > SELECT "location" FROM "h2o_feet"
  2. >

To return any data associated with the location tag key, the query’s SELECT clause must include at least one field key (water_level):

  1. > SELECT "water_level","location" FROM "h2o_feet"
  2. name: h2o_feet
  3. time                   water_level  location
  4. ----                   -----------  --------
  5. 2015-08-18T00:00:00Z   8.12         coyote_creek
  6. 2015-08-18T00:00:00Z   2.064        santa_monica
  7. [...]
  8. 2015-09-18T21:36:00Z   5.066        santa_monica
  9. 2015-09-18T21:42:00Z   4.938        santa_monica

The WHERE clause

The WHERE filters data based on fields, tags, and/or timestamps.

Tired of reading? Check out this InfluxQL Short:

Syntax

  1. SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]

The WHERE clause supports conditional_expressions on fields, tags, and timestamps.

Note InfluxDB does not support using OR in the WHERE clause to specify multiple time ranges. For example, InfluxDB returns an empty response for the following query:

> SELECT * FROM "absolutismus" WHERE time = '2016-07-31T20:07:00Z' OR time = '2016-07-31T23:07:17Z'

Fields

  1. field_key <operator> ['string' | boolean | float | integer]

The WHERE clause supports comparisons against string, boolean, float, and integer field values.

Single quote string field values in the WHERE clause. Queries with unquoted string field values or double quoted string field values will not return any data and, in most cases, will not return an error.

Supported operators
OperatorMeaning
=equal to
<>not equal to
!=not equal to
>greater than
>=greater than or equal to
<less than
<=less than or equal to

Other supported features: Arithmetic Operations, Regular Expressions

Tags

  1. tag_key <operator> ['tag_value']

Single quote tag values in the WHERE clause. Queries with unquoted tag values or double quoted tag values will not return any data and, in most cases, will not return an error.

Supported operators
OperatorMeaning
=equal to
<>not equal to
!=not equal to

Other supported features: Regular Expressions

Timestamps

For most SELECT statements, the default time range is between 1677-09-21 00:12:43.145224194 and 2262-04-11T23:47:16.854775806Z UTC. For SELECT statements with a GROUP BY time() clause, the default time range is between 1677-09-21 00:12:43.145224194 UTC and now().

The Time Syntax section on this page details how to specify alternative time ranges in the WHERE clause.

Examples

Select data that have specific field key-values

  1. > SELECT * FROM "h2o_feet" WHERE "water_level" > 8
  3. name: h2o_feet
  4. --------------
  5. time                   level description      location       water_level
  6. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
  7. 2015-08-18T00:06:00Z   between 6 and 9 feet   coyote_creek   8.005
  8. [...]
  9. 2015-09-18T00:12:00Z   between 6 and 9 feet   coyote_creek   8.189
  10. 2015-09-18T00:18:00Z   between 6 and 9 feet   coyote_creek   8.084

The query returns data from the h2o_feet measurement with field values of water_level that are greater than eight.

Select data that have a specific string field key-value

  1. > SELECT * FROM "h2o_feet" WHERE "level description" = 'below 3 feet'
  3. name: h2o_feet
  4. --------------
  5. time                   level description   location       water_level
  6. 2015-08-18T00:00:00Z   below 3 feet        santa_monica   2.064
  7. 2015-08-18T00:06:00Z   below 3 feet        santa_monica   2.116
  8. [...]
  9. 2015-09-18T14:06:00Z   below 3 feet        santa_monica   2.999
  10. 2015-09-18T14:36:00Z   below 3 feet        santa_monica   2.907

The query returns data from the h2o_feet measurement with field values of level description that equal the below 3 feet string. InfluxQL requires single quotes around string field values in the WHERE clause.

Select data that have a specific field key-value and perform basic arithmetic

  1. > SELECT * FROM "h2o_feet" WHERE "water_level" + 2 > 11.9
  3. name: h2o_feet
  4. --------------
  5. time                   level description           location       water_level
  6. 2015-08-29T07:06:00Z   at or greater than 9 feet   coyote_creek   9.902
  7. 2015-08-29T07:12:00Z   at or greater than 9 feet   coyote_creek   9.938
  8. 2015-08-29T07:18:00Z   at or greater than 9 feet   coyote_creek   9.957
  9. 2015-08-29T07:24:00Z   at or greater than 9 feet   coyote_creek   9.964
  10. 2015-08-29T07:30:00Z   at or greater than 9 feet   coyote_creek   9.954
  11. 2015-08-29T07:36:00Z   at or greater than 9 feet   coyote_creek   9.941
  12. 2015-08-29T07:42:00Z   at or greater than 9 feet   coyote_creek   9.925
  13. 2015-08-29T07:48:00Z   at or greater than 9 feet   coyote_creek   9.902
  14. 2015-09-02T23:30:00Z   at or greater than 9 feet   coyote_creek   9.902

The query returns data from the h2o_feet measurement with field values of water_level plus two that are greater than 11.9. Note that InfluxDB follows the standard order of operations See Mathematical Operators for more on supported operators.

Select data that have a specific tag key-value

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-18T00:00:00Z   2.064
  7. 2015-08-18T00:06:00Z   2.116
  8. [...]
  9. 2015-09-18T21:36:00Z   5.066
  10. 2015-09-18T21:42:00Z   4.938

The query returns data from the h2o_feet measurement where the tag key location is set to santa_monica. InfluxQL requires single quotes around tag values in the WHERE clause.

Select data that have specific field key-values and tag key-values

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" <> 'santa_monica' AND (water_level < -0.59 OR water_level > 9.95)
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-29T07:18:00Z   9.957
  7. 2015-08-29T07:24:00Z   9.964
  8. 2015-08-29T07:30:00Z   9.954
  9. 2015-08-29T14:30:00Z   -0.61
  10. 2015-08-29T14:36:00Z   -0.591
  11. 2015-08-30T15:18:00Z   -0.594

The query returns data from the h2o_feet measurement where the tag key location is not set to santa_monica and where the field values of water_level are either less than -0.59 or greater than 9.95. The WHERE clause supports the operators AND and OR, and supports separating logic with parentheses.

Select data that have specific timestamps

  1. > SELECT * FROM "h2o_feet" WHERE time > now() - 7d

The query returns data from the h2o_feet measurement that have timestamps within the past seven days. The Time Syntax section on this page offers in-depth information on supported time syntax in the WHERE clause.

Common issues with the WHERE clause

A WHERE clause query unexpectedly returns no data

In most cases, this issue is the result of missing single quotes around tag values or string field values. Queries with unquoted or double quoted tag values or string field values will not return any data and, in most cases, will not return an error.

The first two queries in the code block below attempt to specify the tag value santa_monica without any quotes and with double quotes. Those queries return no results. The third query single quotes santa_monica (this is the supported syntax) and returns the expected results.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = santa_monica
  3. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = "santa_monica"
  5. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'
  7. name: h2o_feet
  8. --------------
  9. time                   water_level
  10. 2015-08-18T00:00:00Z   2.064
  11. [...]
  12. 2015-09-18T21:42:00Z   4.938

The first two queries in the code block below attempt to specify the string field value at or greater than 9 feet without any quotes and with double quotes. The first query returns an error because the string field value includes white spaces. The second query returns no results. The third query single quotes at or greater than 9 feet (this is the supported syntax) and returns the expected results.

  1. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = at or greater than 9 feet
  3. ERR: error parsing query: found than, expected ; at line 1, char 86
  5. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = "at or greater than 9 feet"
  7. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = 'at or greater than 9 feet'
  9. name: h2o_feet
  10. --------------
  11. time                   level description
  12. 2015-08-26T04:00:00Z   at or greater than 9 feet
  13. [...]
  14. 2015-09-15T22:42:00Z   at or greater than 9 feet

The GROUP BY clause

The GROUP BY clause groups query results by:

  • one or more specified tags
  • specified time interval

Note: You cannot use GROUP BY to group fields.

GROUP BY tags
GROUP BY time intervals:Basic SyntaxAdvanced SyntaxGROUP BY time intervals and fill()

GROUP BY tags

GROUP BY <tag> groups query results by one or more specified tags.

Watch InfluxQL short about GROUP BY with tags:

Syntax

  1. SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | <tag_key>[,<tag_key]]

GROUP BY *    Groups results by all tags

GROUP BY <tag_key>    Groups results by a specific tag

GROUP BY <tag_key>,<tag_key>    Groups results by more than one tag. The order of the tag keys is irrelevant.

If the query includes a WHERE clause the GROUP BY clause must appear after the WHERE clause.

Other supported features: Regular Expressions

Examples

Group query results by a single tag
  1. > SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                           mean
  6. ----                           ----
  7. 1970-01-01T00:00:00Z     5.359342451341401
  10. name: h2o_feet
  11. tags: location=santa_monica
  12. time                           mean
  13. ----                           ----
  14. 1970-01-01T00:00:00Z     3.530863470081006

The query uses an InfluxQL function to calculate the average water_level for each tag value of location in the h2o_feet measurement. InfluxDB returns results in two series: one for each tag value of location.

Note: In InfluxDB, epoch 0 (1970-01-01T00:00:00Z) is often used as a null timestamp equivalent. If you request a query that has no timestamp to return, such as an aggregation function with an unbounded time range, InfluxDB returns epoch 0 as the timestamp.

Group query results by more than one tag
  1. > SELECT MEAN("index") FROM "h2o_quality" GROUP BY "location","randtag"
  3. name: h2o_quality
  4. tags: location=coyote_creek, randtag=1
  5. time                  mean
  6. ----                  ----
  7. 1970-01-01T00:00:00Z  50.69033760186263
  9. name: h2o_quality
  10. tags: location=coyote_creek, randtag=2
  11. time                   mean
  12. ----                   ----
  13. 1970-01-01T00:00:00Z   49.661867544220485
  15. name: h2o_quality
  16. tags: location=coyote_creek, randtag=3
  17. time                   mean
  18. ----                   ----
  19. 1970-01-01T00:00:00Z   49.360939907550076
  21. name: h2o_quality
  22. tags: location=santa_monica, randtag=1
  23. time                   mean
  24. ----                   ----
  25. 1970-01-01T00:00:00Z   49.132712456344585
  27. name: h2o_quality
  28. tags: location=santa_monica, randtag=2
  29. time                   mean
  30. ----                   ----
  31. 1970-01-01T00:00:00Z   50.2937984496124
  33. name: h2o_quality
  34. tags: location=santa_monica, randtag=3
  35. time                   mean
  36. ----                   ----
  37. 1970-01-01T00:00:00Z   49.99919903884662

The query uses an InfluxQL function to calculate the average index for each combination of the location tag and the randtag tag in the h2o_quality measurement. Separate multiple tags with a comma in the GROUP BY clause.

Group query results by all tags
  1. > SELECT MEAN("index") FROM "h2o_quality" GROUP BY *
  3. name: h2o_quality
  4. tags: location=coyote_creek, randtag=1
  5. time                           mean
  6. ----                           ----
  7. 1970-01-01T00:00:00Z     50.55405446521169
  10. name: h2o_quality
  11. tags: location=coyote_creek, randtag=2
  12. time                           mean
  13. ----                           ----
  14. 1970-01-01T00:00:00Z     50.49958856271162
  17. name: h2o_quality
  18. tags: location=coyote_creek, randtag=3
  19. time                           mean
  20. ----                           ----
  21. 1970-01-01T00:00:00Z     49.5164137518956
  24. name: h2o_quality
  25. tags: location=santa_monica, randtag=1
  26. time                           mean
  27. ----                           ----
  28. 1970-01-01T00:00:00Z     50.43829082296367
  31. name: h2o_quality
  32. tags: location=santa_monica, randtag=2
  33. time                           mean
  34. ----                           ----
  35. 1970-01-01T00:00:00Z     52.0688508894012
  38. name: h2o_quality
  39. tags: location=santa_monica, randtag=3
  40. time                           mean
  41. ----                           ----
  42. 1970-01-01T00:00:00Z     49.29386362086556

The query uses an InfluxQL function to calculate the average index for every possible tag combination in the h2o_quality measurement.

Note that the query results are identical to the results of the query in Example 2 where we explicitly specified the location and randtag tag keys. This is because the h2o_quality measurement only has two tag keys.

GROUP BY time intervals

GROUP BY time() queries group query results by a user-specified time interval.

Basic GROUP BY time() syntax

Syntax

  1. SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>),[tag_key] [fill(<fill_option>)]

Basic GROUP BY time() queries require an InfluxQL function in the SELECT clause and a time range in the WHERE clause. Note that the GROUP BY clause must come after the WHERE clause.

time(time_interval)

The time_interval in the GROUP BY time() clause is a duration literal. It determines how InfluxDB groups query results over time. For example, a time_interval of 5m groups query results into five-minute time groups across the time range specified in the WHERE clause.

fill(<fill_option>)

fill(<fill_option>) is optional. It changes the value reported for time intervals that have no data. See GROUP BY time intervals and fill() for more information.

Coverage:

Basic GROUP BY time() queries rely on the time_interval and on the InfluxDB database’s preset time boundaries to determine the raw data included in each time interval and the timestamps returned by the query.

Examples of basic syntax

The examples below use the following subsample of the sample data:

  1. > SELECT "water_level","location" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'
  3. name: h2o_feet
  4. --------------
  5. time                   water_level   location
  6. 2015-08-18T00:00:00Z   8.12          coyote_creek
  7. 2015-08-18T00:00:00Z   2.064         santa_monica
  8. 2015-08-18T00:06:00Z   8.005         coyote_creek
  9. 2015-08-18T00:06:00Z   2.116         santa_monica
  10. 2015-08-18T00:12:00Z   7.887         coyote_creek
  11. 2015-08-18T00:12:00Z   2.028         santa_monica
  12. 2015-08-18T00:18:00Z   7.762         coyote_creek
  13. 2015-08-18T00:18:00Z   2.126         santa_monica
  14. 2015-08-18T00:24:00Z   7.635         coyote_creek
  15. 2015-08-18T00:24:00Z   2.041         santa_monica
  16. 2015-08-18T00:30:00Z   7.5           coyote_creek
  17. 2015-08-18T00:30:00Z   2.051         santa_monica
Group query results into 12 minute intervals
  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. --------------
  5. time                   count
  6. 2015-08-18T00:00:00Z   2
  7. 2015-08-18T00:12:00Z   2
  8. 2015-08-18T00:24:00Z   2

The query uses an InfluxQL function to count the number of water_level points with the tag location = coyote_creek and it group results into 12 minute intervals.

The result for each timestamp represents a single 12 minute interval. The count for the first timestamp covers the raw data between 2015-08-18T00:00:00Z and up to, but not including, 2015-08-18T00:12:00Z. The count for the second timestamp covers the raw data between 2015-08-18T00:12:00Z and up to, but not including, 2015-08-18T00:24:00Z.

Group query results into 12 minutes intervals and by a tag key
  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location"
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   count
  6. ----                   -----
  7. 2015-08-18T00:00:00Z   2
  8. 2015-08-18T00:12:00Z   2
  9. 2015-08-18T00:24:00Z   2
  11. name: h2o_feet
  12. tags: location=santa_monica
  13. time                   count
  14. ----                   -----
  15. 2015-08-18T00:00:00Z   2
  16. 2015-08-18T00:12:00Z   2
  17. 2015-08-18T00:24:00Z   2

The query uses an InfluxQL function to count the number of water_level points. It groups results by the location tag and into 12 minute intervals. Note that the time interval and the tag key are separated by a comma in the GROUP BY clause.

The query returns two series of results: one for each tag value of the location tag. The result for each timestamp represents a single 12 minute interval. The count for the first timestamp covers the raw data between 2015-08-18T00:00:00Z and up to, but not including, 2015-08-18T00:12:00Z. The count for the second timestamp covers the raw data between 2015-08-18T00:12:00Z and up to, but not including, 2015-08-18T00:24:00Z.

Common issues with basic syntax

Unexpected timestamps and values in query results

With the basic syntax, InfluxDB relies on the GROUP BY time() interval and on the system’s preset time boundaries to determine the raw data included in each time interval and the timestamps returned by the query. In some cases, this can lead to unexpected results.

Example

Raw data:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:18:00Z'
  2. name: h2o_feet
  3. --------------
  4. time                   water_level
  5. 2015-08-18T00:00:00Z   8.12
  6. 2015-08-18T00:06:00Z   8.005
  7. 2015-08-18T00:12:00Z   7.887
  8. 2015-08-18T00:18:00Z   7.762

Query and results:

The following query covers a 12-minute time range and groups results into 12-minute time intervals, but it returns two results:

  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time < '2015-08-18T00:18:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. time                   count
  5. ----                   -----
  6. 2015-08-18T00:00:00Z   1        <----- Note that this timestamp occurs before the start of the query's time range
  7. 2015-08-18T00:12:00Z   1

Explanation:

InfluxDB uses preset round-number time boundaries for GROUP BY intervals that are independent of any time conditions in the WHERE clause. When it calculates the results, all returned data must occur within the query’s explicit time range but the GROUP BY intervals will be based on the preset time boundaries.

The table below shows the preset time boundary, the relevant GROUP BY time() interval, the points included, and the returned timestamp for each GROUP BY time() interval in the results.

Time Interval NumberPreset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:00:00Z AND time < 2015-08-18T00:12:00Ztime >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:12:00Z8.0052015-08-18T00:00:00Z
2time >= 2015-08-12T00:12:00Z AND time < 2015-08-18T00:24:00Ztime >= 2015-08-12T00:12:00Z AND time < 2015-08-18T00:18:00Z7.8872015-08-18T00:12:00Z

The first preset 12-minute time boundary begins at 00:00 and ends just before 00:12. Only one raw point (8.005) falls both within the query’s first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the query’s time range, the query result excludes data that occur before the query’s time range.

The second preset 12-minute time boundary begins at 00:12 and ends just before 00:24. Only one raw point (7.887) falls both within the query’s second GROUP BY time() interval and in that second time boundary.

The advanced GROUP BY time() syntax allows users to shift the start time of the InfluxDB database’s preset time boundaries. Example 3 in the Advanced Syntax section continues with the query shown here; it shifts forward the preset time boundaries by six minutes such that InfluxDB returns:

  1. name: h2o_feet
  2. time                   count
  3. ----                   -----
  4. 2015-08-18T00:06:00Z   2

Advanced GROUP BY time() syntax

Syntax

  1. SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>,<offset_interval>),[tag_key] [fill(<fill_option>)]

Advanced GROUP BY time() queries require an InfluxQL function in the SELECT clause and a time range in the WHERE clause. Note that the GROUP BY clause must come after the WHERE clause.

time(time_interval,offset_interval)

See the Basic GROUP BY time() Syntax for details on the time_interval.

The offset_interval is a duration literal. It shifts forward or back the InfluxDB database’s preset time boundaries. The offset_interval can be positive or negative.

fill(<fill_option>)

fill(<fill_option>) is optional. It changes the value reported for time intervals that have no data. See GROUP BY time intervals and fill() for more information.

Coverage:

Advanced GROUP BY time() queries rely on the time_interval, the offset_interval , and on the InfluxDB database’s preset time boundaries to determine the raw data included in each time interval and the timestamps returned by the query.

Examples of advanced syntax

The examples below use the following subsample of the sample data:

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:54:00Z'
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-18T00:00:00Z   8.12
  7. 2015-08-18T00:06:00Z   8.005
  8. 2015-08-18T00:12:00Z   7.887
  9. 2015-08-18T00:18:00Z   7.762
  10. 2015-08-18T00:24:00Z   7.635
  11. 2015-08-18T00:30:00Z   7.5
  12. 2015-08-18T00:36:00Z   7.372
  13. 2015-08-18T00:42:00Z   7.234
  14. 2015-08-18T00:48:00Z   7.11
  15. 2015-08-18T00:54:00Z   6.982
Group query results into 18 minute intervals and shift the preset time boundaries forward
  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,6m)
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-08-18T00:06:00Z   7.884666666666667
  7. 2015-08-18T00:24:00Z   7.502333333333333
  8. 2015-08-18T00:42:00Z   7.108666666666667

The query uses an InfluxQL function to calculate the average water_level, grouping results into 18 minute time intervals, and offsetting the preset time boundaries by six minutes.

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m)
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-08-18T00:00:00Z   7.946
  7. 2015-08-18T00:18:00Z   7.6323333333333325
  8. 2015-08-18T00:36:00Z   7.238666666666667
  9. 2015-08-18T00:54:00Z   6.982

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries:

Time Interval NumberPreset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:00:00Z AND time < 2015-08-18T00:18:00Ztime >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:18:00Z8.005,7.8872015-08-18T00:00:00Z
2time >= 2015-08-18T00:18:00Z AND time < 2015-08-18T00:36:00Z<—- same7.762,7.635,7.52015-08-18T00:18:00Z
3time >= 2015-08-18T00:36:00Z AND time < 2015-08-18T00:54:00Z<—- same7.372,7.234,7.112015-08-18T00:36:00Z
4time >= 2015-08-18T00:54:00Z AND time < 2015-08-18T01:12:00Ztime = 2015-08-18T00:54:00Z6.9822015-08-18T00:54:00Z

The first preset 18-minute time boundary begins at 00:00 and ends just before 00:18. Two raw points (8.005 and 7.887) fall both within the first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the query’s time range, the query result excludes data that occur before the query’s time range.

The second preset 18-minute time boundary begins at 00:18 and ends just before 00:36. Three raw points (7.762 and 7.635 and 7.5) fall both within the second GROUP BY time() interval and in that second time boundary. In this case, the boundary time range and the interval’s time range are the same.

The fourth preset 18-minute time boundary begins at 00:54 and ends just before 1:12:00. One raw point (6.982) falls both within the fourth GROUP BY time() interval and in that fourth time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

Time Interval NumberOffset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:24:00Z<—- same8.005,7.887,7.7622015-08-18T00:06:00Z
2time >= 2015-08-18T00:24:00Z AND time < 2015-08-18T00:42:00Z<—- same7.635,7.5,7.3722015-08-18T00:24:00Z
3time >= 2015-08-18T00:42:00Z AND time < 2015-08-18T01:00:00Z<—- same7.234,7.11,6.9822015-08-18T00:42:00Z
4time >= 2015-08-18T01:00:00Z AND time < 2015-08-18T01:18:00ZNANANA

The six-minute offset interval shifts forward the preset boundary’s time range such that the boundary time ranges and the relevant GROUP BY time() interval time ranges are always the same. With the offset, each interval performs the calculation on three points, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the fourth time boundary to be outside the query’s time range so the query returns no results for that last interval.

Group query results into 12 minute intervals and shift the preset time boundaries back
  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m,-12m)
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-08-18T00:06:00Z   7.884666666666667
  7. 2015-08-18T00:24:00Z   7.502333333333333
  8. 2015-08-18T00:42:00Z   7.108666666666667

The query uses an InfluxQL function to calculate the average water_level, grouping results into 18 minute time intervals, and offsetting the preset time boundaries by -12 minutes.

Note: The query in Example 2 returns the same results as the query in Example 1, but the query in Example 2 uses a negative offset_interval instead of a positive offset_interval. There are no performance differences between the two queries; feel free to choose the most intuitive option when deciding between a positive and negative offset_interval.

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time <= '2015-08-18T00:54:00Z' GROUP BY time(18m)
  3. name: h2o_feet
  4. time                    mean
  5. ----                    ----
  6. 2015-08-18T00:00:00Z    7.946
  7. 2015-08-18T00:18:00Z    7.6323333333333325
  8. 2015-08-18T00:36:00Z    7.238666666666667
  9. 2015-08-18T00:54:00Z    6.982

The time boundaries and returned timestamps for the query without the offset_interval adhere to the InfluxDB database’s preset time boundaries:

Time Interval NumberPreset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:00:00Z AND time < 2015-08-18T00:18:00Ztime >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:18:00Z8.005,7.8872015-08-18T00:00:00Z
2time >= 2015-08-18T00:18:00Z AND time < 2015-08-18T00:36:00Z<—- same7.762,7.635,7.52015-08-18T00:18:00Z
3time >= 2015-08-18T00:36:00Z AND time < 2015-08-18T00:54:00Z<—- same7.372,7.234,7.112015-08-18T00:36:00Z
4time >= 2015-08-18T00:54:00Z AND time < 2015-08-18T01:12:00Ztime = 2015-08-18T00:54:00Z6.9822015-08-18T00:54:00Z

The first preset 18-minute time boundary begins at 00:00 and ends just before 00:18. Two raw points (8.005 and 7.887) fall both within the first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the query’s time range, the query result excludes data that occur before the query’s time range.

The second preset 18-minute time boundary begins at 00:18 and ends just before 00:36. Three raw points (7.762 and 7.635 and 7.5) fall both within the second GROUP BY time() interval and in that second time boundary. In this case, the boundary time range and the interval’s time range are the same.

The fourth preset 18-minute time boundary begins at 00:54 and ends just before 1:12:00. One raw point (6.982) falls both within the fourth GROUP BY time() interval and in that fourth time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

Time Interval NumberOffset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-17T23:48:00Z AND time < 2015-08-18T00:06:00ZNANANA
2time >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:24:00Z<—- same8.005,7.887,7.7622015-08-18T00:06:00Z
3time >= 2015-08-18T00:24:00Z AND time < 2015-08-18T00:42:00Z<—- same7.635,7.5,7.3722015-08-18T00:24:00Z
4time >= 2015-08-18T00:42:00Z AND time < 2015-08-18T01:00:00Z<—- same7.234,7.11,6.9822015-08-18T00:42:00Z

The negative 12-minute offset interval shifts back the preset boundary’s time range such that the boundary time ranges and the relevant GROUP BY time() interval time ranges are always the same. With the offset, each interval performs the calculation on three points, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the first time boundary to be outside the query’s time range so the query returns no results for that first interval.

Group query results into 12 minute intervals and shift the preset time boundaries forward

This example is a continuation of the scenario outlined in Common Issues with Basic Syntax.

  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time < '2015-08-18T00:18:00Z' GROUP BY time(12m,6m)
  3. name: h2o_feet
  4. time                   count
  5. ----                   -----
  6. 2015-08-18T00:06:00Z   2

The query uses an InfluxQL function to count the number of water_level points, grouping results into 12 minute time intervals, and offsetting the preset time boundaries by six minutes.

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries. Let’s first examine the results without the offset:

  1. > SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:06:00Z' AND time < '2015-08-18T00:18:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. time                   count
  5. ----                   -----
  6. 2015-08-18T00:00:00Z   1
  7. 2015-08-18T00:12:00Z   1

The time boundaries and returned timestamps for the query without the offset_interval adhere to InfluxDB database’s preset time boundaries:

Time Interval NumberPreset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:00:00Z AND time < 2015-08-18T00:12:00Ztime >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:12:00Z8.0052015-08-18T00:00:00Z
2time >= 2015-08-12T00:12:00Z AND time < 2015-08-18T00:24:00Ztime >= 2015-08-12T00:12:00Z AND time < 2015-08-18T00:18:00Z7.8872015-08-18T00:12:00Z

The first preset 12-minute time boundary begins at 00:00 and ends just before 00:12. Only one raw point (8.005) falls both within the query’s first GROUP BY time() interval and in that first time boundary. Note that while the returned timestamp occurs before the start of the query’s time range, the query result excludes data that occur before the query’s time range.

The second preset 12-minute time boundary begins at 00:12 and ends just before 00:24. Only one raw point (7.887) falls both within the query’s second GROUP BY time() interval and in that second time boundary.

The time boundaries and returned timestamps for the query with the offset_interval adhere to the offset time boundaries:

Time Interval NumberOffset Time BoundaryGROUP BY time() IntervalPoints IncludedReturned Timestamp
1time >= 2015-08-18T00:06:00Z AND time < 2015-08-18T00:18:00Z<—- same8.005,7.8872015-08-18T00:06:00Z
2time >= 2015-08-18T00:18:00Z AND time < 2015-08-18T00:30:00ZNANANA

The six-minute offset interval shifts forward the preset boundary’s time range such that the preset boundary time range and the relevant GROUP BY time() interval time range are the same. With the offset, the query returns a single result, and the timestamp returned matches both the start of the boundary time range and the start of the GROUP BY time() interval time range.

Note that offset_interval forces the second time boundary to be outside the query’s time range so the query returns no results for that second interval.

GROUP BY time intervals and fill()

fill() changes the value reported for time intervals that have no data.

Syntax

  1. SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(time_interval,[<offset_interval])[,tag_key] [fill(<fill_option>)]

By default, a GROUP BY time() interval with no data reports null as its value in the output column. fill() changes the value reported for time intervals that have no data. Note that fill() must go at the end of the GROUP BY clause if you’re GROUP(ing) BY several things (for example, both tags and a time interval).

fill_option

Any numerical value Reports the given numerical value for time intervals with no data.

linear         Reports the results of linear interpolation for time intervals with no data.

none Reports no timestamp and no value for time intervals with no data.

null Reports null for time intervals with no data but returns a timestamp. This is the same as the default behavior.

previous Reports the value from the previous time interval for time intervals with no data.

Examples

Example 1: fill(100) Example 2: fill(linear) Example 3: fill(none) Example 4: fill(null) Example 5: fill(previous)

Without fill(100):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z

With fill(100):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(100)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z   100

fill(100) changes the value reported for the time interval with no data to 100.

Without fill(linear):

  1. > SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m)
  3. name: pond
  4. time                   mean
  5. ----                   ----
  6. 2016-11-11T21:00:00Z   1
  7. 2016-11-11T21:12:00Z
  8. 2016-11-11T21:24:00Z   3
  9. 2016-11-11T21:36:00Z
  10. 2016-11-11T21:48:00Z
  11. 2016-11-11T22:00:00Z   6

With fill(linear):

  1. > SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)
  3. name: pond
  4. time                   mean
  5. ----                   ----
  6. 2016-11-11T21:00:00Z   1
  7. 2016-11-11T21:12:00Z   2
  8. 2016-11-11T21:24:00Z   3
  9. 2016-11-11T21:36:00Z   4
  10. 2016-11-11T21:48:00Z   5
  11. 2016-11-11T22:00:00Z   6

fill(linear) changes the value reported for the time interval with no data to the results of linear interpolation.

Note: The data in Example 2 are not in NOAA_water_database. We had to create a dataset with less regular data to work with fill(linear).

Without fill(none):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z

With fill(none):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(none)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235

fill(none) reports no value and no timestamp for the time interval with no data.

Without fill(null):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z

With fill(null):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(null)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z

fill(null) reports null as the value for the time interval with no data. That result matches the result of the query without fill(null).

Without fill(previous):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z

With fill(previous):

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-09-18T16:00:00Z' AND time <= '2015-09-18T16:42:00Z' GROUP BY time(12m) fill(previous)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:00:00Z   3.599
  7. 2015-09-18T16:12:00Z   3.402
  8. 2015-09-18T16:24:00Z   3.235
  9. 2015-09-18T16:36:00Z   3.235

fill(previous) changes the value reported for the time interval with no data to 3.235, the value from the previous time interval.

Common issues with fill()

Queries with fill() when no data fall within the query’s time range

Currently, queries ignore fill() if no data fall within the query’s time range. This is the expected behavior. An open feature request on GitHub proposes that fill() should force a return of values even if the query’s time range covers no data.

Example

The following query returns no data because water_level has no points within the query’s time range. Note that fill(800) has no effect on the query results.

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'coyote_creek' AND time >= '2015-09-18T22:00:00Z' AND time <= '2015-09-18T22:18:00Z' GROUP BY time(12m) fill(800)
  2. >
Queries with fill(previous) when the previous result falls outside the query’s time range

fill(previous) doesn’t fill the result for a time interval if the previous value is outside the query’s time range.

Example

The following query covers the time range between 2015-09-18T16:24:00Z and 2015-09-18T16:54:00Z. Note that fill(previous) fills the result for 2015-09-18T16:36:00Z with the result from 2015-09-18T16:24:00Z.

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE location = 'coyote_creek' AND time >= '2015-09-18T16:24:00Z' AND time <= '2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:24:00Z   3.235
  7. 2015-09-18T16:36:00Z   3.235
  8. 2015-09-18T16:48:00Z   4

The next query shortens the time range in the previous query. It now covers the time between 2015-09-18T16:36:00Z and 2015-09-18T16:54:00Z. Note that fill(previous) doesn’t fill the result for 2015-09-18T16:36:00Z with the result from 2015-09-18T16:24:00Z; the result for 2015-09-18T16:24:00Z is outside the query’s shorter time range.

  1. > SELECT MAX("water_level") FROM "h2o_feet" WHERE location = 'coyote_creek' AND time >= '2015-09-18T16:36:00Z' AND time <= '2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous)
  3. name: h2o_feet
  4. --------------
  5. time                   max
  6. 2015-09-18T16:36:00Z
  7. 2015-09-18T16:48:00Z   4
fill(linear) when the previous or following result falls outside the query’s time range

fill(linear) doesn’t fill the result for a time interval with no data if the previous result or the following result is outside the query’s time range.

Example

The following query covers the time range between 2016-11-11T21:24:00Z and 2016-11-11T22:06:00Z. Note that fill(linear) fills the results for the 2016-11-11T21:36:00Z time interval and the 2016-11-11T21:48:00Z time interval using the values from the 2016-11-11T21:24:00Z time interval and the 2016-11-11T22:00:00Z time interval.

  1. > SELECT MEAN("tadpoles") FROM "pond" WHERE time > '2016-11-11T21:24:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)
  3. name: pond
  4. time                   mean
  5. ----                   ----
  6. 2016-11-11T21:24:00Z   3
  7. 2016-11-11T21:36:00Z   4
  8. 2016-11-11T21:48:00Z   5
  9. 2016-11-11T22:00:00Z   6

The next query shortens the time range in the previous query. It now covers the time between 2016-11-11T21:36:00Z and 2016-11-11T22:06:00Z. Note that fill() previous doesn’t fill the results for the 2016-11-11T21:36:00Z time interval and the 2016-11-11T21:48:00Z time interval; the result for 2016-11-11T21:24:00Z is outside the query’s shorter time range and InfluxDB cannot perform the linear interpolation.

  1. > SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:36:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear)
  2. name: pond
  3. time                   mean
  4. ----                   ----
  5. 2016-11-11T21:36:00Z
  6. 2016-11-11T21:48:00Z
  7. 2016-11-11T22:00:00Z   6

Note: The data in Issue 3 are not in NOAA_water_database. We had to create a dataset with less regular data to work with fill(linear).

The INTO clause

The INTO clause writes query results to a user-specified measurement.

Syntax

  1. SELECT_clause INTO <measurement_name> FROM_clause [WHERE_clause] [GROUP_BY_clause]

The INTO clause supports several formats for specifying a measurement:

INTO <measurement_name> Writes data to the specified measurement. If you’re using the CLI InfluxDB writes the data to the measurement in the USEd database and the DEFAULT retention policy. If you’re using the InfluxDB API InfluxDB writes the data to the measurement in the database specified in the db query string parameter and the DEFAULT retention policy.

INTO <database_name>.<retention_policy_name>.<measurement_name> Writes data to a fully qualified measurement. Fully qualify a measurement by specifying its database and retention policy.

INTO <database_name>..<measurement_name> Writes data to a measurement in a user-specified database and the DEFAULT retention policy.

INTO <database_name>.<retention_policy_name>.:MEASUREMENT FROM /<regular_expression>/ Writes data to all measurements in the user-specified database and retention policy that match the regular expression in the FROM clause. :MEASUREMENT is a backreference to each measurement matched in the FROM clause.

Examples

Rename a database

  1. > SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/ GROUP BY *
  3. name: result
  4. time written
  5. ---- -------
  6. 0    76290

Directly renaming a database in InfluxDB is not possible, so a common use for the INTO clause is to move data from one database to another. The query above writes all data in the NOAA_water_database and autogen retention policy to the copy_NOAA_water_database database and the autogen retention policy.

The backreference syntax (:MEASUREMENT) maintains the source measurement names in the destination database. Note that both the copy_NOAA_water_database database and its autogen retention policy must exist prior to running the INTO query. See Database Management for how to manage databases and retention policies.

The GROUP BY * clause preserves tags in the source database as tags in the destination database. The following query does not maintain the series context for tags; tags will be stored as fields in the destination database (copy_NOAA_water_database):

  1. SELECT * INTO "copy_NOAA_water_database"."autogen".:MEASUREMENT FROM "NOAA_water_database"."autogen"./.*/

When moving large amounts of data, we recommend sequentially running INTO queries for different measurements and using time boundaries in the WHERE clause. This prevents your system from running out of memory. The codeblock below provides sample syntax for those queries:

  1. SELECT *
  2. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  3. FROM <source_database>.<retention_policy_name>.<measurement_name>
  4. WHERE time > now() - 100w AND time < now() - 90w GROUP BY *
  6. SELECT *
  7. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  8. FROM <source_database>.<retention_policy_name>.<measurement_name>}
  9. WHERE time > now() - 90w AND < now() - 80w GROUP BY *
  11. SELECT *
  12. INTO <destination_database>.<retention_policy_name>.<measurement_name>
  13. FROM <source_database>.<retention_policy_name>.<measurement_name>
  14. WHERE time > now() - 80w AND time < now() - 70w GROUP BY *

Write the results of a query to a measurement

  1. > SELECT "water_level" INTO "h2o_feet_copy_1" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
  3. name: result
  4. ------------
  5. time                   written
  6. 1970-01-01T00:00:00Z   7604
  8. > SELECT * FROM "h2o_feet_copy_1"
  10. name: h2o_feet_copy_1
  11. ---------------------
  12. time                   water_level
  13. 2015-08-18T00:00:00Z   8.12
  14. [...]
  15. 2015-09-18T16:48:00Z   4

The query writes its results a new measurement: h2o_feet_copy_1. If you’re using the CLI, InfluxDB writes the data to the USEd database and the DEFAULT retention policy. If you’re using the InfluxDB API, InfluxDB writes the data to the database and retention policy specified in the db and rp query string parameters. If you do not set the rp query string parameter, the InfluxDB API automatically writes the data to the database’s DEFAULT retention policy.

The response shows the number of points (7605) that InfluxDB writes to h2o_feet_copy_1. The timestamp in the response is meaningless; InfluxDB uses epoch 0 (1970-01-01T00:00:00Z) as a null timestamp equivalent.

Write the results of a query to a fully qualified measurement

  1. > SELECT "water_level" INTO "where_else"."autogen"."h2o_feet_copy_2" FROM "h2o_feet" WHERE "location" = 'coyote_creek'
  3. name: result
  4. ------------
  5. time                   written
  6. 1970-01-01T00:00:00Z   7604
  8. > SELECT * FROM "where_else"."autogen"."h2o_feet_copy_2"
  10. name: h2o_feet_copy_2
  11. ---------------------
  12. time                   water_level
  13. 2015-08-18T00:00:00Z   8.12
  14. [...]
  15. 2015-09-18T16:48:00Z   4

The query writes its results to a new measurement: h2o_feet_copy_2. InfluxDB writes the data to the where_else database and to the autogen retention policy. Note that both where_else and autogen must exist prior to running the INTO query. See Database Management for how to manage databases and retention policies.

The response shows the number of points (7605) that InfluxDB writes to h2o_feet_copy_2. The timestamp in the response is meaningless; InfluxDB uses epoch 0 (1970-01-01T00:00:00Z) as a null timestamp equivalent.

Write aggregated results to a measurement (downsampling)

  1. > SELECT MEAN("water_level") INTO "all_my_averages" FROM "h2o_feet" WHERE "location" = 'coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
  3. name: result
  4. ------------
  5. time                   written
  6. 1970-01-01T00:00:00Z   3
  8. > SELECT * FROM "all_my_averages"
  10. name: all_my_averages
  11. ---------------------
  12. time                   mean
  13. 2015-08-18T00:00:00Z   8.0625
  14. 2015-08-18T00:12:00Z   7.8245
  15. 2015-08-18T00:24:00Z   7.5675

The query aggregates data using an InfluxQL function and a GROUP BY time() clause. It also writes its results to the all_my_averages measurement.

The response shows the number of points (3) that InfluxDB writes to all_my_averages. The timestamp in the response is meaningless; InfluxDB uses epoch 0 (1970-01-01T00:00:00Z) as a null timestamp equivalent.

The query is an example of downsampling: taking higher precision data, aggregating those data to a lower precision, and storing the lower precision data in the database. Downsampling is a common use case for the INTO clause.

Write aggregated results for more than one measurement to a different database (downsampling with backreferencing)

  1. > SELECT MEAN(*) INTO "where_else"."autogen".:MEASUREMENT FROM /.*/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:06:00Z' GROUP BY time(12m)
  3. name: result
  4. time                   written
  5. ----                   -------
  6. 1970-01-01T00:00:00Z   5
  8. > SELECT * FROM "where_else"."autogen"./.*/
  10. name: average_temperature
  11. time                   mean_degrees   mean_index   mean_pH   mean_water_level
  12. ----                   ------------   ----------   -------   ----------------
  13. 2015-08-18T00:00:00Z   78.5
  15. name: h2o_feet
  16. time                   mean_degrees   mean_index   mean_pH   mean_water_level
  17. ----                   ------------   ----------   -------   ----------------
  18. 2015-08-18T00:00:00Z                                         5.07625
  20. name: h2o_pH
  21. time                   mean_degrees   mean_index   mean_pH   mean_water_level
  22. ----                   ------------   ----------   -------   ----------------
  23. 2015-08-18T00:00:00Z                               6.75
  25. name: h2o_quality
  26. time                   mean_degrees   mean_index   mean_pH   mean_water_level
  27. ----                   ------------   ----------   -------   ----------------
  28. 2015-08-18T00:00:00Z                  51.75
  30. name: h2o_temperature
  31. time                   mean_degrees   mean_index   mean_pH   mean_water_level
  32. ----                   ------------   ----------   -------   ----------------
  33. 2015-08-18T00:00:00Z   63.75

The query aggregates data using an InfluxQL function and a GROUP BY time() clause. It aggregates data in every measurement that matches the regular expression in the FROM clause and writes the results to measurements with the same name in the where_else database and the autogen retention policy. Note that both where_else and autogen must exist prior to running the INTO query. See Database management for how to manage databases and retention policies.

The response shows the number of points (5) that InfluxDB writes to the where_else database and the autogen retention policy. The timestamp in the response is meaningless; InfluxDB uses epoch 0 (1970-01-01T00:00:00Z) as a null timestamp equivalent.

The query is an example of downsampling with backreferencing. It takes higher precision data from more than one measurement, aggregates those data to a lower precision, and stores the lower precision data in the database. Downsampling with backreferencing is a common use case for the INTO clause.

Common issues with the INTO clause

Missing data

If an INTO query includes a tag key in the SELECT clause, the query converts tags in the current measurement to fields in the destination measurement. This can cause InfluxDB to overwrite points that were previously differentiated by a tag value. Note that this behavior does not apply to queries that use the TOP() or BOTTOM() functions. The Frequently Asked Questions document describes that behavior in detail.

To preserve tags in the current measurement as tags in the destination measurement, GROUP BY the relevant tag key or GROUP BY * in the INTO query.

Automating queries with the INTO clause

The INTO clause section in this document shows how to manually implement queries with an INTO clause. See the Continuous Queries documentation for how to automate INTO clause queries on realtime data. Among other uses, Continuous Queries automate the downsampling process.

ORDER BY time DESC

By default, InfluxDB returns results in ascending time order; the first point returned has the oldest timestamp and the last point returned has the most recent timestamp. ORDER BY time DESC reverses that order such that InfluxDB returns the points with the most recent timestamps first.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] ORDER BY time DESC

ORDER by time DESC must appear after the GROUP BY clause if the query includes a GROUP BY clause. ORDER by time DESC must appear after the WHERE clause if the query includes a WHERE clause and no GROUP BY clause.

Examples

Return the newest points first

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' ORDER BY time DESC
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-09-18T21:42:00Z   4.938
  7. 2015-09-18T21:36:00Z   5.066
  8. [...]
  9. 2015-08-18T00:06:00Z   2.116
  10. 2015-08-18T00:00:00Z   2.064

The query returns the points with the most recent timestamps from the h2o_feet measurement first. Without ORDER by time DESC, the query would return 2015-08-18T00:00:00Z first and 2015-09-18T21:42:00Z last.

Return the newest points first and include a GROUP BY time() clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY time(12m) ORDER BY time DESC
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-08-18T00:36:00Z   4.6825
  7. 2015-08-18T00:24:00Z   4.80675
  8. 2015-08-18T00:12:00Z   4.950749999999999
  9. 2015-08-18T00:00:00Z   5.07625

The query uses an InfluxQL function and a time interval in the GROUP BY clause to calculate the average water_level for each twelve-minute interval in the query’s time range. ORDER BY time DESC returns the most recent 12-minute time intervals first.

Without ORDER BY time DESC, the query would return 2015-08-18T00:00:00Z first and 2015-08-18T00:36:00Z last.

The LIMIT and SLIMIT clauses

LIMIT and SLIMIT limit the number of points and the number of series returned per query.

The LIMIT clause

LIMIT <N> returns the first N points from the specified measurement.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT <N>

N specifies the number of points to return from the specified measurement. If N is greater than the number of points in a measurement, InfluxDB returns all points from that series.

Note that the LIMIT clause must appear in the order outlined in the syntax above.

Examples

Limit the number of points returned

  1. > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3
  3. name: h2o_feet
  4. time                   water_level   location
  5. ----                   -----------   --------
  6. 2015-08-18T00:00:00Z   8.12          coyote_creek
  7. 2015-08-18T00:00:00Z   2.064         santa_monica
  8. 2015-08-18T00:06:00Z   8.005         coyote_creek

The query returns the three oldest points (determined by timestamp) from the h2o_feet measurement.

Limit the number points returned and include a GROUP BY clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) LIMIT 2
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   mean
  6. ----                   ----
  7. 2015-08-18T00:00:00Z   8.0625
  8. 2015-08-18T00:12:00Z   7.8245
  10. name: h2o_feet
  11. tags: location=santa_monica
  12. time                   mean
  13. ----                   ----
  14. 2015-08-18T00:00:00Z   2.09
  15. 2015-08-18T00:12:00Z   2.077

The query uses an InfluxQL function and a GROUP BY clause to calculate the average water_level for each tag and for each twelve-minute interval in the query’s time range. LIMIT 2 requests the two oldest twelve-minute averages (determined by timestamp).

Note that without LIMIT 2, the query would return four points per series; one for each twelve-minute interval in the query’s time range.

The SLIMIT clause

SLIMIT <N> returns every point from <N> series in the specified measurement.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] SLIMIT <N>

N specifies the number of series to return from the specified measurement. If N is greater than the number of series in a measurement, InfluxDB returns all series from that measurement.

There is an ongoing issue that requires queries with SLIMIT to include GROUP BY *. Note that the SLIMIT clause must appear in the order outlined in the syntax above.

Examples

Limit the number of series returned

  1. > SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   water_level
  6. ----                   -----
  7. 2015-08-18T00:00:00Z   8.12
  8. 2015-08-18T00:06:00Z   8.005
  9. 2015-08-18T00:12:00Z   7.887
  10. [...]
  11. 2015-09-18T16:12:00Z   3.402
  12. 2015-09-18T16:18:00Z   3.314
  13. 2015-09-18T16:24:00Z   3.235

The query returns all water_level points from one of the series associated with the h2o_feet measurement.

Limit the number of series returned and include a GROUP BY time() clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) SLIMIT 1
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   mean
  6. ----                   ----
  7. 2015-08-18T00:00:00Z   8.0625
  8. 2015-08-18T00:12:00Z   7.8245
  9. 2015-08-18T00:24:00Z   7.5675
  10. 2015-08-18T00:36:00Z   7.303

The query uses an InfluxQL function and a time interval in the GROUP BY clause to calculate the average water_level for each twelve-minute interval in the query’s time range. SLIMIT 1 requests a single series associated with the h2o_feet measurement.

Note that without SLIMIT 1, the query would return results for the two series associated with the h2o_feet measurement: location=coyote_creek and location=santa_monica.

LIMIT and SLIMIT

LIMIT <N> followed by SLIMIT <N> returns the first <N> points from <N> series in the specified measurement.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(<time_interval>)] [ORDER_BY_clause] LIMIT <N1> SLIMIT <N2>

N1 specifies the number of points to return per measurement. If N1 is greater than the number of points in a measurement, InfluxDB returns all points from that measurement.

N2 specifies the number of series to return from the specified measurement. If N2 is greater than the number of series in a measurement, InfluxDB returns all series from that measurement.

There is an ongoing issue that requires queries with LIMIT and SLIMIT to include GROUP BY *. Note that the LIMIT and SLIMIT clauses must appear in the order outlined in the syntax above.

Examples

Limit the number of points and series returned

  1. > SELECT "water_level" FROM "h2o_feet" GROUP BY * LIMIT 3 SLIMIT 1
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   water_level
  6. ----                   -----------
  7. 2015-08-18T00:00:00Z   8.12
  8. 2015-08-18T00:06:00Z   8.005
  9. 2015-08-18T00:12:00Z   7.887

The query returns the three oldest points (determined by timestamp) from one of the series associated with the measurement h2o_feet.

Limit the number of points and series returned and include a GROUP BY time() clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) LIMIT 2 SLIMIT 1
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   mean
  6. ----                   ----
  7. 2015-08-18T00:00:00Z   8.0625
  8. 2015-08-18T00:12:00Z   7.8245

The query uses an InfluxQL function and a time interval in the GROUP BY clause to calculate the average water_level for each twelve-minute interval in the query’s time range. LIMIT 2 requests the two oldest twelve-minute averages (determined by timestamp) and SLIMIT 1 requests a single series associated with the h2o_feet measurement.

Note that without LIMIT 2 SLIMIT 1, the query would return four points for each of the two series associated with the h2o_feet measurement.

The OFFSET and SOFFSET clauses

OFFSET and SOFFSET paginates points and series returned.

The OFFSET clauseThe SOFFSET clause

The OFFSET clause

OFFSET <N> paginates N points in the query results.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]

N specifies the number of points to paginate. The OFFSET clause requires a LIMIT clause. Using the OFFSET clause without a LIMIT clause can cause inconsistent query results.

Note: InfluxDB returns no results if the WHERE clause includes a time range and the OFFSET clause would cause InfluxDB to return points with timestamps outside of that time range.

Examples

Paginate points

  1. > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3
  3. name: h2o_feet
  4. time                   water_level   location
  5. ----                   -----------   --------
  6. 2015-08-18T00:06:00Z   2.116         santa_monica
  7. 2015-08-18T00:12:00Z   7.887         coyote_creek
  8. 2015-08-18T00:12:00Z   2.028         santa_monica

The query returns the fourth, fifth, and sixth points from the h2o_feet measurement. If the query did not include OFFSET 3, it would return the first, second, and third points from that measurement.

Paginate points and include several clauses

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   mean
  6. ----                   ----
  7. 2015-08-18T00:12:00Z   7.8245
  8. 2015-08-18T00:00:00Z   8.0625

This example is pretty involved, so here’s the clause-by-clause breakdown:

The SELECT clause specifies an InfluxQL function. The FROM clause specifies a single measurement. The WHERE clause specifies the time range for the query. The GROUP BY clause groups results by all tags (*) and into 12-minute intervals. The ORDER BY time DESC clause returns results in descending timestamp order. The LIMIT 2 clause limits the number of points returned to two. The OFFSET 2 clause excludes the first two averages from the query results. The SLIMIT 1 clause limits the number of series returned to one.

Without OFFSET 2, the query would return the first two averages of the query results:

  1. name: h2o_feet
  2. tags: location=coyote_creek
  3. time                   mean
  4. ----                   ----
  5. 2015-08-18T00:36:00Z   7.303
  6. 2015-08-18T00:24:00Z   7.5675

The SOFFSET clause

SOFFSET <N> paginates N series in the query results.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(time_interval)] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] SLIMIT_clause SOFFSET <N>

N specifies the number of series to paginate. The SOFFSET clause requires an SLIMIT clause. Using the SOFFSET clause without an SLIMIT clause can cause inconsistent query results. There is an ongoing issue that requires queries with SLIMIT to include GROUP BY *.

Note: InfluxDB returns no results if the SOFFSET clause paginates through more than the total number of series.

Examples

Paginate series

  1. > SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1
  3. name: h2o_feet
  4. tags: location=santa_monica
  5. time                   water_level
  6. ----                   -----------
  7. 2015-08-18T00:00:00Z   2.064
  8. 2015-08-18T00:06:00Z   2.116
  9. [...]
  10. 2015-09-18T21:36:00Z   5.066
  11. 2015-09-18T21:42:00Z   4.938

The query returns data for the series associated with the h2o_feet measurement and the location = santa_monica tag. Without SOFFSET 1, the query returns data for the series associated with the h2o_feet measurement and the location = coyote_creek tag.

Paginate series and include all clauses

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1
  3. name: h2o_feet
  4. tags: location=santa_monica
  5. time                   mean
  6. ----                   ----
  7. 2015-08-18T00:12:00Z   2.077
  8. 2015-08-18T00:00:00Z   2.09

This example is pretty involved, so here’s the clause-by-clause breakdown:

The SELECT clause specifies an InfluxQL function. The FROM clause specifies a single measurement. The WHERE clause specifies the time range for the query. The GROUP BY clause groups results by all tags (*) and into 12-minute intervals. The ORDER BY time DESC clause returns results in descending timestamp order. The LIMIT 2 clause limits the number of points returned to two. The OFFSET 2 clause excludes the first two averages from the query results. The SLIMIT 1 clause limits the number of series returned to one. The SOFFSET 1 clause paginates the series returned.

Without SOFFSET 1, the query would return the results for a different series:

  1. name: h2o_feet
  2. tags: location=coyote_creek
  3. time                   mean
  4. ----                   ----
  5. 2015-08-18T00:12:00Z   7.8245
  6. 2015-08-18T00:00:00Z   8.0625

The Time Zone clause

The tz() clause returns the UTC offset for the specified timezone.

Syntax

  1. SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause] tz('<time_zone>')

By default, InfluxDB stores and returns timestamps in UTC. The tz() clause includes the UTC offset or, if applicable, the UTC Daylight Savings Time (DST) offset to the query’s returned timestamps. The returned timestamps must be in RFC3339 format for the UTC offset or UTC DST to appear. The time_zone parameter follows the TZ syntax in the Internet Assigned Numbers Authority time zone database and it requires single quotes.

Examples

Return the UTC offset for Chicago’s time zone

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:18:00Z' tz('America/Chicago')
  3. name: h2o_feet
  4. time                       water_level
  5. ----                       -----------
  6. 2015-08-17T19:00:00-05:00  2.064
  7. 2015-08-17T19:06:00-05:00  2.116
  8. 2015-08-17T19:12:00-05:00  2.028
  9. 2015-08-17T19:18:00-05:00  2.126

The query results include the UTC offset (-05:00) for the America/Chicago time zone in the timestamps.

Time syntax

For most SELECT statements, the default time range is between 1677-09-21 00:12:43.145224194 and 2262-04-11T23:47:16.854775806Z UTC. For SELECT statements with a GROUP BY time() clause, the default time range is between 1677-09-21 00:12:43.145224194 UTC and now(). The following sections detail how to specify alternative time ranges in the SELECT statement’s WHERE clause.

Absolute timeRelative timeCommon issues with time syntax

Tired of reading? Check out this InfluxQL Short:

Absolute time

Specify absolute time with date-time strings and epoch time.

Syntax

  1. SELECT_clause FROM_clause WHERE time <operator> ['<rfc3339_date_time_string>' | '<rfc3339_like_date_time_string>' | <epoch_time>] [AND ['<rfc3339_date_time_string>' | '<rfc3339_like_date_time_string>' | <epoch_time>] [...]]

Supported operators

OperatorMeaning
=equal to
<>not equal to
!=not equal to
>greater than
>=greater than or equal to
<less than
<=less than or equal to

Currently, InfluxDB does not support using OR with absolute time in the WHERE clause. See the Frequently Asked Questions document and the GitHub Issue for more information.

rfc3339_date_time_string

  1. 'YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ'

.nnnnnnnnn is optional and is set to .000000000 if not included. The RFC3339 date-time string requires single quotes.

rfc3339_like_date_time_string

  1. 'YYYY-MM-DD HH:MM:SS.nnnnnnnnn'

HH:MM:SS.nnnnnnnnn.nnnnnnnnn is optional and is set to 00:00:00.000000000 if not included. The RFC3339-like date-time string requires single quotes.

epoch_time

Epoch time is the amount of time that has elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.

By default, InfluxDB assumes that all epoch timestamps are in nanoseconds. Include a duration literal at the end of the epoch timestamp to indicate a precision other than nanoseconds.

Basic arithmetic

All timestamp formats support basic arithmetic. Add (+) or subtract (-) a time from a timestamp with a duration literal. Note that InfluxQL requires a whitespace between the + or - and the duration literal.

Examples

Specify a time range with RFC3339 date-time strings

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z'
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-08-18T00:00:00Z   2.064
  7. 2015-08-18T00:06:00Z   2.116
  8. 2015-08-18T00:12:00Z   2.028

The query returns data with timestamps between August 18, 2015 at 00:00:00.000000000 and August 18, 2015 at 00:12:00. The nanosecond specification in the first timestamp (.000000000) is optional.

Note that the single quotes around the RFC3339 date-time strings are required.

Specify a time range with RFC3339-like date-time strings

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18' AND time <= '2015-08-18 00:12:00'
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-08-18T00:00:00Z   2.064
  7. 2015-08-18T00:06:00Z   2.116
  8. 2015-08-18T00:12:00Z   2.028

The query returns data with timestamps between August 18, 2015 at 00:00:00 and August 18, 2015 at 00:12:00. The first date-time string does not include a time; InfluxDB assumes the time is 00:00:00.

Note that the single quotes around the RFC3339-like date-time strings are required.

Specify a time range with epoch timestamps

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000000000000 AND time <= 1439856720000000000
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-08-18T00:00:00Z   2.064
  7. 2015-08-18T00:06:00Z   2.116
  8. 2015-08-18T00:12:00Z   2.028

The query returns data with timestamps that occur between August 18, 2015 at 00:00:00 and August 18, 2015 at 00:12:00. By default InfluxDB assumes epoch timestamps are in nanoseconds.

Specify a time range with second-precision epoch timestamps

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000s AND time <= 1439856720s
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-08-18T00:00:00Z   2.064
  7. 2015-08-18T00:06:00Z   2.116
  8. 2015-08-18T00:12:00Z   2.028

The query returns data with timestamps that occur between August 18, 2015 at 00:00:00 and August 18, 2015 at 00:12:00. The s duration literal at the end of the epoch timestamps indicate that the epoch timestamps are in seconds.

Perform basic arithmetic on an RFC3339-like date-time string

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time > '2015-09-18T21:24:00Z' + 6m
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-09-18T21:36:00Z   5.066
  7. 2015-09-18T21:42:00Z   4.938

The query returns data with timestamps that occur at least six minutes after September 18, 2015 at 21:24:00. Note that the whitespace between the + and 6m is required.

Perform basic arithmetic on an epoch timestamp

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time > 24043524m - 6m
  3. name: h2o_feet
  4. time                   water_level
  5. ----                   -----------
  6. 2015-09-18T21:24:00Z   5.013
  7. 2015-09-18T21:30:00Z   5.01
  8. 2015-09-18T21:36:00Z   5.066
  9. 2015-09-18T21:42:00Z   4.938

The query returns data with timestamps that occur at least six minutes before September 18, 2015 at 21:24:00. Note that the whitespace between the - and 6m is required.

Relative time

Use now() to query data with timestamps relative to the server’s current timestamp.

Syntax

  1. SELECT_clause FROM_clause WHERE time <operator> now() [[ - | + ] <duration_literal>] [(AND|OR) now() [...]]

now() is the Unix time of the server at the time the query is executed on that server. The whitespace between - or + and the duration literal is required.

Supported operators

OperatorMeaning
=equal to
<>not equal to
!=not equal to
>greater than
>=greater than or equal to
<less than
<=less than or equal to

duration_literal

  • microseconds: u or µ
  • milliseconds: ms
  • secondss
  • minutesm
  • hours:h
  • days:d
  • weeks:w

Examples

Specify a time range with relative time

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time > now() - 1h

The query returns data with timestamps that occur within the past hour. The whitespace between - and 1h is required.

Specify a time range with absolute time and relative time

  1. > SELECT "level description" FROM "h2o_feet" WHERE time > '2015-09-18T21:18:00Z' AND time < now() + 1000d
  3. name: h2o_feet
  4. time                   level description
  5. ----                   -----------------
  6. 2015-09-18T21:24:00Z   between 3 and 6 feet
  7. 2015-09-18T21:30:00Z   between 3 and 6 feet
  8. 2015-09-18T21:36:00Z   between 3 and 6 feet
  9. 2015-09-18T21:42:00Z   between 3 and 6 feet

The query returns data with timestamps that occur between September 18, 2015 at 21:18:00 and 1000 days from now(). The whitespace between + and 1000d is required.

Common issues with time syntax

Using OR to select time multiple time intervals

InfluxDB does not support using the OR operator in the WHERE clause to specify multiple time intervals.

For more information, see Frequently asked questions.

Querying data that occur after now() with a GROUP BY time() clause

Most SELECT statements have a default time range between 1677-09-21 00:12:43.145224194 and 2262-04-11T23:47:16.854775806Z UTC. For SELECT statements with a GROUP BY time() clause, the default time range is between 1677-09-21 00:12:43.145224194 UTC and now().

To query data with timestamps that occur after now(), SELECT statements with a GROUP BY time() clause must provide an alternative upper bound in the WHERE clause.

Example

Use the CLI to write a point to the NOAA_water_database that occurs after now():

  1. > INSERT h2o_feet,location=santa_monica water_level=3.1 1587074400000000000

Run a GROUP BY time() query that covers data with timestamps between 2015-09-18T21:30:00Z and now():

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >= '2015-09-18T21:30:00Z' GROUP BY time(12m) fill(none)
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-09-18T21:24:00Z   5.01
  7. 2015-09-18T21:36:00Z   5.002

Run a GROUP BY time() query that covers data with timestamps between 2015-09-18T21:30:00Z and 180 weeks from now():

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >= '2015-09-18T21:30:00Z' AND time <= now() + 180w GROUP BY time(12m) fill(none)
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 2015-09-18T21:24:00Z   5.01
  7. 2015-09-18T21:36:00Z   5.002
  8. 2020-04-16T22:00:00Z   3.1

Note that the WHERE clause must provide an alternative upper bound to override the default now() upper bound. The following query merely resets the lower bound to now() such that the query’s time range is between now() and now():

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >= now() GROUP BY time(12m) fill(none)
  2. >

Configuring the returned timestamps

The CLI returns timestamps in nanosecond epoch format by default. Specify alternative formats with the precision <format> command. The InfluxDB API returns timestamps in RFC3339 format by default. Specify alternative formats with the epoch query string parameter.

Regular expressions

InfluxQL supports using regular expressions when specifying:

Currently, InfluxQL does not support using regular expressions to match non-string field values in the WHERE clause, databases, and retention polices.

Note: Regular expression comparisons are more computationally intensive than exact string comparisons; queries with regular expressions are not as performant as those without.

Syntax

  1. SELECT /<regular_expression_field_key>/ FROM /<regular_expression_measurement>/ WHERE [<tag_key> <operator> /<regular_expression_tag_value>/ | <field_key> <operator> /<regular_expression_field_value>/] GROUP BY /<regular_expression_tag_key>/

Regular expressions are surrounded by / characters and use Golang’s regular expression syntax.

Supported operators

=~ matches against !~ doesn’t match against

Examples

Use a regular expression to specify field keys and tag keys in the SELECT clause

  1. > SELECT /l/ FROM "h2o_feet" LIMIT 1
  3. name: h2o_feet
  4. time                   level description      location       water_level
  5. ----                   -----------------      --------       -----------
  6. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12

The query selects all field keys and tag keys that include an l. Note that the regular expression in the SELECT clause must match at least one field key in order to return results for a tag key that matches the regular expression.

Currently, there is no syntax to distinguish between regular expressions for field keys and regular expressions for tag keys in the SELECT clause. The syntax /<regular_expression>/::[field | tag] is not supported.

Use a regular expression to specify measurements in the FROM clause

  1. > SELECT MEAN("degrees") FROM /temperature/
  3. name: average_temperature
  4. time            mean
  5. ----            ----
  6. 1970-01-01T00:00:00Z   79.98472932232272
  8. name: h2o_temperature
  9. time            mean
  10. ----            ----
  11. 1970-01-01T00:00:00Z   64.98872722506226

The query uses an InfluxQL function to calculate the average degrees for every measurement in the NOAA_water_database database that contains the word temperature.

Use a regular expression to specify tag values in the WHERE clause

  1. > SELECT MEAN(water_level) FROM "h2o_feet" WHERE "location" =~ /[m]/ AND "water_level" > 3
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 1970-01-01T00:00:00Z   4.47155532049926

The query uses an InfluxQL function to calculate the average water_level where the tag value of location includes an m and water_level is greater than three.

Use a regular expression to specify a tag with no value in the WHERE clause

  1. > SELECT * FROM "h2o_feet" WHERE "location" !~ /./
  2. >

The query selects all data from the h2o_feet measurement where the location tag has no value. Every data point in the NOAA_water_database has a tag value for location.

It’s possible to perform this same query without a regular expression. See the Frequently Asked Questions document for more information.

Use a regular expression to specify a tag with a value in the WHERE clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" =~ /./
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 1970-01-01T00:00:00Z   4.442107025822523

The query uses an InfluxQL function to calculate the average water_level across all data that have a tag value for location.

Use a regular expression to specify a field value in the WHERE clause

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'santa_monica' AND "level description" =~ /between/
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 1970-01-01T00:00:00Z   4.47155532049926

The query uses an InfluxQL function to calculate the average water_level for all data where the field value of level description includes the word between.

Use a regular expression to specify tag keys in the GROUP BY clause

  1. > SELECT FIRST("index") FROM "h2o_quality" GROUP BY /l/
  3. name: h2o_quality
  4. tags: location=coyote_creek
  5. time                   first
  6. ----                   -----
  7. 2015-08-18T00:00:00Z   41
  9. name: h2o_quality
  10. tags: location=santa_monica
  11. time                   first
  12. ----                   -----
  13. 2015-08-18T00:00:00Z   99

The query uses an InfluxQL function to select the first value of index for every tag that includes the letter l in its tag key.

Data types and cast operations

The SELECT clause supports specifying a field’s type and basic cast operations with the :: syntax.

Data TypesCast Operations

Data types

Field values can be floats, integers, strings, or booleans. The :: syntax allows users to specify the field’s type in a query.

Note: Generally, it is not necessary to specify the field value type in the SELECT clause. In most cases, InfluxDB rejects any writes that attempt to write a field value to a field that previously accepted field values of a different type.

It is possible for field value types to differ across shard groups. In these cases, it may be necessary to specify the field value type in the SELECT clause. Please see the Frequently Asked Questions document for more information on how InfluxDB handles field value type discrepancies.

Syntax

  1. SELECT_clause <field_key>::<type> FROM_clause

type can be float, integer, string, or boolean. In most cases, InfluxDB returns no data if the field_key does not store data of the specified type. See Cast Operations for more information.

Example

  1. > SELECT "water_level"::float FROM "h2o_feet" LIMIT 4
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-18T00:00:00Z   8.12
  7. 2015-08-18T00:00:00Z   2.064
  8. 2015-08-18T00:06:00Z   8.005
  9. 2015-08-18T00:06:00Z   2.116

The query returns values of the water_level field key that are floats.

Cast operations

The :: syntax allows users to perform basic cast operations in queries. Currently, InfluxDB supports casting field values from integers to floats or from floats to integers.

Syntax

  1. SELECT_clause <field_key>::<type> FROM_clause

type can be float or integer.

InfluxDB returns no data if the query attempts to cast an integer or float to a string or boolean.

Examples

Cast float field values to integers

  1. > SELECT "water_level"::integer FROM "h2o_feet" LIMIT 4
  3. name: h2o_feet
  4. --------------
  5. time                   water_level
  6. 2015-08-18T00:00:00Z   8
  7. 2015-08-18T00:00:00Z   2
  8. 2015-08-18T00:06:00Z   8
  9. 2015-08-18T00:06:00Z   2

The query returns the integer form of water_level’s float field values.

Cast float field values to strings (this functionality is not supported)

  1. > SELECT "water_level"::string FROM "h2o_feet" LIMIT 4
  2. >

The query returns no data as casting a float field value to a string is not yet supported.

Merge behavior

In InfluxDB, queries merge series automatically.

Example

The h2o_feet measurement in the NOAA_water_database is part of two series. The first series is made up of the h2o_feet measurement and the location = coyote_creek tag. The second series is made of up the h2o_feet measurement and the location = santa_monica tag.

The following query automatically merges those two series when it calculates the average water_level:

  1. > SELECT MEAN("water_level") FROM "h2o_feet"
  3. name: h2o_feet
  4. --------------
  5. time                   mean
  6. 1970-01-01T00:00:00Z   4.442107025822521

If you want the average water_level for the first series only, specify the relevant tag in the WHERE clause:

  1. > SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location" = 'coyote_creek'
  3. name: h2o_feet
  4. --------------
  5. time                   mean
  6. 1970-01-01T00:00:00Z   5.359342451341401

If you want the average water_level for each individual series, include a GROUP BY clause:

  1. > SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   mean
  6. ----                   ----
  7. 1970-01-01T00:00:00Z   5.359342451341401
  9. name: h2o_feet
  10. tags: location=santa_monica
  11. time                   mean
  12. ----                   ----
  13. 1970-01-01T00:00:00Z   3.530863470081006

Multiple statements

Separate multiple SELECT statements in a query with a semicolon (;).

Examples

Example 1: CLI Example 2: InfluxDB API

In the InfluxDB CLI:

  1. > SELECT MEAN("water_level") FROM "h2o_feet"; SELECT "water_level" FROM "h2o_feet" LIMIT 2
  3. name: h2o_feet
  4. time                   mean
  5. ----                   ----
  6. 1970-01-01T00:00:00Z   4.442107025822522
  8. name: h2o_feet
  9. time                   water_level
  10. ----                   -----------
  11. 2015-08-18T00:00:00Z   8.12
  12. 2015-08-18T00:00:00Z   2.064

With the InfluxDB API:

  1. {
  2.     "results": [
  3.         {
  4.             "statement_id": 0,
  5.             "series": [
  6.                 {
  7.                     "name": "h2o_feet",
  8.                     "columns": [
  9.                         "time",
  10.                         "mean"
  11.                     ],
  12.                     "values": [
  13.                         [
  14.                             "1970-01-01T00:00:00Z",
  15.                             4.442107025822522
  16.                         ]
  17.                     ]
  18.                 }
  19.             ]
  20.         },
  21.         {
  22.             "statement_id": 1,
  23.             "series": [
  24.                 {
  25.                     "name": "h2o_feet",
  26.                     "columns": [
  27.                         "time",
  28.                         "water_level"
  29.                     ],
  30.                     "values": [
  31.                         [
  32.                             "2015-08-18T00:00:00Z",
  33.                             8.12
  34.                         ],
  35.                         [
  36.                             "2015-08-18T00:00:00Z",
  37.                             2.064
  38.                         ]
  39.                     ]
  40.                 }
  41.             ]
  42.         }
  43.     ]
  44. }

Subqueries

A subquery is a query that is nested in the FROM clause of another query. Use a subquery to apply a query as a condition in the enclosing query. Subqueries offer functionality similar to nested functions and SQL HAVING clauses.

Syntax

  1. SELECT_clause FROM ( SELECT_statement ) [...]

InfluxDB performs the subquery first and the main query second.

The main query surrounds the subquery and requires at least the SELECT clause and the FROM clause. The main query supports all clauses listed in this document.

The subquery appears in the main query’s FROM clause, and it requires surrounding parentheses. The subquery supports all clauses listed in this document.

InfluxQL supports multiple nested subqueries per main query. Sample syntax for multiple subqueries:

  1. SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]

Examples

Calculate the SUM() of several MAX() values

  1. > SELECT SUM("max") FROM (SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location")
  3. name: h2o_feet
  4. time                   sum
  5. ----                   ---
  6. 1970-01-01T00:00:00Z   17.169

The query returns the sum of the maximum water_level values across every tag value of location.

InfluxDB first performs the subquery; it calculates the maximum value of water_level for each tag value of location:

  1. > SELECT MAX("water_level") FROM "h2o_feet" GROUP BY "location"
  2. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   max
  6. ----                   ---
  7. 2015-08-29T07:24:00Z   9.964
  9. name: h2o_feet
  10. tags: location=santa_monica
  11. time                   max
  12. ----                   ---
  13. 2015-08-29T03:54:00Z   7.205

Next, InfluxDB performs the main query and calculates the sum of those maximum values: 9.964 + 7.205 = 17.169. Notice that the main query specifies max, not water_level, as the field key in the SUM() function.

Calculate the MEAN() difference between two fields

  1. > SELECT MEAN("difference") FROM (SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare")
  3. name: pet_daycare
  4. time                   mean
  5. ----                   ----
  6. 1970-01-01T00:00:00Z   1.75

The query returns the average of the differences between the number of cats and dogs in the pet_daycare measurement.

InfluxDB first performs the subquery. The subquery calculates the difference between the values in the cats field and the values in the dogs field, and it names the output column difference:

  1. > SELECT "cats" - "dogs" AS "difference" FROM "pet_daycare"
  3. name: pet_daycare
  4. time                   difference
  5. ----                   ----------
  6. 2017-01-20T00:55:56Z   -1
  7. 2017-01-21T00:55:56Z   -49
  8. 2017-01-22T00:55:56Z   66
  9. 2017-01-23T00:55:56Z   -9

Next, InfluxDB performs the main query and calculates the average of those differences. Notice that the main query specifies difference as the field key in the MEAN() function.

Calculate several MEAN() values and place a condition on those mean values

  1. > SELECT "all_the_means" FROM (SELECT MEAN("water_level") AS "all_the_means" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) ) WHERE "all_the_means" > 5
  3. name: h2o_feet
  4. time                   all_the_means
  5. ----                   -------------
  6. 2015-08-18T00:00:00Z   5.07625

The query returns all mean values of the water_level field that are greater than five.

InfluxDB first performs the subquery. The subquery calculates MEAN() values of water_level from 2015-08-18T00:00:00Z through 2015-08-18T00:30:00Z and groups the results into 12-minute intervals. It also names the output column all_the_means:

  1. > SELECT MEAN("water_level") AS "all_the_means" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m)
  3. name: h2o_feet
  4. time                   all_the_means
  5. ----                   -------------
  6. 2015-08-18T00:00:00Z   5.07625
  7. 2015-08-18T00:12:00Z   4.950749999999999
  8. 2015-08-18T00:24:00Z   4.80675

Next, InfluxDB performs the main query and returns only those mean values that are greater than five. Notice that the main query specifies all_the_means as the field key in the SELECT clause.

Calculate the SUM() of several DERIVATIVE() values

  1. > SELECT SUM("water_level_derivative") AS "sum_derivative" FROM (SELECT DERIVATIVE(MEAN("water_level")) AS "water_level_derivative" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location") GROUP BY "location"
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   sum_derivative
  6. ----                   --------------
  7. 1970-01-01T00:00:00Z   -0.4950000000000001
  9. name: h2o_feet
  10. tags: location=santa_monica
  11. time                   sum_derivative
  12. ----                   --------------
  13. 1970-01-01T00:00:00Z   -0.043999999999999595

The query returns the sum of the derivative of average water_level values for each tag value of location.

InfluxDB first performs the subquery. The subquery calculates the derivative of average water_level values taken at 12-minute intervals. It performs that calculation for each tag value of location and names the output column water_level_derivative:

  1. > SELECT DERIVATIVE(MEAN("water_level")) AS "water_level_derivative" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m),"location"
  3. name: h2o_feet
  4. tags: location=coyote_creek
  5. time                   water_level_derivative
  6. ----                   ----------------------
  7. 2015-08-18T00:12:00Z   -0.23800000000000043
  8. 2015-08-18T00:24:00Z   -0.2569999999999997
  10. name: h2o_feet
  11. tags: location=santa_monica
  12. time                   water_level_derivative
  13. ----                   ----------------------
  14. 2015-08-18T00:12:00Z   -0.0129999999999999
  15. 2015-08-18T00:24:00Z   -0.030999999999999694

Next, InfluxDB performs the main query and calculates the sum of the water_level_derivative values for each tag value of location. Notice that the main query specifies water_level_derivative, not water_level or derivative, as the field key in the SUM() function.

Common issues with subqueries

Multiple SELECT statements in a subquery

InfluxQL supports multiple nested subqueries per main query:

  1. SELECT_clause FROM ( SELECT_clause FROM ( SELECT_statement ) [...] ) [...]
  2.                      ------------------   ----------------
  3.                          Subquery 1          Subquery 2

InfluxQL does not support multiple SELECT statements per subquery:

  1. SELECT_clause FROM (SELECT_statement; SELECT_statement) [...]

The system returns a parsing error if a subquery includes multiple SELECT statements.