You can use a WHERE clause to filter data based on fields, tags, or timestamps.

Syntax

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

Syntax description

WHERE clauses support conditional expressions of fields, tags, or timestamps.

Fields

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

WHERE clauses support the comparison of field values. The field values can be strings, Boolean values, floating-point numbers, or integers.

You must enclose field values of the string type in single quotation marks ('). If you do not use single quotation marks (') or use double quotation marks ("), no results or error messages are returned. The following table describes the supported operators.

Operator Description
= Equal to
<> Not equal to
!= Not equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to

Arithmetic operators and regular expressions are also supported.

Tags

tag_key <operator> ['tag_value']

Enclose tag values with single quotation marks ('). If you do not use single quotation marks (') or use double quotation marks ("), no results or error messages are returned. The following table describes the supported operators.

Operator Description
= Equal to
<> Not equal to
!= Not equal to

Regular expressions are also supported.

Timestamps

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

Examples

Query data points whose field values meet specified conditions 
> 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 data points in the h2o_feet measurement are queried. The data points meet the following condition: The value of the water_level field is greater than 8.

Query data points whose field values of the string type meet specified conditions

> 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 data points in the h2o_feet measurement are queried. The data points meet the following condition: The value of the level description field is below 3 feet. You must enclose field values of the string type in single quotation marks (').

Query data points whose field values meet specified conditions (The WHERE clause contains arithmetic operators) 

> SELECT * FROM "h2o_feet" WHERE "water_level" + 2 > 11.9
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 data points in the h2o_feet measurement are queried. The data points meet the following condition: If you add 2 to the value of the water_level field, the total value is greater than 11.9. The standard precedence of arithmetic operators is applied. For more information, see the InfluxQL arithmetic operators topic.

Query data points whose tag values meet specified conditions

> 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 data points in the h2o_feet measurement are queried. The data points meet the following condition: The value of the location tag is santa_monica. You must enclose field values of the string type in single quotation marks (').

Query data points whose field values and tag values meet specified conditions at the same time 

> 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 data points in the h2o_feet measurement are queried. The data points meet the following conditions: The value of the location tag is not santa_monica, and the value of the water_level field is smaller than -0.59 or greater than 9.95. WHERE clauses support the AND and OR operators. You can logically separate these two operators with parentheses ().

Query data points whose timestamps meet specified conditions

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

The data points in the h2o_feet measurement are queried. The data points meet the following condition: The timestamps are generated in the last seven days. For more information about the timestamp syntax that is supported by WHERE clauses, see the Time syntax topic.

FAQ about WHERE clauses

How do I troubleshoot the error if no results are returned?

Check whether tag values or field values of the string type are enclosed in single quotation marks ('). If you do not use single quotation marks (') or use double quotation marks ("), no results or error messages are returned.

The following code block shows three statements. In the first two statements, the santa_monica tag value is not enclosed in single quotation marks (') or is enclosed in double quotation marks ("). In this case, no results are returned. In the third statement, the santa_monica tag value is enclosed in single quotation marks ('). In this case, results are returned. 

> 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 following code block shows three statements. In the first two statements, the field value of the string type is not enclosed in single quotation marks (') or is enclosed in double quotation marks ("). For the first statement, an error message is returned because the field value contains spaces. For the second statement, no results are returned. In the third statement, the field value is enclosed in single quotation marks ('). In this case, results are returned. 

> 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