All Products
Search
Document Center

WHERE Statement

Last Updated: Aug 04, 2020

The WHERE clause filters data based on fields, tags, and timestamps.

Syntax

  1. 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

  1. 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

  1. 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

  1. > SELECT * FROM "h2o_feet" WHERE "water_level" > 8
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z between 6 and 9 feet coyote_creek 8.12
  6. 2015-08-18T00:06:00Z between 6 and 9 feet coyote_creek 8.005
  7. [...]
  8. 2015-09-18T00:12:00Z between 6 and 9 feet coyote_creek 8.189
  9. 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

  1. > SELECT * FROM "h2o_feet" WHERE "level description" = 'below 3 feet'
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-18T00:00:00Z below 3 feet santa_monica 2.064
  6. 2015-08-18T00:06:00Z below 3 feet santa_monica 2.116
  7. [...]
  8. 2015-09-18T14:06:00Z below 3 feet santa_monica 2.999
  9. 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

  1. > SELECT * FROM "h2o_feet" WHERE "water_level" + 2 > 11.9
  2. name: h2o_feet
  3. --------------
  4. time level description location water_level
  5. 2015-08-29T07:06:00Z at or greater than 9 feet coyote_creek 9.902
  6. 2015-08-29T07:12:00Z at or greater than 9 feet coyote_creek 9.938
  7. 2015-08-29T07:18:00Z at or greater than 9 feet coyote_creek 9.957
  8. 2015-08-29T07:24:00Z at or greater than 9 feet coyote_creek 9.964
  9. 2015-08-29T07:30:00Z at or greater than 9 feet coyote_creek 9.954
  10. 2015-08-29T07:36:00Z at or greater than 9 feet coyote_creek 9.941
  11. 2015-08-29T07:42:00Z at or greater than 9 feet coyote_creek 9.925
  12. 2015-08-29T07:48:00Z at or greater than 9 feet coyote_creek 9.902
  13. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'
  2. name: h2o_feet
  3. --------------
  4. time water_level
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. [...]
  8. 2015-09-18T21:36:00Z 5.066
  9. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" <> 'santa_monica' AND ("water_level" < -0.59 OR "water_level" > 9.95)
  2. name: h2o_feet
  3. --------------
  4. time water_level
  5. 2015-08-29T07:18:00Z 9.957
  6. 2015-08-29T07:24:00Z 9.964
  7. 2015-08-29T07:30:00Z 9.954
  8. 2015-08-29T14:30:00Z -0.61
  9. 2015-08-29T14:36:00Z -0.591
  10. 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

  1. > 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.

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = santa_monica
  2. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = "santa_monica"
  3. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'
  4. name: h2o_feet
  5. --------------
  6. time water_level
  7. 2015-08-18T00:00:00Z 2.064
  8. [...]
  9. 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.

  1. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = at or greater than 9 feet
  2. ERR: error parsing query: found than, expected ; at line 1, char 86
  3. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = "at or greater than 9 feet"
  4. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = 'at or greater than 9 feet'
  5. name: h2o_feet
  6. --------------
  7. time level description
  8. 2015-08-26T04:00:00Z at or greater than 9 feet
  9. [...]
  10. 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®.