HowTo InfluxDB Query Syntax

Howto: InfluxDB Query Syntax

Sources

I really recommend reading this page, it sums up perfectly what is the difference between SQL and Influx. There are also some examples on how to query.

Terminology

Tables are called measurements. Also any stored values are split into tags and fields, where tags are just enhanced version of fields. They are always indexed and stored as strings.

The time Tag is different and very unique. It is the only tag on which you can group, order or DELETE.

Fields may be strings, bools, ints or floats.

Do not store more then 100.000 different values into one tag, this may happen if you make a ID a tag

To query on a certain timestamp you need to add a postfix, declaring the type: ns, u, ms, s, m, h, d, w

Example-Queries

SELECT <stuff> FROM <measurement_name> WHERE <some_conditions>
SELECT * FROM "foodships" WHERE "planet" = 'Saturn'
SELECT * FROM "foodships" WHERE "planet" = 'Saturn' AND time > '2015-04-16 12:00:01'
SELECT * FROM "foodships" WHERE time > now() - 1h

InfluxQL also supports regular expressions, arithmetic in expressions, SHOW statements, and GROUP BY statements. See our data exploration page for an in-depth discussion of those topics. InfluxQL functions include COUNT, MIN, MAX, MEDIAN, DERIVATIVE and more. For a full list check out the functions page.

citation of influx-wiki

SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
SELECT "<field_key>","<tag_key>"
SELECT "<field_key>"::field,"<tag_key>"::tag

SELECT ("water_level" * 2) + 4 from "h2o_feet"
SELECT * FROM "h2o_feet","h2o_pH"
SELECT * FROM "h2o_feet" WHERE "water_level" > 8
SELECT * FROM "h2o_feet" WHERE "water_level" + 2 > 11.9

SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | <tag_key>[,<tag_key]]
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)

SELECT mean("cpuUtil") FROM "cpuram" WHERE $timeFilter GROUP BY time(15m)

IMPORTANT: We are using retention policies. You need to extend the measurement_name by the retention policy name to not only aquire data from the default retention policy. Default is rp_days_14, for any other pre-append it to the measurement name: rp_days_90.jobs.

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.

Element	Double quotes	Single quotes
Timestamp	Never	Never
Measurements, tag keys, tag values, field key	Never*	Never*
Field Values	Double quote string field values. Do not double quote floats, integers, or Booleans.	Never

• InfluxDB line protocol allows users to double and single quote measurement names, tag keys, tag values, and field keys. It will, however, assume that the double or single quotes are part of the name, key, or value. This can complicate query syntax (see the example below).

Queries with quoting

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

Duplicate points

A point is uniquely identified by the measurement name, tag set, field set, and timestamp

If you write a point to a series with a timestamp that matches an existing point, the field set becomes a union of the old and new field set, and conflicts favor the new field set.

If all tags and the time are the same, the value is overwritten.

If a tag differs, a new row is created

Insert Into

You can reinsert data to delete unused colums and to keep data if you changed a measurement name or if you start to use rentation policity

SELECT * INTO cpuram2 FROM "cpuram"

Common Problems

Order By

This function very limited. It is always ordered by the timestamp, but you can specify by ORDER BY time [ASC/DESC] in which direction. Any other key than time is not supported, but mostly not needed.

Keywords

Some of our colum names are keywords. If you query any table, colum etc i recommend doing so in double-qoutations marks. Single quotation marks are not supported. Keywords are as example duration.

Aggregate Function

You can aggregate data by build-in functions If you do so, you are not allowed to mix aggregate data with normal fields/tags. You can use group by (10s) to aggregate over a time range

No Data is returned even if the should be data

Make sure your WHERE time > now()-1d clauses matchs the wanted timerange. Some data isnt aquired every 5 minutes. Also make sure to declare the right retention policy. Some data is first off saved into the rp_days_14-RP, some only into the rp_days_90 and now below. Add this retention policy to the tablename: rp_days_14.jobs -> Does not work rp_days_14.jobs -> works