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.

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:

$ influx -precision rfc3339 -database NOAA_water_database
Connected to http://localhost:8086 version 1.11.5
InfluxDB shell 1.11.5
>

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

name: h2o_feet

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

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

> SELECT * FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
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

> SELECT "level description","location","water_level" FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
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

> SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
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

> SELECT *::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      water_level
2015-08-18T00:00:00Z   below 3 feet           2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   5.066
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

> SELECT ("water_level" * 2) + 4 FROM "h2o_feet"

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   20.24
2015-08-18T00:00:00Z   8.128
[...]
2015-09-18T21:36:00Z   14.132
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

> SELECT * FROM "h2o_feet","h2o_pH"

name: h2o_feet
--------------
time                   level description      location       pH   water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938

name: h2o_pH
------------
time                   level description   location       pH   water_level
2015-08-18T00:00:00Z                       santa_monica   6
2015-08-18T00:00:00Z                       coyote_creek   7
[...]
2015-09-18T21:36:00Z                       santa_monica   8
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

> SELECT * FROM "NOAA_water_database"."autogen"."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
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

> SELECT * FROM "NOAA_water_database".."h2o_feet"

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica   2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
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:

> SELECT "location" FROM "h2o_feet"
>

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

> SELECT "water_level","location" FROM "h2o_feet"
name: h2o_feet
time                   water_level  location
----                   -----------  --------
2015-08-18T00:00:00Z   8.12         coyote_creek
2015-08-18T00:00:00Z   2.064        santa_monica
[...]
2015-09-18T21:36:00Z   5.066        santa_monica
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

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

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

Other supported features: Arithmetic Operations, Regular Expressions

Tags

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

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

> SELECT * FROM "h2o_feet" WHERE "water_level" > 8

name: h2o_feet
--------------
time                   level description      location       water_level
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
2015-08-18T00:06:00Z   between 6 and 9 feet   coyote_creek   8.005
[...]
2015-09-18T00:12:00Z   between 6 and 9 feet   coyote_creek   8.189
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

> SELECT * FROM "h2o_feet" WHERE "level description" = 'below 3 feet'

name: h2o_feet
--------------
time                   level description   location       water_level
2015-08-18T00:00:00Z   below 3 feet        santa_monica   2.064
2015-08-18T00:06:00Z   below 3 feet        santa_monica   2.116
[...]
2015-09-18T14:06:00Z   below 3 feet        santa_monica   2.999
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

> SELECT * FROM "h2o_feet" WHERE "water_level" + 2 > 11.10

name: h2o_feet
--------------
time                   level description           location       water_level
2015-08-29T07:06:00Z   at or greater than 9 feet   coyote_creek   9.902
2015-08-29T07:12:00Z   at or greater than 9 feet   coyote_creek   9.938
2015-08-29T07:18:00Z   at or greater than 9 feet   coyote_creek   9.957
2015-08-29T07:24:00Z   at or greater than 9 feet   coyote_creek   9.964
2015-08-29T07:30:00Z   at or greater than 9 feet   coyote_creek   9.954
2015-08-29T07:36:00Z   at or greater than 9 feet   coyote_creek   9.941
2015-08-29T07:42:00Z   at or greater than 9 feet   coyote_creek   9.925
2015-08-29T07:48:00Z   at or greater than 9 feet   coyote_creek   9.902
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.10. 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

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
[...]
2015-09-18T21:36:00Z   5.066
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

> SELECT "water_level" FROM "h2o_feet" WHERE "location" <> 'santa_monica' AND (water_level < -0.59 OR water_level > 9.95)

name: h2o_feet
--------------
time                   water_level
2015-08-29T07:18:00Z   9.957
2015-08-29T07:24:00Z   9.964
2015-08-29T07:30:00Z   9.954
2015-08-29T14:30:00Z   -0.61
2015-08-29T14:36:00Z   -0.591
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

> 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.

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = santa_monica

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = "santa_monica"

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   2.064
[...]
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.

> SELECT "level description" FROM "h2o_feet" WHERE "level description" = at or greater than 9 feet

ERR: error parsing query: found than, expected ; at line 1, char 86

> SELECT "level description" FROM "h2o_feet" WHERE "level description" = "at or greater than 9 feet"

> SELECT "level description" FROM "h2o_feet" WHERE "level description" = 'at or greater than 9 feet'

name: h2o_feet
--------------
time                   level description
2015-08-26T04:00:00Z   at or greater than 9 feet
[...]
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 <tag> groups query results by one or more specified tags.

Watch InfluxQL short about GROUP BY with tags:

Syntax

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

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

name: h2o_feet
tags: location=coyote_creek
time			               mean
----			               ----
1970-01-01T00:00:00Z	 5.359342451341401


name: h2o_feet
tags: location=santa_monica
time			               mean
----			               ----
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

> SELECT MEAN("index") FROM "h2o_quality" GROUP BY "location","randtag"

name: h2o_quality
tags: location=coyote_creek, randtag=1
time                  mean
----                  ----
1970-01-01T00:00:00Z  50.69033760186263

name: h2o_quality
tags: location=coyote_creek, randtag=2
time                   mean
----                   ----
1970-01-01T00:00:00Z   49.661867544220485

name: h2o_quality
tags: location=coyote_creek, randtag=3
time                   mean
----                   ----
1970-01-01T00:00:00Z   49.360939907550076

name: h2o_quality
tags: location=santa_monica, randtag=1
time                   mean
----                   ----
1970-01-01T00:00:00Z   49.132712456344585

name: h2o_quality
tags: location=santa_monica, randtag=2
time                   mean
----                   ----
1970-01-01T00:00:00Z   50.2937984496124

name: h2o_quality
tags: location=santa_monica, randtag=3
time                   mean
----                   ----
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

> SELECT MEAN("index") FROM "h2o_quality" GROUP BY *

name: h2o_quality
tags: location=coyote_creek, randtag=1
time			               mean
----			               ----
1970-01-01T00:00:00Z	 50.55405446521169


name: h2o_quality
tags: location=coyote_creek, randtag=2
time			               mean
----			               ----
1970-01-01T00:00:00Z	 50.49958856271162


name: h2o_quality
tags: location=coyote_creek, randtag=3
time			               mean
----			               ----
1970-01-01T00:00:00Z	 49.5164137518956


name: h2o_quality
tags: location=santa_monica, randtag=1
time			               mean
----			               ----
1970-01-01T00:00:00Z	 50.43829082296367


name: h2o_quality
tags: location=santa_monica, randtag=2
time			               mean
----			               ----
1970-01-01T00:00:00Z	 52.0688508894012


name: h2o_quality
tags: location=santa_monica, randtag=3
time			               mean
----			               ----
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

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:

> SELECT "water_level","location" FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z'

name: h2o_feet
--------------
time                   water_level   location
2015-08-18T00:00:00Z   8.12          coyote_creek
2015-08-18T00:00:00Z   2.064         santa_monica
2015-08-18T00:06:00Z   8.005         coyote_creek
2015-08-18T00:06:00Z   2.116         santa_monica
2015-08-18T00:12:00Z   7.887         coyote_creek
2015-08-18T00:12:00Z   2.028         santa_monica
2015-08-18T00:18:00Z   7.762         coyote_creek
2015-08-18T00:18:00Z   2.126         santa_monica
2015-08-18T00:24:00Z   7.635         coyote_creek
2015-08-18T00:24:00Z   2.041         santa_monica
2015-08-18T00:30:00Z   7.5           coyote_creek
2015-08-18T00:30:00Z   2.051         santa_monica

Group query results into 12 minute intervals

> 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)

name: h2o_feet
--------------
time                   count
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
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

> 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"

name: h2o_feet
tags: location=coyote_creek
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
2015-08-18T00:24:00Z   2

name: h2o_feet
tags: location=santa_monica
time                   count
----                   -----
2015-08-18T00:00:00Z   2
2015-08-18T00:12:00Z   2
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:

> SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:18:00Z'
name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   8.12
2015-08-18T00:06:00Z   8.005
2015-08-18T00:12:00Z   7.887
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:

> 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)

name: h2o_feet
time                   count
----                   -----
2015-08-18T00:00:00Z   1        <----- Note that this timestamp occurs before the start of the query's time range
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.

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:

name: h2o_feet
time                   count
----                   -----
2015-08-18T00:06:00Z   2

Advanced GROUP BY time() syntax

Syntax

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:

> SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:54:00Z'

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z   8.12
2015-08-18T00:06:00Z   8.005
2015-08-18T00:12:00Z   7.887
2015-08-18T00:18:00Z   7.762
2015-08-18T00:24:00Z   7.635
2015-08-18T00:30:00Z   7.5
2015-08-18T00:36:00Z   7.372
2015-08-18T00:42:00Z   7.234
2015-08-18T00:48:00Z   7.11
2015-08-18T00:54:00Z   6.982

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

> 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)

name: h2o_feet
time                   mean
----                   ----
2015-08-18T00:06:00Z   7.884666666666667
2015-08-18T00:24:00Z   7.502333333333333
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:

> 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)

name: h2o_feet
time                   mean
----                   ----
2015-08-18T00:00:00Z   7.946
2015-08-18T00:18:00Z   7.6323333333333325
2015-08-18T00:36:00Z   7.238666666666667
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:

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:

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

> 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)

name: h2o_feet
time                   mean
----                   ----
2015-08-18T00:06:00Z   7.884666666666667
2015-08-18T00:24:00Z   7.502333333333333
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:

> 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)

name: h2o_feet
time                    mean
----                    ----
2015-08-18T00:00:00Z    7.946
2015-08-18T00:18:00Z    7.6323333333333325
2015-08-18T00:36:00Z    7.238666666666667
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:

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:

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.

> 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)

name: h2o_feet
time                   count
----                   -----
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:

> 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)

name: h2o_feet
time                   count
----                   -----
2015-08-18T00:00:00Z   1
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:

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:

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

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

> 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)

name: h2o_feet
--------------
time                   max
2015-09-18T16:00:00Z   3.599
2015-09-18T16:12:00Z   3.402
2015-09-18T16:24:00Z   3.235
2015-09-18T16:36:00Z

> 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)

name: h2o_feet
--------------
time                   max
2015-09-18T16:00:00Z   3.599
2015-09-18T16:12:00Z   3.402
2015-09-18T16:24:00Z   3.235
2015-09-18T16:36:00Z   100

> SELECT MEAN("tadpoles") FROM "pond" WHERE time >= '2016-11-11T21:00:00Z' AND time <= '2016-11-11T22:06:00Z' GROUP BY time(12m)

name: pond
time                   mean
----                   ----
2016-11-11T21:00:00Z   1
2016-11-11T21:12:00Z
2016-11-11T21:24:00Z   3
2016-11-11T21:36:00Z
2016-11-11T21:48:00Z
2016-11-11T22:00:00Z   6

> 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)

name: pond
time                   mean
----                   ----
2016-11-11T21:00:00Z   1
2016-11-11T21:12:00Z   2
2016-11-11T21:24:00Z   3
2016-11-11T21:36:00Z   4
2016-11-11T21:48:00Z   5
2016-11-11T22:00:00Z   6

> 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)

name: h2o_feet
--------------
time                   max
2015-09-18T16:00:00Z   3.599
2015-09-18T16:12:00Z   3.402
2015-09-18T16:24:00Z   3.235
2015-09-18T16:36:00Z

> 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)

name: h2o_feet
--------------
time                   max
2015-09-18T16:00:00Z   3.599
2015-09-18T16:12:00Z   3.402
2015-09-18T16:24:00Z   3.235