This is archived documentation for InfluxData product versions that are no longer maintained. For newer documentation, see the latest InfluxData documentation.
InfluxQL is an SQL-like query language for interacting with data in InfluxDB. The following sections cover useful query syntax for exploring your data.
The basics:
- The
SELECT
statement and theWHERE
clause
◦ The basicSELECT
statement
◦ TheSELECT
statement and arithmetic
◦ TheWHERE
clause - The
GROUP BY
clause
◦ The basicGROUP BY
clause
◦ TheGROUP BY
clause andfill()
- The
INTO
clause
◦ Relocate data
◦ Downsample data
Limit and sort your results:
- Limit query returns with
LIMIT
andSLIMIT
◦ Limit results per series withLIMIT
◦ Limit the number of series returned withSLIMIT
◦ Limit the number of points and series returned withLIMIT
andSLIMIT
- 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
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
:
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:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
2015-08-18T00:06:00Z below 3 feet santa_monica 2.116
2015-08-18T00:12:00Z between 6 and 9 feet coyote_creek 7.887
2015-08-18T00:12:00Z below 3 feet santa_monica 2.028
2015-08-18T00:18:00Z between 6 and 9 feet coyote_creek 7.762
2015-08-18T00:18:00Z below 3 feet santa_monica 2.126
2015-08-18T00:24:00Z between 6 and 9 feet coyote_creek 7.635
2015-08-18T00:24:00Z below 3 feet santa_monica 2.041
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 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 SELECT statement and the WHERE
clause
InfluxQL’s SELECT
statement follows the form of an SQL SELECT
statement where the WHERE
clause is optional:
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 *
:
> SELECT * FROM h2o_feet
Select everything from h2o_feet
by specifying each tag key and field key:
> 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:
> 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:
"<database>"."<retention policy>"."<measurement>"
The CLI response for all three queries:
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:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
2015-08-18T00:06:00Z below 3 feet santa_monica 2.116
[...]
2015-09-18T21:24:00Z between 3 and 6 feet santa_monica 5.013
2015-09-18T21:30:00Z between 3 and 6 feet santa_monica 5.01
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 SELECT
statement and arithmetic
Perform basic arithmetic operations on fields that store floats and integers.
Add two to the field water_level
:
> SELECT water_level + 2 FROM h2o_feet
CLI response:
name: h2o_feet
--------------
time
2015-08-18T00:00:00Z 10.12
2015-08-18T00:00:00Z 4.064
[...]
2015-09-18T21:36:00Z 7.066
2015-09-18T21:42:00Z 6.938
Another example that works:
> SELECT (water_level * 2) + 4 from h2o_feet
CLI response:
name: h2o_feet
--------------
time
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
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.
Note: The quoting syntax for queries differs from the line protocol. Please review the rules for single and double-quoting in queries.
Tags
Return data where the tag key location
has the tag value santa_monica
:
> 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):
> SELECT * FROM h2o_feet WHERE location !~ /.*/
Return data where the tag key location
has a value:
> SELECT * FROM h2o_feet WHERE location =~ /.*/
Time ranges
Return data from the past seven days:
> 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 onnow()
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:
> 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'
:
> 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.
More on the 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:
=
equal to<>
not equal to!=
not equal to>
greater than<
less than=~
matches against!~
doesn’t match against
The GROUP BY clause
Use the GROUP BY
clause to group data by tags and/or time intervals.
To successfully implement GROUP BY
, append theGROUP 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 aGROUP BY
clause, theGROUP BY
clause must come after theWHERE
clause.
The basic GROUP BY
clause
GROUP BY tag values
Calculate the MEAN()
water_level
for the different tag values of location
:
> SELECT MEAN(water_level) FROM h2o_feet GROUP BY location
CLI response:
> 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
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:
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
GROUP BY time intervalsCOUNT()
the number of water_level
points between August 18, 2015 at midnight and September 18 at 5:00pm at two day intervals:
> 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:
name: h2o_feet
----------
time count
2015-08-17T00:00:00Z 240
2015-08-19T00:00:00Z 480
2015-08-21T00:00:00Z 480
2015-08-23T00:00:00Z 480
2015-08-25T00:00:00Z 480
2015-08-27T00:00:00Z 480
2015-08-29T00:00:00Z 480
2015-08-31T00:00:00Z 480
2015-09-02T00:00:00Z 480
2015-09-04T00:00:00Z 479
2015-09-06T00:00:00Z 480
2015-09-08T00:00:00Z 480
2015-09-10T00:00:00Z 480
2015-09-12T00:00:00Z 480
2015-09-14T00:00:00Z 480
2015-09-16T00:00:00Z 480
2015-09-18T00:00:00Z 165
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 fromGROUP BY time()
queries.
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:
> SELECT MEAN(water_level) FROM h2o_feet WHERE time > now() - 2w GROUP BY location,time(6h)
- Separate multiple
GROUP BY
arguments with a comma.
Other things to note about GROUP BY time()
:
- InfluxQL requires a
WHERE
clause if you’re usingGROUP BY
withtime()
. Note that unless you specify a different upper and lower bound for the time range,GROUP BY
usesepoch 0
as the lower bound andnow()
as the upper bound for the query. - Valid units for
time()
are:u
microsecondsms
millisecondss
secondsm
minutesh
hoursd
daysw
weeks
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
- setsnull
as the value for intervals with no dataprevious
- copies the value from the previous interval for intervals with no datanone
- skips intervals with no data to report
Follow the ✨ in the examples below to see what fill()
can do.
GROUP BY without fill()
> SELECT MEAN(water_level) FROM h2o_feet WHERE time >= '2015-08-18' AND time < '2015-09-24' GROUP BY time(10d)
CLI response:
name: h2o_feet
--------------
time mean
2015-08-13T00:00:00Z 4.306212083333323
2015-08-23T00:00:00Z 4.318944629367029
2015-09-02T00:00:00Z 4.363877681204781
2015-09-12T00:00:00Z 4.69811470811633
✨2015-09-22T00:00:00Z
GROUP BY with fill()
Use fill()
with -100
:
> 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:
name: h2o_feet
--------------
time mean
2015-08-13T00:00:00Z 4.306212083333323
2015-08-23T00:00:00Z 4.318944629367029
2015-09-02T00:00:00Z 4.363877681204781
2015-09-12T00:00:00Z 4.698114708116322
✨2015-09-22T00:00:00Z -100
Use fill()
with none
:
> 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:
name: h2o_feet
--------------
time mean
2015-08-13T00:00:00Z 4.306212083333323
2015-08-23T00:00:00Z 4.318944629367029
2015-09-02T00:00:00Z 4.363877681204781
2015-09-12T00:00:00Z 4.69811470811633
✨
Note: If you’re
GROUP(ing) BY
several things (for example, both tags and a time interval)fill()
must go at the end of theGROUP BY
clause.
The INTO clause
Relocate data
Copy data to another database, retention policy, and measurement with the INTO
clause:
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:
> 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
:
name: result
------------
time written
1970-01-01T00:00:00Z 7604
Write the field water_level
in h2o_feet
to a new measurement (h2o_feet_copy
) and to the retention policy default
in the already-existing database where_else
:
> SELECT water_level INTO where_else."default".h2o_feet_copy FROM h2o_feet WHERE location = 'coyote_creek'
CLI response:
name: result
------------
time written
1970-01-01T00:00:00Z 7604
Note: If you use
SELECT *
withINTO
, 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. UseGROUP BY <tag_key>
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:
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:
> 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:
name: result
------------
time written
1970-01-01T00:00:00Z 3
To see the query results, select everything from the new measurement average
in NOAA_water_database
:
> SELECT * FROM average
name: average
-------------
time mean
2015-08-18T00:00:00Z 2.09
2015-08-18T00:12:00Z 2.077
2015-08-18T00:24:00Z 2.0460000000000003
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
):
> 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:
name: result
------------
time written
1970-01-01T00:00:00Z 3
Select everything from the new measurement aggregates
in the database where_else
:
> SELECT * FROM where_else."default".aggregates
name: aggregates
----------------
time max mean
2015-08-18T00:00:00Z 2.116 2.09
2015-08-18T00:12:00Z 2.126 2.077
2015-08-18T00:24:00Z 2.051 2.0460000000000003
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:
> 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:
name: result
------------
time written
1970-01-01T00:00:00Z 6
Select the mean
field from all new temperature measurements in the database where_else
.
> SELECT mean FROM where_else."default"./temperature/
name: average_temperature
-------------------------
time mean
2015-08-18T00:00:00Z 78.5
2015-08-18T00:12:00Z 84
2015-08-18T00:24:00Z 74.75
name: h2o_temperature
---------------------
time mean
2015-08-18T00:00:00Z 63.75
2015-08-18T00:12:00Z 63.5
2015-08-18T00:24:00Z 63.5
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
InfluxQL supports two different clauses to limit your query results:
LIMIT <N>
returns the first <N> points from each series in the specified measurement.SLIMIT <N>
returns every point from <N> series in the specified measurement.LIMIT <N>
followed bySLIMIT <N>
returns the first <N> points from <N> series in the specified measurement.
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 <N>
with SELECT
and GROUP BY *
to return the first <N> points from each series.
Return the three oldest points from each series associated with the measurement h2o_feet
:
> SELECT water_level FROM h2o_feet GROUP BY * LIMIT 3
CLI response:
name: h2o_feet
tags: location=coyote_creek
time water_level
---- -----------
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
name: h2o_feet
tags: location=santa_monica
time water_level
---- -----------
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
2015-08-18T00:12:00Z 2.028
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 <N>
with SELECT
and GROUP BY *
to return every point from <N> series.
Return everything from one of the series associated with the measurement h2o_feet
:
> SELECT water_level FROM h2o_feet GROUP BY * SLIMIT 1
CLI response:
name: h2o_feet
tags: location=coyote_creek
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-09-18T16:12:00Z 3.402
2015-09-18T16:18:00Z 3.314
2015-09-18T16:24:00Z 3.235
Note: If <N> is greater than the number of series associated with the specified measurement, InfluxDB returns all points from every series.
Limit the number of points and series returned with LIMIT
and SLIMIT
Use LIMIT <N1>
followed by SLIMIT <N2>
with GROUP BY *
to return <N1> points from <N2> series.
Return the three oldest points from one of the series associated with the measurement h2o_feet
:
> SELECT water_level FROM h2o_feet GROUP BY * LIMIT 3 SLIMIT 1
CLI response:
name: h2o_feet
tags: location=coyote_creek
time water_level
---- -----------
2015-08-18T00:00:00Z 8.12
2015-08-18T00:06:00Z 8.005
2015-08-18T00:12:00Z 7.887
Note: If <N1> is greater than the number of points in the series, InfluxDB returns all points in the series. If <N2> is greater than the number of series associated with the specified measurement, InfluxDB returns 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
:
> SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' LIMIT 5
CLI response:
name: h2o_feet
----------
time water_level
2015-08-18T00:00:00Z 2.064
2015-08-18T00:06:00Z 2.116
2015-08-18T00:12:00Z 2.028
2015-08-18T00:18:00Z 2.126
2015-08-18T00:24:00Z 2.041
Now include ORDER BY time DESC
to get the newest five points from the same series:
> SELECT water_level FROM h2o_feet WHERE location = 'santa_monica' ORDER BY time DESC LIMIT 5
CLI response:
name: h2o_feet
----------
time water_level
2015-09-18T21:42:00Z 4.938
2015-09-18T21:36:00Z 5.066
2015-09-18T21:30:00Z 5.01
2015-09-18T21:24:00Z 5.013
2015-09-18T21:18:00Z 5.072
Finally, use GROUP BY
with ORDER BY time DESC
to return the last five points from each series:
> SELECT water_level FROM h2o_feet GROUP BY location ORDER BY time DESC LIMIT 5
CLI response:
name: h2o_feet
tags: location=santa_monica
time water_level
---- -----------
2015-09-18T21:42:00Z 4.938
2015-09-18T21:36:00Z 5.066
2015-09-18T21:30:00Z 5.01
2015-09-18T21:24:00Z 5.013
2015-09-18T21:18:00Z 5.072
name: h2o_feet
tags: location=coyote_creek
time water_level
---- -----------
2015-09-18T16:24:00Z 3.235
2015-09-18T16:18:00Z 3.314
2015-09-18T16:12:00Z 3.402
2015-09-18T16:06:00Z 3.497
2015-09-18T16:00:00Z 3.599
Paginate query returns with OFFSET
Use OFFSET
to paginate the results returned.
For example, get the first three points written to a series:
> SELECT water_level FROM h2o_feet WHERE location = 'coyote_creek' LIMIT 3
CLI response:
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
Then get the second three points from that same series:
> SELECT water_level FROM h2o_feet WHERE location = 'coyote_creek' LIMIT 3 OFFSET 3
CLI response:
name: h2o_feet
----------
time water_level
2015-08-18T00:18:00Z 7.762
2015-08-18T00:24:00Z 7.635
2015-08-18T00:30:00Z 7.5
Multiple statements in queries
Separate multiple statements in a query with a semicolon.
For example:
> 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 average water_level
:
> SELECT MEAN(water_level) FROM h2o_feet
CLI response:
name: h2o_feet
--------------
time mean
1970-01-01T00:00:00Z 4.319097913525821
If you only want the MEAN()
water_level
for the first series, specify the tag set in the WHERE
clause:
> SELECT MEAN(water_level) FROM h2o_feet WHERE location = 'coyote_creek'
CLI response:
name: h2o_feet
--------------
time mean
1970-01-01T00:00:00Z 5.296914449406493
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()
:
> SELECT water_level FROM h2o_feet WHERE time > now() - 1h
Query data that occur between epoch 0 and 1,000 days from now()
:
> 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.u
microsecondsms
millisecondss
secondsm
minutesh
hoursd
daysw
weeks
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.
> SELECT water_level FROM h2o_feet WHERE time > '2015-08-18 23:00:01.232000000' AND time < '2015-09-19'
> 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 returns as error (
ERR: invalid operation: time and *influxql.VarRef are not compatible
) if you double quote the date time string. - 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
:
> 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.
In this section we’ll be using all of the measurements in the sample data:
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:
name: average_temperature
-------------------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z 82 coyote_creek
name: h2o_feet
--------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
name: h2o_pH
------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z coyote_creek 7
name: h2o_quality
-----------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z 41 coyote_creek 1
name: h2o_temperature
---------------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z 60 coyote_creek
Alternatively,
SELECT
all of the measurements inNOAA_water_database
by typing them out and separating each name with a comma , but that could get tedious:> 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
:
> SELECT * FROM /^h2o/ LIMIT 3
CLI response:
name: h2o_feet
--------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
name: h2o_pH
------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z coyote_creek 7
2015-08-18T00:00:00Z santa_monica 6
2015-08-18T00:06:00Z coyote_creek 8
name: h2o_quality
-----------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z 99 santa_monica 2
2015-08-18T00:00:00Z 41 coyote_creek 1
2015-08-18T00:06:00Z 11 coyote_creek 3
name: h2o_temperature
---------------------
time degrees index level description location pH randtag water_level
2015-08-18T00:00:00Z 70 santa_monica
2015-08-18T00:00:00Z 60 coyote_creek
2015-08-18T00:06:00Z 60 santa_monica
Select the first 5 points from every measurement whose name contains temperature
:
> SELECT * FROM /.*temperature.*/ LIMIT 5
CLI response:
name: average_temperature
-------------------------
time degrees location
2015-08-18T00:00:00Z 85 santa_monica
2015-08-18T00:00:00Z 82 coyote_creek
2015-08-18T00:06:00Z 73 coyote_creek
2015-08-18T00:06:00Z 74 santa_monica
2015-08-18T00:12:00Z 86 coyote_creek
name: h2o_temperature
---------------------
time degrees location
2015-08-18T00:00:00Z 60 coyote_creek
2015-08-18T00:00:00Z 70 santa_monica
2015-08-18T00:06:00Z 65 coyote_creek
2015-08-18T00:06:00Z 60 santa_monica
2015-08-18T00:12:00Z 68 coyote_creek
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
:
> SELECT * FROM h2o_feet WHERE location !~ /.*a.*/ LIMIT 4
CLI response:
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-08-18T00:12:00Z between 6 and 9 feet coyote_creek 7.887
2015-08-18T00:18:00Z between 6 and 9 feet coyote_creek 7.762
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:
> SELECT * FROM h2o_feet WHERE (location =~ /.*y.*/ OR location =~ /.*m.*/) AND water_level > 0 LIMIT 4
or
> SELECT * FROM h2o_feet WHERE location =~ /[ym]/ AND water_level > 0 LIMIT 4
CLI response:
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:00:00Z below 3 feet santa_monica 2.064
2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
2015-08-18T00:06:00Z below 3 feet santa_monica 2.116
See the WHERE clause section for an example of how to return data where a tag key has a value and an example of how to return data where a tag key has no value using regular expressions.