The WHERE
clause filters data based on fields, tags, and timestamps.
Syntax
SELECT_clause FROM_clause WHERE <conditional_expression> [(AND|OR) <conditional_expression> [...]]
Description
You can specify conditional expressions in the WHERE
clause to filter data based on fields, tags, and timestamps.
field
field_key <operator> ['string' | boolean | float | integer]
The WHERE
clause supports the comparison among the specified field values. The data types of the specified field values can be STRING, BOOLEAN, FLOAT, or INT.
Use single quotation marks (‘) to enclose the field values of the STRING type in the WHERE
clause. If the field values of the STRING type are not enclosed in quotation marks or are enclosed in double quotation marks (“) in a query, the query does not return data. If this occurs, the query does not return an error in most cases.
The following table lists the supported operators.
= | Equal to |
---|---|
<> | Not equal to |
! = | Not equal to |
> | Greater than |
>= | Greater than or equal to |
< | Less than |
<= | Less than or equal to |
The arithmetic operations and regular expressions are also supported.
tag
tag_key <operator> ['tag_value']
Use single quotation marks (‘) to enclose tag values in the WHERE
clause. If tag values are not enclosed in quotation marks or are enclosed in double quotation marks (“) in a query, the query does not return data. If this occurs, the query does not return an error in most cases.
The following table lists the supported operators.
= | Equal to |
---|---|
<> | Not equal to |
! = | Not equal to |
The regular expressions are also supported.
timestamp
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 that include GROUP BY time()
clauses, the default time range is from 1677-09-21 00:12:43.145224194 UTC
to the time specified by the now()
function.
For more information about how to specify other time ranges in the WHERE clause, see Time syntax.
Examples
Query the data where the field values meet the 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 query returns the requested data from the h2o_feet
measurement. The data meets the following condition: The value of the water_level
field key is greater than eight.
Query the data where the field values of the STRING type meet the 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 query returns the requested data from the h2o_feet
measurement. The data meets the following condition: The value of the level description
field key equals to the below 3 feet
string. In the WHERE
clause, the field values of the STRING type must be enclosed in single quotation marks (‘).
Query the data where the field values meet the specified conditions by using a query that includes basic arithmetic operations in the WHERE
clause
> 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 query returns the requested data from the h2o_feet
measurement. The data meets the following condition: If you add 2 to the value of the water_level
field key, the result is greater than 11.9. Note that TSDB for InfluxDB® follows the standard order of arithmetic operations. For more information, see the “InfluxQL mathematical operators” topic.
Query data where the tag values meet the 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 query returns the requested data from the h2o_feet
measurement. The data meets the following condition: The value of the location
tag key is santa_monica
. In the WHERE
clause, the tag values of the STRING type must be enclosed in single quotation marks (‘).
Query data where the field values and the tag values meet the specified conditions
> 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 the requested data from the h2o_feet
measurement. The data meets the following conditions: The value of the location
tag key is not equal to santa_monica
. The value of the water_level
field key is less than -0.59 or greater than 9.95. The WHERE
clause supports the AND
and OR
operators. You can separate these operators with parentheses ().
Query the data whose timestamps meet the specified conditions
> SELECT * FROM "h2o_feet" WHERE time > now() - 7d
The query returns the requested data from the h2o_feet
measurement. The data meets the following condition: The timestamps of the data occurred in the past seven days. For more information about the time syntax supported by the WHERE
clause, see Time syntax.
FAQ about the WHERE
clause
Why does a WHERE
clause query return no data?
In most cases, the cause of this issue is that tag values or the field values of the STRING type are not enclosed in single quotation marks (‘). If tag values or the field values of the STRING type are not enclosed in quotation marks or are enclosed in double quotation marks (“) in a query, the query does not return data. If this occurs, the query does not return an error in most cases.
In the following sample code, the santa_monica
tag value is not enclosed in quotation marks in the first query and is enclosed in double quotation marks (“) in the second query. These two queries return no data. The santa_monica
tag value is enclosed in single quotation marks (‘) in the third query. Therefore, the third query returns the expected results because the syntax is valid.
> 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
In the following sample code, the string field value greater than 9 feet
is not enclosed in quotation marks in the first query and is enclosed in double quotation marks (“) in the second query. The first query returns an error because the field value of the STRING type includes spaces. The second query returns no data. The string field value at or greater than 9 feet
is enclosed in single quotation marks (‘) in the third query. Therefore, the third query returns the expected results because the syntax is valid.
> 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
InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.