All Products
Search

This Product

    WHERE Statement

    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
    3. name: h2o_feet
    4. --------------
    5. time                   level description      location       water_level
    6. 2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek   8.12
    7. 2015-08-18T00:06:00Z   between 6 and 9 feet   coyote_creek   8.005
    8. [...]
    9. 2015-09-18T00:12:00Z   between 6 and 9 feet   coyote_creek   8.189
    10. 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'
    3. name: h2o_feet
    4. --------------
    5. time                   level description   location       water_level
    6. 2015-08-18T00:00:00Z   below 3 feet        santa_monica   2.064
    7. 2015-08-18T00:06:00Z   below 3 feet        santa_monica   2.116
    8. [...]
    9. 2015-09-18T14:06:00Z   below 3 feet        santa_monica   2.999
    10. 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
    3. name: h2o_feet
    4. --------------
    5. time                   level description           location       water_level
    6. 2015-08-29T07:06:00Z   at or greater than 9 feet   coyote_creek   9.902
    7. 2015-08-29T07:12:00Z   at or greater than 9 feet   coyote_creek   9.938
    8. 2015-08-29T07:18:00Z   at or greater than 9 feet   coyote_creek   9.957
    9. 2015-08-29T07:24:00Z   at or greater than 9 feet   coyote_creek   9.964
    10. 2015-08-29T07:30:00Z   at or greater than 9 feet   coyote_creek   9.954
    11. 2015-08-29T07:36:00Z   at or greater than 9 feet   coyote_creek   9.941
    12. 2015-08-29T07:42:00Z   at or greater than 9 feet   coyote_creek   9.925
    13. 2015-08-29T07:48:00Z   at or greater than 9 feet   coyote_creek   9.902
    14. 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'
    3. name: h2o_feet
    4. --------------
    5. time                   water_level
    6. 2015-08-18T00:00:00Z   2.064
    7. 2015-08-18T00:06:00Z   2.116
    8. [...]
    9. 2015-09-18T21:36:00Z   5.066
    10. 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)
    3. name: h2o_feet
    4. --------------
    5. time                   water_level
    6. 2015-08-29T07:18:00Z   9.957
    7. 2015-08-29T07:24:00Z   9.964
    8. 2015-08-29T07:30:00Z   9.954
    9. 2015-08-29T14:30:00Z   -0.61
    10. 2015-08-29T14:36:00Z   -0.591
    11. 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
    3. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = "santa_monica"
    5. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica'
    7. name: h2o_feet
    8. --------------
    9. time                   water_level
    10. 2015-08-18T00:00:00Z   2.064
    11. [...]
    12. 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
    3. ERR: error parsing query: found than, expected ; at line 1, char 86
    5. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = "at or greater than 9 feet"
    7. > SELECT "level description" FROM "h2o_feet" WHERE "level description" = 'at or greater than 9 feet'
    9. name: h2o_feet
    10. --------------
    11. time                   level description
    12. 2015-08-26T04:00:00Z   at or greater than 9 feet
    13. [...]
    14. 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®.

    Free Trial Free Trial