Explore your schema using InfluxQL

InfluxQL is an SQL-like query language for interacting with data in InfluxDB. The following sections cover useful query syntax for exploring your schema.

SHOW DATABASESSHOW RETENTION POLICIESSHOW SERIES
SHOW MEASUREMENTSSHOW TAG KEYSSHOW TAG VALUES
SHOW FIELD KEYSFilter meta queries by time

Sample data

The data used in this document are available for download on the Sample Data page.

Before proceeding, login to the Influx CLI.

  1. $ influx -precision rfc3339
  2. Connected to http://localhost:8086 version 1.4.x
  3. InfluxDB shell 1.4.x
  4. >

SHOW DATABASES

Returns a list of all databases on your instance.

Syntax

  1. SHOW DATABASES

Examples

Run a SHOW DATABASES query

  1. > SHOW DATABASES
  2. name: databases
  3. name
  4. ----
  5. NOAA_water_database
  6. _internal

The query returns database names in a tabular format. This InfluxDB instance has two databases: NOAA_water_database and _internal.

SHOW RETENTION POLICIES

Returns a list of retention policies for the specified database.

Syntax

  1. SHOW RETENTION POLICIES [ON <database_name>]

Description of syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

Examples

Run a SHOW RETENTION POLICIES query with the ON clause

  1. > SHOW RETENTION POLICIES ON NOAA_water_database
  2. name duration shardGroupDuration replicaN default
  3. ---- -------- ------------------ -------- -------
  4. autogen 0s 168h0m0s 1 true

The query returns the list of retention policies in the NOAA_water_database database in tabular format. The database has one retention policy called autogen. The autogen retention policy has an infinite duration, a seven-day shard group duration, a replication factor of one, and it is the DEFAULT retention policy for the database.

Run a SHOW RETENTION POLICIES query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW RETENTION POLICIES
  4. name duration shardGroupDuration replicaN default
  5. ---- -------- ------------------ -------- -------
  6. autogen 0s 168h0m0s 1 true

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW RETENTION POLICIES"
  2. {
  3. "results": [
  4. {
  5. "statement_id": 0,
  6. "series": [
  7. {
  8. "columns": [
  9. "name",
  10. "duration",
  11. "shardGroupDuration",
  12. "replicaN",
  13. "default"
  14. ],
  15. "values": [
  16. [
  17. "autogen",
  18. "0s",
  19. "168h0m0s",
  20. 1,
  21. true
  22. ]
  23. ]
  24. }
  25. ]
  26. }
  27. ]
  28. }

SHOW SERIES

Returns a list of series for the specified database.

Syntax

  1. SHOW SERIES [ON <database_name>] [FROM_clause] [WHERE <tag_key> <operator> [ '<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

Description of syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

The FROM, WHERE, LIMIT, and OFFSET clauses are optional. The WHERE clause supports tag comparisons; field comparisons are not valid for the SHOW SERIES query.

Supported operators in the WHERE clause: =   equal to <> not equal to != not equal to =~ matches against !~ doesn’t match against

See the Data Exploration page for documentation on the FROM clause, LIMIT clause, OFFSET clause, and on Regular Expressions in Queries.

Examples

Run a SHOW SERIES query with the ON clause

  1. // Returns series for all shards in the database
  2. > SHOW SERIES ON NOAA_water_database
  3. key
  4. ---
  5. average_temperature,location=coyote_creek
  6. average_temperature,location=santa_monica
  7. h2o_feet,location=coyote_creek
  8. h2o_feet,location=santa_monica
  9. h2o_pH,location=coyote_creek
  10. h2o_pH,location=santa_monica
  11. h2o_quality,location=coyote_creek,randtag=1
  12. h2o_quality,location=coyote_creek,randtag=2
  13. h2o_quality,location=coyote_creek,randtag=3
  14. h2o_quality,location=santa_monica,randtag=1
  15. h2o_quality,location=santa_monica,randtag=2
  16. h2o_quality,location=santa_monica,randtag=3
  17. h2o_temperature,location=coyote_creek
  18. h2o_temperature,location=santa_monica

The query’s output is similar to the line protocol format. Everything before the first comma is the measurement name. Everything after the first comma is either a tag key or a tag value. The NOAA_water_database has five different measurements and 14 different series.

Run a SHOW SERIES query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW SERIES
  4. key
  5. ---
  6. average_temperature,location=coyote_creek
  7. average_temperature,location=santa_monica
  8. h2o_feet,location=coyote_creek
  9. h2o_feet,location=santa_monica
  10. h2o_pH,location=coyote_creek
  11. h2o_pH,location=santa_monica
  12. h2o_quality,location=coyote_creek,randtag=1
  13. h2o_quality,location=coyote_creek,randtag=2
  14. h2o_quality,location=coyote_creek,randtag=3
  15. h2o_quality,location=santa_monica,randtag=1
  16. h2o_quality,location=santa_monica,randtag=2
  17. h2o_quality,location=santa_monica,randtag=3
  18. h2o_temperature,location=coyote_creek
  19. h2o_temperature,location=santa_monica

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW SERIES"
  2. {
  3. "results": [
  4. {
  5. "statement_id": 0,
  6. "series": [
  7. {
  8. "columns": [
  9. "key"
  10. ],
  11. "values": [
  12. [
  13. "average_temperature,location=coyote_creek"
  14. ],
  15. [
  16. "average_temperature,location=santa_monica"
  17. ],
  18. [
  19. "h2o_feet,location=coyote_creek"
  20. ],
  21. [
  22. "h2o_feet,location=santa_monica"
  23. ],
  24. [
  25. "h2o_pH,location=coyote_creek"
  26. ],
  27. [
  28. "h2o_pH,location=santa_monica"
  29. ],
  30. [
  31. "h2o_quality,location=coyote_creek,randtag=1"
  32. ],
  33. [
  34. "h2o_quality,location=coyote_creek,randtag=2"
  35. ],
  36. [
  37. "h2o_quality,location=coyote_creek,randtag=3"
  38. ],
  39. [
  40. "h2o_quality,location=santa_monica,randtag=1"
  41. ],
  42. [
  43. "h2o_quality,location=santa_monica,randtag=2"
  44. ],
  45. [
  46. "h2o_quality,location=santa_monica,randtag=3"
  47. ],
  48. [
  49. "h2o_temperature,location=coyote_creek"
  50. ],
  51. [
  52. "h2o_temperature,location=santa_monica"
  53. ]
  54. ]
  55. }
  56. ]
  57. }
  58. ]
  59. }

Run a SHOW SERIES query with several clauses

  1. > SHOW SERIES ON NOAA_water_database FROM "h2o_quality" WHERE "location" = 'coyote_creek' LIMIT 2
  2. key
  3. ---
  4. h2o_quality,location=coyote_creek,randtag=1
  5. h2o_quality,location=coyote_creek,randtag=2

The query returns all series in the NOAA_water_database database that are associated with the h2o_quality measurement and the tag location = coyote_creek. The LIMIT clause limits the number of series returned to two.

Run a SHOW SERIES query limited by time

Limit series returned within a specified shard group duration.

  1. // Returns all series in the current shard.
  2. > SHOW SERIES ON NOAA_water_database WHERE time > now() - 1m
  3. key
  4. ---
  5. average_temperature,location=coyote_creek
  6. h2o_feet,location=coyote_creek
  7. h2o_pH,location=coyote_creek
  8. h2o_quality,location=coyote_creek,randtag=1
  9. h2o_quality,location=coyote_creek,randtag=2
  10. h2o_quality,location=coyote_creek,randtag=3
  11. h2o_temperature,location=coyote_creek

The query above returns all series in the NOAA_water_database database in the current shard group. The WHERE clause limits results to series in the shard group that contain a timestamp in the last minute. Note, if a shard group duration is 7 days, results returned may be up to 7 days old.

  1. // Returns all series in shard groups that contain a timestamp in the last 28 days.
  2. > SHOW SERIES ON NOAA_water_database WHERE time > now() - 28d
  3. key
  4. ---
  5. average_temperature,location=coyote_creek
  6. average_temperature,location=santa_monica
  7. h2o_feet,location=coyote_creek
  8. h2o_feet,location=santa_monica
  9. h2o_pH,location=coyote_creek
  10. h2o_pH,location=santa_monica
  11. h2o_quality,location=coyote_creek,randtag=1
  12. h2o_quality,location=coyote_creek,randtag=2
  13. h2o_quality,location=coyote_creek,randtag=3
  14. h2o_quality,location=santa_monica,randtag=1
  15. h2o_quality,location=santa_monica,randtag=2
  16. h2o_quality,location=santa_monica,randtag=3
  17. h2o_temperature,location=coyote_creek
  18. h2o_temperature,location=santa_monica

Note, if the specified shard group duration is 7 days, the query above returns series for the last 3 or 4 shards.

SHOW MEASUREMENTS

Returns a list of measurements for the specified database.

Syntax

  1. SHOW MEASUREMENTS [ON <database_name>] [WITH MEASUREMENT <operator> ['<measurement_name>' | <regular_expression>]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

Description of Syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

The WITH, WHERE, LIMIT and OFFSET clauses are optional. The WHERE clause supports tag comparisons; field comparisons are not valid for the SHOW MEASUREMENTS query.

Supported operators in the WHERE clause: =   equal to <> not equal to != not equal to =~ matches against !~ doesn’t match against

See the Data Exploration page for documentation on the LIMIT clause, OFFSET clause, and on Regular expressions in queries.

Examples

Run a SHOW MEASUREMENTS query with the ON clause

  1. > SHOW MEASUREMENTS ON NOAA_water_database
  2. name: measurements
  3. name
  4. ----
  5. average_temperature
  6. h2o_feet
  7. h2o_pH
  8. h2o_quality
  9. h2o_temperature

The query returns the list of measurements in the NOAA_water_database database. The database has five measurements: average_temperature, h2o_feet, h2o_pH, h2o_quality, and h2o_temperature.

Run a SHOW MEASUREMENTS query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW MEASUREMENTS
  4. name: measurements
  5. name
  6. ----
  7. average_temperature
  8. h2o_feet
  9. h2o_pH
  10. h2o_quality
  11. h2o_temperature

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW MEASUREMENTS"
  2. {
  3. {
  4. "results": [
  5. {
  6. "statement_id": 0,
  7. "series": [
  8. {
  9. "name": "measurements",
  10. "columns": [
  11. "name"
  12. ],
  13. "values": [
  14. [
  15. "average_temperature"
  16. ],
  17. [
  18. "h2o_feet"
  19. ],
  20. [
  21. "h2o_pH"
  22. ],
  23. [
  24. "h2o_quality"
  25. ],
  26. [
  27. "h2o_temperature"
  28. ]
  29. ]
  30. }
  31. ]
  32. }
  33. ]
  34. }

Run a SHOW MEASUREMENTS query with several clauses (i)

  1. > SHOW MEASUREMENTS ON NOAA_water_database WITH MEASUREMENT =~ /h2o.*/ LIMIT 2 OFFSET 1
  2. name: measurements
  3. name
  4. ----
  5. h2o_pH
  6. h2o_quality

The query returns the measurements in the NOAA_water_database database that start with h2o. The LIMIT and OFFSET clauses limit the number of measurement names returned to two and offset the results by one, skipping the h2o_feet measurement.

Run a SHOW MEASUREMENTS query with several clauses (ii)

  1. > SHOW MEASUREMENTS ON NOAA_water_database WITH MEASUREMENT =~ /h2o.*/ WHERE "randtag" =~ /\d/
  2. name: measurements
  3. name
  4. ----
  5. h2o_quality

The query returns all measurements in the NOAA_water_database that start with h2o and have values for the tag key randtag that include an integer.

SHOW TAG KEYS

Returns a list of tag keys associated with the specified database.

Syntax

  1. SHOW TAG KEYS [ON <database_name>] [FROM_clause] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

Description of syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

The FROM clause and the WHERE clause are optional. The WHERE clause supports tag comparisons; field comparisons are not valid for the SHOW TAG KEYS query.

Supported operators in the WHERE clause: =   equal to <> not equal to != not equal to =~ matches against !~ doesn’t match against

See the Data Exploration page for documentation on the FROM clause, LIMIT clause, OFFSET clause, and on Regular Expressions in Queries.

Examples

Run a SHOW TAG KEYS query with the ON clause

  1. > SHOW TAG KEYS ON "NOAA_water_database"
  2. name: average_temperature
  3. tagKey
  4. ------
  5. location
  6. name: h2o_feet
  7. tagKey
  8. ------
  9. location
  10. name: h2o_pH
  11. tagKey
  12. ------
  13. location
  14. name: h2o_quality
  15. tagKey
  16. ------
  17. location
  18. randtag
  19. name: h2o_temperature
  20. tagKey
  21. ------
  22. location

The query returns the list of tag keys in the NOAA_water_database database. The output groups tag keys by measurement name; it shows that every measurement has the location tag key and that the h2o_quality measurement has an additional randtag tag key.

Run a SHOW TAG KEYS query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW TAG KEYS
  4. name: average_temperature
  5. tagKey
  6. ------
  7. location
  8. name: h2o_feet
  9. tagKey
  10. ------
  11. location
  12. name: h2o_pH
  13. tagKey
  14. ------
  15. location
  16. name: h2o_quality
  17. tagKey
  18. ------
  19. location
  20. randtag
  21. name: h2o_temperature
  22. tagKey
  23. ------
  24. location

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode "q=SHOW TAG KEYS"
  2. {
  3. "results": [
  4. {
  5. "statement_id": 0,
  6. "series": [
  7. {
  8. "name": "average_temperature",
  9. "columns": [
  10. "tagKey"
  11. ],
  12. "values": [
  13. [
  14. "location"
  15. ]
  16. ]
  17. },
  18. {
  19. "name": "h2o_feet",
  20. "columns": [
  21. "tagKey"
  22. ],
  23. "values": [
  24. [
  25. "location"
  26. ]
  27. ]
  28. },
  29. {
  30. "name": "h2o_pH",
  31. "columns": [
  32. "tagKey"
  33. ],
  34. "values": [
  35. [
  36. "location"
  37. ]
  38. ]
  39. },
  40. {
  41. "name": "h2o_quality",
  42. "columns": [
  43. "tagKey"
  44. ],
  45. "values": [
  46. [
  47. "location"
  48. ],
  49. [
  50. "randtag"
  51. ]
  52. ]
  53. },
  54. {
  55. "name": "h2o_temperature",
  56. "columns": [
  57. "tagKey"
  58. ],
  59. "values": [
  60. [
  61. "location"
  62. ]
  63. ]
  64. }
  65. ]
  66. }
  67. ]
  68. }

Run a SHOW TAG KEYS query with several clauses

  1. > SHOW TAG KEYS ON "NOAA_water_database" FROM "h2o_quality" LIMIT 1 OFFSET 1
  2. name: h2o_quality
  3. tagKey
  4. ------
  5. randtag

The query returns tag keys from the h2o_quality measurement in the NOAA_water_database database. The LIMIT and OFFSET clauses limit the number of tag keys returned to one and offsets the results by one.

SHOW TAG VALUES

Returns the list of tag values for the specified tag key(s) in the database.

Syntax

  1. SHOW TAG VALUES [ON <database_name>][FROM_clause] WITH KEY [ [<operator> "<tag_key>" | <regular_expression>] | [IN ("<tag_key1>","<tag_key2")]] [WHERE <tag_key> <operator> ['<tag_value>' | <regular_expression>]] [LIMIT_clause] [OFFSET_clause]

Description of syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

The WITH clause is required. It supports specifying a single tag key, a regular expression, and multiple tag keys.

The FROM, WHERE, LIMIT, and OFFSET clauses are optional. The WHERE clause supports tag comparisons; field comparisons are not valid for the SHOW TAG KEYS query.

Supported operators in the WITH and WHERE clauses: =   equal to <> not equal to != not equal to =~ matches against !~ doesn’t match against

See the Data Exploration page for documentation on the FROM clause, LIMIT clause, OFFSET clause, and on Regular Expressions in Queries.

Examples

Run a SHOW TAG VALUES query with the ON clause

  1. > SHOW TAG VALUES ON "NOAA_water_database" WITH KEY = "randtag"
  2. name: h2o_quality
  3. key value
  4. --- -----
  5. randtag 1
  6. randtag 2
  7. randtag 3

The query returns all tag values of the randtag tag key in the NOAA_water_database database. SHOW TAG VALUES groups query results by measurement name.

Run a SHOW TAG VALUES query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW TAG VALUES WITH KEY = "randtag"
  4. name: h2o_quality
  5. key value
  6. --- -----
  7. randtag 1
  8. randtag 2
  9. randtag 3

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode 'q=SHOW TAG VALUES WITH KEY = "randtag"'
  2. {
  3. "results": [
  4. {
  5. "statement_id": 0,
  6. "series": [
  7. {
  8. "name": "h2o_quality",
  9. "columns": [
  10. "key",
  11. "value"
  12. ],
  13. "values": [
  14. [
  15. "randtag",
  16. "1"
  17. ],
  18. [
  19. "randtag",
  20. "2"
  21. ],
  22. [
  23. "randtag",
  24. "3"
  25. ]
  26. ]
  27. }
  28. ]
  29. }
  30. ]
  31. }

Run a SHOW TAG VALUES query with several clauses

  1. > SHOW TAG VALUES ON "NOAA_water_database" WITH KEY IN ("location","randtag") WHERE "randtag" =~ /./ LIMIT 3
  2. name: h2o_quality
  3. key value
  4. --- -----
  5. location coyote_creek
  6. location santa_monica
  7. randtag 1

The query returns the tag values of the tag keys location and randtag for all measurements in the NOAA_water_database database where randtag has tag values. The LIMIT clause limits the number of tag values returned to three.

SHOW FIELD KEYS

Returns the field keys and the data type of their field values.

Syntax

  1. SHOW FIELD KEYS [ON <database_name>] [FROM <measurement_name>]

Description of syntax

ON <database_name> is optional. If the query does not include ON <database_name>, you must specify the database with USE <database_name> in the CLI or with the db query string parameter in the InfluxDB API request.

The FROM clause is also optional. See the Data Exploration page for documentation on the FROM clause.

Note: A field’s data type can differ across shards. If your field has more than one type, SHOW FIELD KEYS returns the type that occurs first in the following list: float, integer, string, boolean.

Examples

Run a SHOW FIELD KEYS query with the ON clause

  1. > SHOW FIELD KEYS ON "NOAA_water_database"
  2. name: average_temperature
  3. fieldKey fieldType
  4. -------- ---------
  5. degrees float
  6. name: h2o_feet
  7. fieldKey fieldType
  8. -------- ---------
  9. level description string
  10. water_level float
  11. name: h2o_pH
  12. fieldKey fieldType
  13. -------- ---------
  14. pH float
  15. name: h2o_quality
  16. fieldKey fieldType
  17. -------- ---------
  18. index float
  19. name: h2o_temperature
  20. fieldKey fieldType
  21. -------- ---------
  22. degrees float

The query returns the field keys and field value data types for each measurement in the NOAA_water_database database.

Run a SHOW FIELD KEYS query without the ON clause

CLI InfluxDB API

Specify the database with USE <database_name>

  1. > USE NOAA_water_database
  2. Using database NOAA_water_database
  3. > SHOW FIELD KEYS
  4. name: average_temperature
  5. fieldKey fieldType
  6. -------- ---------
  7. degrees float
  8. name: h2o_feet
  9. fieldKey fieldType
  10. -------- ---------
  11. level description string
  12. water_level float
  13. name: h2o_pH
  14. fieldKey fieldType
  15. -------- ---------
  16. pH float
  17. name: h2o_quality
  18. fieldKey fieldType
  19. -------- ---------
  20. index float
  21. name: h2o_temperature
  22. fieldKey fieldType
  23. -------- ---------
  24. degrees float

Specify the database with the db query string parameter:

  1. ~# curl -G "http://localhost:8086/query?db=NOAA_water_database&pretty=true" --data-urlencode 'q=SHOW FIELD KEYS'
  2. {
  3. "results": [
  4. {
  5. "statement_id": 0,
  6. "series": [
  7. {
  8. "name": "average_temperature",
  9. "columns": [
  10. "fieldKey",
  11. "fieldType"
  12. ],
  13. "values": [
  14. [
  15. "degrees",
  16. "float"
  17. ]
  18. ]
  19. },
  20. {
  21. "name": "h2o_feet",
  22. "columns": [
  23. "fieldKey",
  24. "fieldType"
  25. ],
  26. "values": [
  27. [
  28. "level description",
  29. "string"
  30. ],
  31. [
  32. "water_level",
  33. "float"
  34. ]
  35. ]
  36. },
  37. {
  38. "name": "h2o_pH",
  39. "columns": [
  40. "fieldKey",
  41. "fieldType"
  42. ],
  43. "values": [
  44. [
  45. "pH",
  46. "float"
  47. ]
  48. ]
  49. },
  50. {
  51. "name": "h2o_quality",
  52. "columns": [
  53. "fieldKey",
  54. "fieldType"
  55. ],
  56. "values": [
  57. [
  58. "index",
  59. "float"
  60. ]
  61. ]
  62. },
  63. {
  64. "name": "h2o_temperature",
  65. "columns": [
  66. "fieldKey",
  67. "fieldType"
  68. ],
  69. "values": [
  70. [
  71. "degrees",
  72. "float"
  73. ]
  74. ]
  75. }
  76. ]
  77. }
  78. ]
  79. }

Run a SHOW FIELD KEYS query with the FROM clause

  1. > SHOW FIELD KEYS ON "NOAA_water_database" FROM "h2o_feet"
  2. name: h2o_feet
  3. fieldKey fieldType
  4. -------- ---------
  5. level description string
  6. water_level float

The query returns the fields keys and field value data types for the h2o_feet measurement in the NOAA_water_database database.

Common Issues with SHOW FIELD KEYS

SHOW FIELD KEYS and field type discrepancies

Field value data types cannot differ within a shard but they can differ across shards. SHOW FIELD KEYS returns every data type, across every shard, associated with the field key.

Example

The all_the_types field stores four different data types:

  1. > SHOW FIELD KEYS
  2. name: mymeas
  3. fieldKey fieldType
  4. -------- ---------
  5. all_the_types integer
  6. all_the_types float
  7. all_the_types string
  8. all_the_types boolean

Note that SHOW FIELD KEYS handles field type discrepancies differently from SELECT statements. For more information, see the How does InfluxDB handle field type discrepancies across shards?.

Filter meta queries by time

When you filter meta queries by time, you may see results outside of your specified time. Meta query results are filtered at the shard level, so results can be approximately as granular as your shard group duration. If your time filter spans multiple shards, you’ll get results from all shards with points in the specified time range. To review your shards and timestamps on points in the shard, run SHOW SHARDS. To learn more about shards and their duration, see recommended shard groups durations.

The example below shows how to filter SHOW TAG KEYS by approximately one hour using a 1h shard group duration. To filter other meta data, replace SHOW TAG KEYS with SHOW TAG VALUES, SHOW SERIES, SHOW FIELD KEYS, and so on.

Note: SHOW MEASUREMENTS cannot be filtered by time.

Example filtering SHOW TAG KEYS by time

  1. Specify a shard duration on a new database or alter an existing shard duration. To specify a 1h shard duration when creating a new database, run the following command:

    1. > CREATE database mydb with duration 7d REPLICATION 1 SHARD DURATION 1h name myRP;

    Note: The minimum shard duration is 1h.

  2. Verify the shard duration has the correct time interval (precision) by running the SHOW SHARDS command. The example below shows a shard duration with an hour precision.

    1. > SHOW SHARDS
    2. name: mydb
    3. id database retention_policy shard_group start_time end_time expiry_time owners
    4. -- -------- ---------------- ----------- ---------- -------- ----------- ------
    5. > precision h
  3. (Optional) Insert sample tag keys. This step is for demonstration purposes. If you already have tag keys (or other meta data) to search for, skip this step.

    1. // Insert a sample tag called "test_key" into the "test" measurement, and then check the timestamp:
    2. > INSERT test,test_key=hello value=1
    3. > select * from test
    4. name: test
    5. time test_key value
    6. ---- -------- -----
    7. 434820 hello 1
    8. // Add new tag keys with timestamps one, two, and three hours earlier:
    9. > INSERT test,test_key_1=hello value=1 434819
    10. > INSERT test,test_key_2=hello value=1 434819
    11. > INSERT test,test_key_3_=hello value=1 434818
    12. > INSERT test,test_key_4=hello value=1 434817
    13. > INSERT test,test_key_5_=hello value=1 434817
  4. To find tag keys within a shard duration, run one of the following commands:

    SHOW TAG KEYS ON database-name <WHERE time clause> OR

    SELECT * FROM measurement <WHERE time clause>

    The examples below use test data from step 3.

    1. //Using data from Step 3, show tag keys between now and an hour ago
    2. > SHOW TAG KEYS ON mydb where time > now() -1h and time < now()
    3. name: test
    4. tagKey
    5. ------
    6. test_key
    7. test_key_1
    8. test_key_2
    9. // Find tag keys between one and two hours ago
    10. > SHOW TAG KEYS ON mydb where > time > now() -2h and time < now()-1h
    11. name: test
    12. tagKey
    13. ------
    14. test_key_1
    15. test_key_2
    16. test_key_3
    17. // Find tag keys between two and three hours ago
    18. > SHOW TAG KEYS ON mydb where > time > now() -3h and time < now()-2h
    19. name: test
    20. tagKey
    21. ------
    22. test_key_3
    23. test_key_4
    24. test_key_5
    25. // For a specified measurement, find tag keys in a given shard by specifying the time boundaries of the shard
    26. > SELECT * FROM test WHERE time >= '2019-08-09T00:00:00Z' and time < '2019-08-09T10:00:00Z'
    27. name: test
    28. time test_key_4 test_key_5 value
    29. ---- ------------ ------------ -----
    30. 434817 hello 1
    31. 434817 hello 1
    32. // For a specified database, find tag keys in a given shard by specifying the time boundaries of the shard
    33. > SHOW TAG KEYS ON mydb WHERE time >= '2019-08-09T00:00:00Z' and time < '2019-08-09T10:00:00Z'
    34. name: test
    35. tagKey
    36. ------
    37. test_key_4
    38. test_key_5