All Products
Search

This Product

    Time Series Database:SELECT statements

    Document Center

    Time Series Database:SELECT statements

    Last Updated:Apr 23, 2021

    You can use a SELECT statement to query data in one or more measurements.

    Syntax

    SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

    Syntax description

    Each SELECT statement includes a SELECT clause and a FROM clause.

    SELECT clauses

    SELECT clauses support the following formats:

    • SELECT *: returns all fields and tags.

    • SELECT "<field_key>": returns a specified field.

    • SELECT "<field_key>","<field_key>": returns multiple fields.

    • SELECT "<field_key>","<tag_key>": returns a specified field and tag. If you specify tags in a SELECT clause, you must specify one or more fields.

    • SELECT "<field_key>"::field,"<tag_key>"::tag: returns a specified field and tag. The ::[field | tag] syntax defines the type of an identifier to differentiate the field key and tag key that have the same name.

    SELECT clauses also support arithmetic operators, functions, conversion operators, and regular expressions.

    FROM clauses

    FROM clauses support the following measurement formats:

    FROM <measurement_name>: returns data from a measurement. If you use the command-line interface (CLI) that is provided by TSDB for InfluxDB®, the measurement belongs to the database that is specified by the USE parameter. In this case, the default retention policy is used. If you call an HTTP API operation, the measurement belongs to the database that is specified by the db parameter. In this case, the default retention policy is used.

    FROM <measurement_name>,<measurement_name>: returns data from multiple measurements.

    FROM <database_name>.<retention_policy_name>.<measurement_name>: returns data from a measurement that is fully qualified. The measurement is fully qualified by specifying a database and retention policy.

    FROM <database_name>..<measurement_name>: returns data from a measurement that belongs to a specified database and uses the default retention policy.

    FROM clauses also support regular expressions.

    Quotations

    If an identifier includes characters other than [A-z,0-9,_], or start with a digit or an InfluxQL keyword, the identifier must be enclosed in double quotation marks("). We recommend that you use double quotation marks(") for identifiers.

    Note

    Note: The syntax of quotation marks (") for identifiers is different from the syntax that is defined in the InfluxDB line protocol.

    Examples

    Query all fields and tags in a single measurement

    > SELECT * FROM "h2o_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:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

    This statement is used to query all fields and tags in the h2o_feet measurement.

    If you use the CLI, enter the USE NOAA_water_database before the statement. The queried measurement belongs to the database that is specified by the USE parameter and uses the default retention policy. If you call an HTTP API operation, set the db parameter to NOAA_water_database. If you do not specify the rp parameter, the default retention policy of the database is used.

    Query specified fields and tags in a single measurement

    > SELECT "level description","location","water_level" FROM "h2o_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:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

    In this statement, the level description field, the water_level field, and the location tag are specified. If you specify tags in a SELECT clause, you must specify one or more fields.

    Query fields and tags with specified identifier types in a single measurement

    > SELECT "level description"::field,"location"::tag,"water_level"::field FROM "h2o_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:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

    In this statement, the level description field, the water_level field, and the location tag are specified. The ::[field | tag] syntax defines the type of each identifier. To differentiate the field key and tag key that have the same name, you must use the ::[field | tag] syntax. If a statement does not contain the field key and tag key that have the same name, you do not need to use this syntax.

    Query all fields in a single measurement

    > SELECT *::field FROM "h2o_feet"

name: h2o_feet
--------------
time                   level description      water_level
2015-08-18T00:00:00Z   below 3 feet           2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   4.938

    In this example, all fields in the h2o_feet measurement are queried. SELECT clauses supports a combination of the * and :: syntax.

    Query a specified field in a single measurement and perform arithmetic operations

    > SELECT ("water_level"*2)+4 from "h2o_feet"

name: h2o_feet
--------------
time                   water_level
2015-08-18T00:00:00Z20.24
2015-08-18T00:00:00Z8.128
[...]
2015-09-18T21:36:00Z14.132
2015-09-18T21:42:00Z13.876

    Query all data in multiple measurements

    > SELECT * FROM "h2o_feet","h2o_pH"

name: h2o_feet
--------------
time                   level description      location       pH   water_level
2015-08-18T00:00:00Z   below 3 feet           santa_monica        2.064
2015-08-18T00:00:00Z   between 6 and 9 feet   coyote_creek        8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica        5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica        4.938

name: h2o_pH
------------
time                   level description   location       pH   water_level
2015-08-18T00:00:00Z                       santa_monica   6
2015-08-18T00:00:00Z                       coyote_creek   7
[...]
2015-09-18T21:36:00Z                       santa_monica   8
2015-09-18T21:42:00Z                       santa_monica   7

    In this example, all fields and tags in the h2o_feet and h2o_pH measurements are queried. You must separate multiple measurements with commas (,).

    Query all data in a fully qualified measurement

    > SELECT * FROM "NOAA_water_database"."autogen"."h2o_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:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

    In this example, all data in the h2o_feet measurement is queried. The h2o_feet measurement belongs to the NOAA_water_database database and uses the autogen retention policy.

    If you use the CLI, you can specify a fully qualified measurement instead of using the USE and DEFAULT parameters. If you call an HTTP API operation, you can specify a fully qualified measurement instead of using the db and rp parameters.

    Query all data in a measurement that belongs to a specified database

    > SELECT * FROM "NOAA_water_database".."h2o_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:00:00Z   between 6 and 9 feet   coyote_creek   8.12
[...]
2015-09-18T21:36:00Z   between 3 and 6 feet   santa_monica   5.066
2015-09-18T21:42:00Z   between 3 and 6 feet   santa_monica   4.938

    In this example, all data in the h2o_feet measurement is queried. The h2o_feet measurement belongs to the NOAA_water_database database and uses the default retention policy. ..indicates the default retention policy of the database.

    If you use the CLI, you can specify a database instead of using the USE parameter. If you call an HTTP API operation, you can specify a database instead of using the db parameter.

    FAQ about SELECT statements

    How do I query tag keys by using a SELECT clause?

    To query tag keys, you must specify at least one field key in the SELECT clause. If the SELECT clause contains only tag keys, an empty string is returned. This is caused by the data storage mechanism of TSDB.

    Example

    In the following example, no data is returned because only the location tag key is specified in the SELECT clause:

    > SELECT "location" FROM "h2o_feet"
>

    To query the location tag key, you must specify at least one field key in the SELECT clause. In the following example, the water_level field key is specified:

    > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3
name: h2o_feet
time                   water_level  location
-----------------------
2015-08-18T00:00:00Z8.12         coyote_creek
2015-08-18T00:00:00Z2.064        santa_monica
[...]
2015-09-18T21:36:00Z5.066        santa_monica
2015-09-18T21:42:00Z4.938        santa_monica

    InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.