InfluxQL is an SQL-like query language for interacting with data in InfluxDB. The following sections cover useful query syntax for exploring your data.
WHERE clause in InfluxQL:
- The
WHERE
clause supports comparisons against strings, booleans, floats, integers, and against thetime
of the timestamp. It supports using regular expressions to match tags, but not to match fields. - Chain logic together using
AND
andOR
, and separate using(
and)
. - Acceptable comparators include:
1 | = equal to |
The basics:
- The
SELECT
statement and theWHERE
clause- The basic
SELECT
statement - The
SELECT
statement and arithmetic - The
WHERE
clause
- The basic
- The GROUP BY clause
- The basic
GROUP BY
clause - The
GROUP BY
clause andfill()
- The basic
- The INTO clause
- Relocate data
- Downsample data
Limit and sort your results:
- Limit query returns with
LIMIT
andSLIMIT
- Limit results per series with LIMIT
- Limit the number of series returned with SLIMIT
- Sort query returns with ORDER BY time DESC
- Paginate query returns with OFFSET
General tips on query syntax:
- Multiple statements in queries
- Merge series in queries
- Time syntax in queries
- Relative time
- Absolute time
- Regular expressions in queries
- Regular expressions and selecting measurements
- Regular expressions and specifying tags
The examples below query data using InfluxDB’s Command Line Interface (CLI). See the Querying Data guide for how to query data directly using the HTTP API.
Sample data
1 | If you’d like to follow along with the queries in this document, see Sample Data for how to download and write the data to InfluxDB. This document uses publicly available data from the National Oceanic and Atmospheric Administration’s (NOAA) Center for Operational Oceanographic Products and Services. The data include water levels (ft) collected every six seconds at two stations (Santa Monica, CA (ID 9410840) and Coyote Creek, CA (ID 9414575)) over the period from August 18, 2015 through September 18, 2015. A subsample of the data in the measurement h2o_feet: |
1 | name: h2o_feet |
The series
are made up of the measurement
h2o_feet
and the tag key
location
with the tag values
santa_monica
and coyote_creek
. There are two fields: water_level
which stores floats and level description
which stores strings. All of the data are 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 SELECT statement and the WHERE clause
InfluxQL’s SELECT
statement follows the form of an SQL SELECT
statement where the WHERE
clause is optional:
1 | SELECT <stuff> FROM <measurement_name> WHERE <some_conditions> |
The basic SELECT statement
The following three examples return everything from the measurement h2o_feet (see the CLI response at the end of this section). While they all return the same result, they get to that result in slightly different ways and serve to introduce some of the specifics of the SELECT syntax:
Select everything from h2o_feet with *:
1 | SELECT * FROM h2o_feet |
Select everything from h2o_feet by specifying each tag key and field key:
1 | SELECT "level description",location,water_level FROM h2o_feet |
- Separate multiple fields and tags of interest with a comma. Note that you must specify at least one field in the SELECT statement.
- Leave identifiers unquoted unless they start with a digit, contain characters other than [A-z,0-9,_], or if they are an InfluxQL keyword then you need to double quote them. Identifiers are database names, retention policy names, user names
- measurement names, tag keys, and field keys.
Select everything from h2o_feet by fully qualifying the measurement:
1 | SELECT * FROM NOAA_water_database."default".h2o_feet |
Fully qualify a measurement if you wish to query data from a different database or from a retention policy other than the default retention policy. A fully qualified measurement takes the following form:
1 | "<database>"."<retention policy>"."<measurement>" |
The CLI response for all three queries:
1 | name: h2o_feet |
The SELECT statement and arithmetic
Perform basic arithmetic operations on fields that store floats and integers.
Add two to the field water_level:
1 | SELECT water_level + 2 FROM h2o_feet |
CLI response:
1 | name: h2o_feet |
Another example that works:
1 | SELECT (water_level * 2) + 4 from h2o_feet |
CLI response:
1 | name: h2o_feet |
Note: When performing arithmetic on fields that store integers be aware that InfluxDB casts those integers to floats for all mathematical operations. This can lead to overflow issues for some numbers.
The WHERE clause
Use a WHERE clause to filter your data based on tags, time ranges, and/or field values.
Tags
Return data where the tag key location has the tag value santa_monica:
1 | SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' |
Always single quote tag values in queries - they are strings. Note that double quotes do not work when specifying tag values and can cause queries to silently fail.
Note: Tags are indexed so queries on tag keys or tag values are more performant than queries on fields.
Return data where the tag key location has no tag value (more on regular expressions later):
1 | SELECT * FROM h2o_feet WHERE location !~ /.*/ |
Return data where the tag key location has a value:
1 | SELECT * FROM h2o_feet WHERE location =~ /.*/ |
Time ranges
Return data from the past seven days:
1 | SELECT * FROM h2o_feet WHERE time > now() - 7d |
now() is the Unix time of the server at the time the query is executed on that server. For more on now() and other ways to specify time in queries, see time syntax in queries.
Field values
Return data where the tag key location has the tag value coyote_creek and the field water_level is greater than 8 feet:
1 | SELECT * FROM h2o_feet WHERE location = 'coyote_creek' AND water_level > 8 |
Return data where the tag key location has the tag value santa_monica and the field level description equals ‘below 3 feet’:
1 | SELECT * FROM h2o_feet WHERE location = 'santa_monica' AND "level description" = 'below 3 feet' |
Always single quote field values that are strings. Note that double quotes do not work when specifying string field values and can cause queries to silently fail.
Note: Fields are not indexed; queries on fields are not as performant as those on tags.
The GROUP BY clause
Use the GROUP BY
clause to group data by tags and/or time intervals. To successfully implement GROUP BY
, append the GROUP BY
clause to a SELECT
statement and pair the SELECT
statement with one of InfluxQL’s functions.
Note: If your query includes both a WHERE clause and a GROUP BY clause, the GROUP BY clause must come after the WHERE clause.
The basic GROUP BY clause
GROUP BY tag values
Calculate the MEAN() water_level for the different tag values of location:
1 | SELECT MEAN(water_level) FROM h2o_feet GROUP BY location |
CLI response:
1 | name: h2o_feet |
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.
Calculate the MEAN() index for every tag set in h2o_quality:
SELECT MEAN(index) FROM h2o_quality GROUP BY *
CLI response:
1 | name: h2o_quality |
GROUP BY time intervals
GROUP BY time intervals
COUNT() the number of water_level points between August 18, 2015 at midnight and September 18 at 5:00pm at two day intervals:
1 | SELECT COUNT(water_level) FROM h2o_feet WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-09-18T17:00:00Z' AND location='coyote_creek' GROUP BY time(2d) |
CLI response:
1 | name: h2o_feet |
Notice that each timestamp represents a two day interval and that the value in the count field is the number of water_level points that occurred in that two day interval. You could get the same results by querying the data 17 times - that is, one COUNT() query for every two days between August 18, 2015 at midnight and September 18 at 5:00pm - but that could take a while.
Note: The first timestamp in the CLI response (2015-08-17T00:00:00Z) occurs before the lower bound of the query’s time range (2015-08-18T00:00:00Z). See Frequently Encountered Issues for an explanation of the time intervals returned from GROUP BY time() queries.
Other things to note about GROUP BY time():
InfluxQL requires a WHERE clause if you’re using GROUP BY with time(). Note that unless you specify a different upper and lower bound for the time range, GROUP BY uses epoch 0 as the lower bound and now() as the upper bound for the query - this can lead to unexpected results.
Valid units for time() are:
1 | `u` microseconds |
GROUP BY tag values AND a time interval
Calculate the average water_level for the different tag values of location in the last two weeks at 6 hour intervals:
1 | SELECT MEAN(water_level) FROM h2o_feet WHERE time > now() - 2w GROUP BY location,time(6h) |
- Separate multiple GROUP BY arguments with a comma.
The GROUP BY clause and fill()
By default, a GROUP BY interval with no data has null as its value in the output column. Use fill() to change the value reported for intervals that have no data. fill() options include:
- Any numerical value
- null - sets null as the value for intervals with no data
- previous - copies the value from the previous interval for intervals with no data
- none - skips intervals with no data to report
Follow the ✨ in the examples below to see what fill() can do.
GROUP BY without fill()
1 | SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18' AND time < '2015-09-24' GROUP BY time(10d) |
CLI response:
1 | name: h2o_feet |
GROUP BY with fill()
Use fill() with -100:
1 | SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18' AND time < '2015-09-24' GROUP BY time(10d) fill(-100) |
CLI response:
1 | name: h2o_feet |
Use fill() with none:
1 | SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18' AND time < '2015-09-24' GROUP BY time(10d) fill(none) |
CLI response:
1 | name: h2o_feet |
Note: If you’re GROUP(ing) BY several things (for example, both tags and a time interval) fill() must go at the end of the GROUP BY clause.
The INTO clause
Relocate data
Copy data to another database, retention policy, and measurement with the INTO clause:
1 | SELECT <field_key> INTO <different_measurement> FROM <current_measurement> [WHERE <stuff>] [GROUP BY <stuff>] |
Write the field water_level in h2o_feet to a new measurement (h2o_feet_copy) in the same database:
1 | SELECT water_level INTO h2o_feet_copy FROM h2o_feet WHERE location = 'coyote_creek' |
The CLI response shows the number of points that InfluxDB wrote to h2o_feet_copy:
1 | name: result |
Write the field water_level in h2o_feet to a new measurement (h2o_feet_copy) and to the retention policy default in the database where_else:
1 | SELECT water_level INTO where_else."default".h2o_feet_copy FROM h2o_feet WHERE location = 'coyote_creek' |
CLI response:
1 | name: result |
Note: If you use SELECT * with INTO, the query converts tags in the current measurement to fields in the new measurement. This can cause InfluxDB to overwrite points that were previously differentiated by a tag value. Use GROUP BY
to preserve tags as tags.
Downsample data
Combine the INTO clause with an InfluxQL function and a GROUP BY clause to write the lower precision query results to a different measurement:
1 | SELECT <function>(<field_key>) INTO <different_measurement> FROM <current_measurement> WHERE <stuff> GROUP BY <stuff> |
Note: The INTO queries in this section downsample old data, that is, data that have already been written to InfluxDB. If you want InfluxDB to automatically query and downsample all future data see Continuous Queries.
Calculate the average water_level in santa_monica, and write the results to a new measurement (average) in the same database:
1 | SELECT mean(water_level) INTO average FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) |
The CLI response shows the number of points that InfluxDB wrote to the new measurement:
1 | name: result |
To see the query results, select everything from the new measurement average in NOAA_water_database:
1 | SELECT * FROM average |
Calculate the average water_level and the max water_level in santa_monica, and write the results to a new measurement (aggregates) in a different database (where_else):
1 | SELECT mean(water_level), max(water_level) INTO where_else."default".aggregates FROM h2o_feet WHERE location = 'santa_monica' AND time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) |
CLI response:
1 | name: result |
Select everything from the new measurement aggregates in the database where_else:
1 | SELECT * FROM where_else."default".aggregates |
Calculate the average degrees for all temperature measurements (h2o_temperature and average_temperature) in the NOAA_water_database and write the results to new measurements with the same names in a different database (where_else). :MEASUREMENT tells InfluxDB to write the query results to measurements with the same names as those targeted by the query:
1 | SELECT mean(degrees) INTO where_else."default".:MEASUREMENT FROM /temperature/ WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:30:00Z' GROUP BY time(12m) |
CLI response:
1 | name: result |
Select the mean field from all new temperature measurements in the database where_else.
1 | SELECT mean FROM where_else."default"./temperature/ |
More on downsampling with INTO:
- InfluxDB does not store null values. Depending on the frequency of your data, the query results may be missing time intervals. Use fill() to ensure that every time interval appears in the results.
- The number of writes in the CLI response includes one write for every time interval in the query’s time range even if there is no data for some of the time intervals.
Limit query returns with LIMIT and SLIMIT
SLIMIT InfluxQL supports two different clauses to limit your query results. Currently, they are mutually exclusive so you may use one or the other, but not both in the same query.
Please note that using LIMIT and SLIMIT without a GROUP BY * clause can cause unexpected results. See GitHub Issue #4232 for more information.
Limit the number of results returned per series with LIMIT
Use LIMIT
Return the three oldest points from each series associated with the measurement h2o_feet:
1 | SELECT water_level FROM h2o_feet GROUP BY * LIMIT 3 |
CLI response:
1 | name: h2o_feet |
Note: If N is greater than the number of points in the series, InfluxDB returns all points in the series.
Limit the number of series returned with SLIMIT
Use SLIMIT
Return everything from one of the series associated with the measurement h2o_feet:
1 | SELECT water_level FROM h2o_feet GROUP BY * SLIMIT 1 |
CLI response:
1 | name: h2o_feet |
Note: If N is greater than the number of series associated with the specified measurement, InfluxDB returns all points from every series.
Sort query returns with ORDER BY time DESC
By default, InfluxDB returns results in ascending time order - so the first points that are returned are the oldest points by timestamp. Use ORDER BY time DESC to see the newest points by timestamp.
Return the oldest five points from one series without ORDER BY time DESC:
1 | SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' LIMIT 5 |
CLI response:
1 | name: h2o_feet |
Now include ORDER BY time DESC to get the newest five points from the same series:
1 | SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time DESC LIMIT 5 |
CLI response:
1 | name: h2o_feet |
Finally, use GROUP BY
with ORDER BY time DESC
to return the last five points from each series:
1 | SELECT water_level FROM h2o_feet GROUP BY location ORDER BY time DESC LIMIT 5 |
CLI response:
1 | name: h2o_feet |
aginate query returns with OFFSET
Use OFFSET to paginate the results returned. For example, get the first three points written to a series:
1 | SELECT water_level FROM h2o_feet WHERE location = 'coyote_creek' LIMIT 3 |
CLI response:
1 | name: h2o_feet |
Then get the second three points from that same series:
1 | SELECT water_level FROM h2o_feet WHERE location = 'coyote_creek' LIMIT 3 OFFSET 3 |
CLI response:
1 | name: h2o_feet |
Multiple statements in queries
Separate multiple statements in a query with a semicolon. For example:
1 | SELECT mean(water_level) FROM h2o_feet WHERE time > now() - 2w GROUP BY location,time(24h) fill(none); SELECT count(water_level) FROM h2o_feet WHERE time > now() - 2w GROUP BY location,time(24h) fill(80) |
Merge series in queries
In InfluxDB, queries merge series automatically.
The NOAA_water_database
database has two series
. The first series is made up of the measurement h2o_feet
and the tag key location
with the tag value coyote_creek
. The second series is made of up the measurement h2o_feet
and the tag key location
with the tag value santa_monica
.
The following query automatically merges those two series when it calculates the MEAN() water_level:
1 | SELECT MEAN(water_level) FROM h2o_feet |
CLI response:
1 | name: h2o_feet |
If you only want the MEAN() water_level for the first series, specify the tag set in the WHERE clause:
1 | SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek' |
CLI response:
1 | name: h2o_feet |
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.
Time syntax in queries
InfluxDB is a time series database so, unsurprisingly, InfluxQL has a lot to do with specifying time ranges. If you do not specify start and end times in your query, they default to epoch 0 (1970-01-01T00:00:00Z) and now(). The following sections detail how to specify different start and end times in queries.
Relative time
now()
is the Unix time of the server at the time the query is executed on that server. Use now() to calculate a timestamp relative to the server’s current timestamp.
Query data starting an hour ago and ending now():
1 | SELECT water_level FROM h2o_feet WHERE time > now() - 1h |
Query data that occur between epoch 0 and 1,000 days from now():
1 | SELECT "level description" FROM h2o_feet WHERE time < now() + 1000d |
Note the whitespace between the operator and the time duration. Leaving that whitespace out can cause InfluxDB to return no results or an error parsing query error .
The other options for specifying time durations with now() are listed below.
1 | `u` microseconds |
Absolute time
Date time strings
Specify time with date time strings. Date time strings can take two formats: YYYY-MM-DD HH:MM:SS.nnnnnnnnn and YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ, where the second specification is RFC3339. Nanoseconds (nnnnnnnnn) are optional in both formats.
The following two queries query data between August 18, 2015 23:00:01.232000000 and September 19, 2015 00:00:00.
1 | SELECT water_level FROM h2o_feet WHERE time > '2015-08-18 23:00:01.232000000' AND time < '2015-09-19' |
1 | SELECT water_level FROM h2o_feet WHERE time > '2015-08-18T23:00:01.232000000Z' AND time < '2015-09-19' |
- Single quote the date time string. InfluxDB fails to restrict data by date time strings that are double quoted.
- If you only specify the date, InfluxDB sets the time to 00:00:00.
Epoch time
Specify time with timestamps in epoch time. Epoch time is the number of nanoseconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970. Indicate the units of the timestamp at the end of the timestamp (see the section above for a list of acceptable time units).
Return all points that occur after 2014-01-01 00:00:00:
1 | SELECT * FROM h2o_feet WHERE time > 1388534400s |
Regular expressions in queries
Regular expressions are surrounded by / characters and use Golang’s regular expression syntax. Use regular expressions when selecting measurements and tags.
Note: You cannot use regular expressions to match databases, retention policies, or fields. You can only use regular expressions to match measurements and tags.
The sample data
need to be more intricate for the following sections. Assume that the database NOAA_water_database
now holds several measurements: h2o_feet
, h2o_quality
, h2o_pH
, average_temperature
, and h2o_temperature
. Please note that every measurement besides h2o_feet is fictional and contains fictional data.
Regular expressions and selecting measurements
Select the oldest point from every measurement in the NOAA_water_database
database:
SELECT FROM /./ LIMIT 1
CLI response:
1 | name: average_temperature |
Alternatively, SELECT
all of the measurements in NOAA_water_database
by typing them out and separating each name with a comma (see below), but that could get tedious: sql > SELECT * FROM average_temperature,h2o_feet,h2o_pH,h2o_quality,h2o_temperature LIMIT 1
Select the first three points from every measurement whose name starts with h2o:
1 | SELECT * FROM /^h2o/ LIMIT 3 |
CLI response:
1 | name: h2o_feet |
Select the last 5 points from every measurement whose name contains temperature:
1 | SELECT * FROM /.*temperature.*/ LIMIT 5 |
CLI response:
1 | name: average_temperature |
Regular expressions and specifying tags
Use regular expressions to specify tags in the WHERE clause. The relevant comparators include:
=~ matches against
!~ doesn’t match against
Select the oldest four points from the measurement h2o_feet
where the value of the tag location
does not include an a:
1 | SELECT * FROM h2o_feet WHERE location !~ /.*a.*/ LIMIT 4 |
CLI response:
1 | name: h2o_feet |
Select the oldest four points from the measurement h2o_feet
where the value of the tag location
includes a y
or an m
and water_level
is greater than zero:
1 | SELECT * FROM h2o_feet WHERE (location =~ /.*y.*/ OR location =~ /.*m.*/) AND water_level > 0 LIMIT 4 |
or1
SELECT * FROM h2o_feet WHERE location =~ /[ym]/ AND water_level > 0 LIMIT 4
CLI response:
1 | name: h2o_feet |
referrerr: https://docs.influxdata.com/influxdb/v0.9/query_language/data_exploration