All Products
Search
Document Center

FAQ

Last Updated: Sep 16, 2020

This topic describes the frequently asked questions (FAQ) about the differences between similar concepts of TSDB for InfluxDB®. It also lists the FAQ about the differences between TSDB for InfluxDB® and other database services in terms of the running method.

Administration

  • How can I identify the version of TSDB for InfluxDB®?
  • What is the relationship between a shard group duration and a retention policy (RP)?
  • Why is no data lost after I modify a retention policy?
  • Why does TSDB for InfluxDB® fail to parse the microsecond time unit?

Command-line interface (CLI)

  • How can I enable the CLI of TSDB for InfluxDB® to return human readable timestamps?
  • How can I execute the USE statement to specify a database if I am not an administrator?
  • How can I use the CLI of TSDB for InfluxDB® to write data to a non-default retention policy?

Data types

  • Why am I unable to query the field values of the Boolean data type?
  • How does TSDB for InfluxDB® handle field data type differences across shards?
  • What are the smallest and the largest integers that TSDB for InfluxDB® can store?
  • What is the valid timestamp range in TSDB for InfluxDB®?
  • How can I view the data types of fields?
  • Can I convert the data types of fields?

Influx Query Language (InfluxQL) functions

  • How can I perform mathematical operations within functions?
  • Why does a query response use epoch 0 as a timestamp?
  • Which InfluxQL functions can be nested?

Data queries

  • How can I specify the time intervals that are returned by GROUP BY time() queries?
  • Why does a query response contain no data or partial data?
  • Why does the response of a GROUP BY time() query exclude the data whose timestamps occur after the time returned by the now() function?
  • Can I perform mathematical operations on timestamps?
  • Can I identify the time granularities for data writes based on the returned timestamps?
  • When do I use single quotation marks (‘) and double quotation marks (“) in queries?
  • Why is data lost after I create a new default retention policy (DEFAULT)?
  • Why does the response of a WHERE clause that uses the OR operator to specify multiple time ranges contain no data?
  • Why does the fill(previous) function fail to return values?
  • Why is data lost when I run SELECT INTO queries?
  • How do I query the data that has the same tag key name and the same field key name?
  • How can I query data across measurements?
  • Does the sequence of timestamp filters in query statements have a significant effect on the response time?
  • How do I specify a SELECT statement if I want to query the tags that have no values?

Series and series cardinality

  • Why is the series cardinality important?

Data writes

  • How do I write a field value of the integer data type?
  • How does TSDB for InfluxDB® process duplicate points?
  • What is the line feed that is required in HTTP API requests?
  • What are the characters and words that I must avoid if possible when I write data to TSDB for InfluxDB®?
  • When do I use single quotation marks (‘) and double quotation marks (“) to write data?
  • Does the timestamp granularity have a significant effect on system performance?

How can I identify the version of TSDB for InfluxDB®?

You can use the following methods to identify the version of your TSDB for InfluxDB®:

Run the curl path/ping command.

  1. $ curl -i 'https://<Domain name>:3242/ping?u=<Username>&p=<Password>'
  2. HTTP/1.1 204 No Content
  3. Content-Type: application/json
  4. X-Influxdb-Build: OSS
  5. X-Influxdb-Version: 1.7.x

Use the CLI of TSDB for InfluxDB®.

  1. $ influx -ssl -username <Username> -password <Password> -host <Domain name> -port 3242
  2. Connected to https://<Domain name>:3242 version 1.7.x

What is the relationship between a shard group duration and a retention policy (RP)?

TSDB for InfluxDB® stores data in shard groups. Each shard group covers a specified time interval. To view the specified time interval in TSDB for InfluxDB®, you can check the value of the DURATION element in the retention policy. The following table lists the default mappings between the time intervals of shard groups and the values of the DURATION elements in the retention policies.

Duration specified by a retention policy Time interval covered by a shard group
< 2 days 1 hour
≥ 2 days and ≤ 6 months 1 day
> 6 months 7 days

To view the shard group durations of retention policies, execute the SHOW RETENTION POLICIES statement.

Why is no data lost after I modify a retention policy?

The possible reasons are described as follows:

The most possible reason is that TSDB for InfluxDB® checks and enforces a retention policy every 30 minutes by default. If data falls in a time range that is excluded from the duration specified by the new retention policy, TSDB for InfluxDB® may delete the data upon the next check.

Another possible reason is that the changes of the DURATION and SHARD DURATION values in the retention policy may cause unexpected data retention. TSDB for InfluxDB® stores data in shard groups. Each shard group covers a specific retention policy and a time interval. If TSDB for InfluxDB® enforces a retention policy, TSDB for InfluxDB® deletes all points in the shard group, instead of individual points. TSDB for InfluxDB® cannot split shard groups.

The system is forced to save all data to a previous shard group if the following two conditions are met. The first condition is that the DURATION value of the new retention policy is smaller than the SHARD DURATION value of the previous retention policy. The second condition is that TSDB for InfluxDB® is writing data to the previous shard group that covers a longer time interval specified by the DURATION element. The system saves all data to the previous shard group even if certain points in the shard group fall out of the time interval specified by the new DURATION value. If all the points in the previous shard group fall out of the time interval specified by the new DURATION value, TSDB for InfluxDB® deletes the entire shard group. Then, the system starts to write data to a shard group that has a new and shorter time interval specified by the SHARD DURATION element. This helps you to prevent unexpected data retention.

Why does TSDB for InfluxDB® fail to parse the microsecond time unit?

The syntax for specifying the microsecond time unit varies based on scenarios, such as data writes, time granularity settings in the CLI of TSDB for InfluxDB®, and data queries. The following table lists the syntax that is supported for each scenario.

Use the HTTP API to write data Perform queries Configure time granularity settings in the CLI
u Supported Supported Supported
us Not supported Not supported Not supported
µ Not supported Supported Not supported
µs Not supported Not supported Not supported

How can I enable the CLI of TSDB for InfluxDB® to return human readable timestamps?

When you connect to the CLI for the first time, you can specify the time granularity based on RFC 3339.

  1. $ influx -ssl -username <Username> -password <Password> -host <Domain name> -port 3242 -precision rfc3339

You can also specify the time granularity after you connect to the CLI.

  1. $ influx -ssl -username <Username> -password <Password> -host <Domain name> -port 3242
  2. Connected to https://<Domain name>:3242 version 1.7.x
  3. > precision rfc3339
  4. >

For more information about the CLI, see Command-line interface.

How can I execute the USE statement to specify a database if I am not an administrator?

If you are not an administrator and want to execute the USE<database_name> statement to specify a database, you must have READ access, WRITE access, or full access to the database. If you are not an administrator and execute the USE statement to specify a database to which you have no READ access, WRITE access, or full access, the system returns this error:

  1. ERR: Database <database_name> doesn't exist. Run SHOW DATABASES for a list of existing databases.

Note: The SHOW DATABASES statement returns only the databases to which non-administrator users have READ access, WRITE access, or full access.

How can I use the CLI of TSDB for InfluxDB® to write data to a non-default retention policy?

You can use the syntax INSERT INTO [<database>]<retention_policy> <line_protocol>to write data to a non-default retention policy. This method can be used only in the CLI to specify the database and the retention policy. To write data over HTTP, you must use the db and rp parameters to specify the database and the retention policy, respectively. The rp parameter is optional.

Example

  1. > INSERT INTO one_day mortality bool=true
  2. Using retention policy one_day
  3. > SELECT * FROM "mydb"."one_day"."mortality"
  4. name: mortality
  5. ---------------
  6. time bool
  7. 2016-09-13T22:29:43.229530864Z true

If you want to query data in a non-default retention policy, you must fully qualify measurement. You can use the following syntax to fully qualify the measurement:

  1. "<database>"."<retention_policy>"."<measurement>"

Why am I unable to query the field values of the Boolean data type?

The syntax of writing Boolean values is different from that of querying Boolean values.

Syntax for Boolean values Write data Query data
t,f Supported Not supported
T,F Supported Not supported
true,false Supported Supported
True,False Supported Supported
TRUE,FALSE Supported Supported

For example, the SELECT * FROM "hamlet" WHERE "bool"=True statement returns all points where the value of bool is TRUE. The SELECT * FROM "hamlet" WHERE "bool"=T statement does not return any data.

How does TSDB for InfluxDB® handle field data type differences across shards?

The data types of field values can be INT, FLOAT, STRING, or BOOLEAN. The data types of field values must be the same in each shard, but the data types of field values can be different across shards.

SELECT

A SELECT statement returns all the field values if all the field values have the same data type. If the data types of field values are different across shards, TSDB for InfluxDB® converts the data types by using applicable type conversion operations. Then, TSDB for InfluxDB® returns all the field values based on the following data type sequence: FLOAT, INT, STRING, and BOOLEAN.

If different data types of field values are found in your data, use the <field_key>::<type> syntax to query different data types.

Examples

In the just_my_type measurement, the my_field field has four values in four shards. Each value for the my_field field has a different data type: FLOAT, INT, STRING, and BOOLEAN.

A SELECT * statement returns only the values of the FLOAT and INT data types. In the response of the statement, TSDB for InfluxDB® converts the values of the INT data type to the values of the FLOAT data type.

  1. > SELECT * FROM just_my_type
  2. name: just_my_type
  3. ------------------
  4. time my_field
  5. 2016-06-03T15:45:00Z 9.87034
  6. 2016-06-03T16:45:00Z 7

The SELECT <field_key>::<type> [...] statementreturns the values of all data types. TSDB for InfluxDB® stores the output data of each type in a separate column, and the column names are incremented, for example, my_field, my_field_1, and my_field_2. If possible, TSDB for InfluxDB® converts the data type of a field value to another data type. In the following example, TSDB for InfluxDB® converts the 7 integer to the floating-point number that is listed in the first column. TSDB for InfluxDB® converts the 9.879034 floating-point number to the integer that is listed in the second column. TSDB for InfluxDB® cannot convert a floating-point number or an integer to a string or a Boolean value.

  1. > SELECT "my_field"::float,"my_field"::integer,"my_field"::string,"my_field"::boolean FROM just_my_type
  2. name: just_my_type
  3. ------------------
  4. time my_field my_field_1 my_field_2 my_field_3
  5. 2016-06-03T15:45:00Z 9.87034 9
  6. 2016-06-03T16:45:00Z 7 7
  7. 2016-06-03T17:45:00Z a string
  8. 2016-06-03T18:45:00Z true

SHOW FIELD KEYS

The SHOW FIELD KEYS statement returns all the data types in each shard that is associated with a specified field key.

Examples

In the just_my_type measurement, the my_field field has four values in four shards. Each value for the my_field field has a different data type: FLOAT, INT, STRING, and BOOLEAN.

The SHOW FIELD KEYS statement returns all the four data types.

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

What are the smallest and the largest integers that TSDB for InfluxDB® can store?

TSDB for InfluxDB® stores integers as signed Int64 values. The valid smallest and largest Int64 values are -9023372036854775808 and 9023372036854775807, respectively. For more information, see Go built-ins.

If the stored values are close to the smallest or largest integer, unexpected results may occur. Certain functions and operators may convert Int64 values to Float64 values during computation, which can cause overflow issues.

What is the valid timestamp range in TSDB for InfluxDB®?

The valid timestamp range in TSDB for InfluxDB® is -9223372036854775806 to 9223372036854775806, or 1677-09-21T00:12:43.145224194Z to 2262-04-11T23:47:16.854775806Z. If timestamps fall out of the valid range, parsing errors occur.

How can I view the data types of fields?

You can execute the SHOW FIELD KEYS statement to view the data types of fields.

Example

  1. > SHOW FIELD KEYS FROM all_the_types
  2. name: all_the_types
  3. -------------------
  4. fieldKey fieldType
  5. blue string
  6. green boolean
  7. orange integer
  8. yellow float

Can I convert the data types of fields?

Yes, you can change the data types of fields. However, you can convert only certain data types to other data types in TSDB for InfluxDB®. You can use the <field_key>::<type> syntax to convert field values from integers to floating-point numbers or from floating-point numbers to integers. For more information about data type conversion, see Data exploration. You cannot convert floating-point numbers or integers to strings or Boolean values. Similarly, you cannot convert strings or Boolean values to floating-point numbers or integers.

You can use the following alternative methods to change data types:

Write the data to another field

The simplest method is to write the data of the new data type to another field in the same series.

Use the shard system

The data types of field values must be the same in each shard, but the data types of field values can be different across shards.

If you want to change the data type of a field value, you can execute the SHOW SHARDS statement to query the end_time value of the current shard. If the timestamps of points occur after the time specified by end_time, TSDB for InfluxDB® allows you to write data of a different data type to an existing field. For example, if the data timestamps occur before the end time of the current shard, you can write only integers to a field. However, if the data timestamps occur after the time specified by end_time, you can write floating-point numbers to the field.

This process does not change the data types of the field values in the original shard.

How can I perform mathematical operations within functions?

Currently, you cannot use TSDB for InfluxDB® to perform mathematical operations within functions. We recommend that you run subqueries as an alternative method.

Examples

InfluxQL does not support the following syntax:

  1. SELECT MEAN("dogs" - "cats") from "pet_daycare"

However, you can run the following subquery as an alternative method:

  1. > SELECT MEAN("difference") FROM (SELECT "dogs" - "cat" AS "difference" FROM "pet_daycare")

For more information about subqueries, see Data exploration.

Why does a query response use epoch 0 as a timestamp?

In most cases, epoch 0 (1970-01-01T00:00:00Z) is used as a null timestamp in TSDB for InfluxDB®. If no timestamps can be returned for your queries, TSDB for InfluxDB® returns epoch 0 as a timestamp. One of the examples is that no time ranges are specified for aggregate functions.

Which InfluxQL functions can be nested?

The following InfluxQL functions can be nested:

  • DISTINCT() nested in COUNT()
  • CUMULATIVE_SUM()
  • DERIVATIVE()
  • DIFFERENCE()
  • ELAPSED()
  • MOVING_AVERAGE()
  • NON_NEGATIVE_DERIVATIVE()
  • HOLT_WINTERS() and HOLT_WINTERS_WITH_FIT()

For more information about how to use subqueries as substitutes for nested functions, see Data exploration.

How can I specify the time intervals that are returned by GROUP BY time() queries?

You can use two methods to specify the time intervals that are returned by GROUP BY time() queries: Use the preset time buckets in TSDB for InfluxDB® and specify an offset interval.

Examples

Preset time buckets

Execute the following statement to calculate the average values of sunflowers between 18:15 and 19:45, and divide the average values into groups by hour:

  1. SELECT mean("sunflowers")
  2. FROM "flower_orders"
  3. WHERE time >= '2016-08-29T18:15:00Z' AND time <= '2016-08-29T19:45:00Z' GROUP BY time(1h)

The following query result shows how TSDB for InfluxDB® maintains its preset time buckets:

In this example, the 18:00 and 19:00 hours are preset time buckets. In the WHERE clause, the time range for the query is specified. Based on the specified time range, the data generated before 18:15 is not used to calculate the average value in the 18:00 preset time bucket. The data used to calculate the average value in the 18:00 time bucket must fall in the 18:00 hour. The same rules apply to the 19:00 preset time bucket. The data used to calculate the average value in the 19:00 preset time bucket must fall in the 19:00 hour. The dotted lines show the points that are used to calculate each average value.

Even if the first timestamp in the result is 2016-08-29T18:00:00Z, the query result for the 18:00 preset time bucket excludes the data generated before the time specified by the 2016-08-29T18:15:00Z timestamp. This timestamp specifies the start time of the query time range and is set in the WHERE clause.

Raw data:                                                                                      Result:

  1. name: flower_orders name: flower_orders
  2. --------- -------------------
  3. time sunflowers time mean
  4. 2016-08-29T18:00:00Z 34 2016-08-29T18:00:00Z 22.332
  5. |--| 2016-08-29T19:00:00Z 62.75
  6. 2016-08-29T18:15:00Z |28|
  7. 2016-08-29T18:30:00Z |19|
  8. 2016-08-29T18:45:00Z |20|
  9. |--|
  10. |--|
  11. 2016-08-29T19:00:00Z |56|
  12. 2016-08-29T19:15:00Z |76|
  13. 2016-08-29T19:30:00Z |29|
  14. 2016-08-29T19:45:00Z |90|
  15. |--|
  16. 2016-08-29T20:00:00Z 70

Offset intervals

Execute the following statement to calculate the average values of the sunflowers field between 18:15 and 19:45, and divide the average values into groups by hour. In this statement, an offset of 15 minutes is specified for the preset time buckets of TSDB for InfluxDB®:

  1. SELECT mean("sunflowers")
  2. FROM "flower_orders"
  3. WHERE time >= '2016-08-29T18:15:00Z' AND time <= '2016-08-29T19:45:00Z' GROUP BY time(1h,15m)
  4. ---
  5. |
  6. offset interval

Due to the specified offset, each preset time bucket of TSDB for InfluxDB® is forward shifted by 15 minutes. In this case, the data generated between 18:15 and 19:15 is used to calculate the average value in the 18:00 preset time bucket. The data generated between 19:15 and 20:15 is used to calculate the average value in the 19:00 preset time bucket. The dotted lines show the points that are used to calculate each average value.

Note that the first timestamp in the result is 2016-08-29T18:15:00Z instead of 2016-08-29T18:00:00Z.

Raw data:                                                                                      Result:

  1. name: flower_orders name: flower_orders
  2. --------- -------------------
  3. time sunflowers time mean
  4. 2016-08-29T18:00:00Z 34 2016-08-29T18:15:00Z 30.75
  5. |--| 2016-08-29T19:15:00Z 65
  6. 2016-08-29T18:15:00Z |28|
  7. 2016-08-29T18:30:00Z |19|
  8. 2016-08-29T18:45:00Z |20|
  9. 2016-08-29T19:00:00Z |56|
  10. |--|
  11. |--|
  12. 2016-08-29T19:15:00Z |76|
  13. 2016-08-29T19:30:00Z |29|
  14. 2016-08-29T19:45:00Z |90|
  15. 2016-08-29T20:00:00Z |70|
  16. |--|

Why does a query response contain no data or partial data?

The possible reasons vary based on the scenarios. The common reasons are described as follows:

Retention policies

The most common reason is that you query data from a non-default retention policy. By default, TSDB for InfluxDB® queries data from the default retention policy (DEFAULT) of a database. If your data is not stored in the default retention policy and the target retention policy is not specified, TSDB for InfluxDB® returns no data.

Tag keys in the SELECT statement

A SELECT statement returns data only if the statement contains at least one field key. If a SELECT statement contains only tag keys, the statement returns no data. For more information, see Data exploration.

Time ranges in query statements

Another possible reason is that the time ranges specified in query statements are invalid. By default, most SELECT statements query the data whose timestamps range from 1677-09-21 00:12:43.145224194 (UTC+0) to 2262-04-11T23:47:16.854775806Z (UTC+0). If your SELECT statements include GROUP BY time() clauses, the system returns only the points whose timestamps fall in a specific time range. By default, the start time of the time range is specified by the 1677-09-21 00:12:43.145224194 timestamp. The end time of the time range is the time returned by the now() function. By default, GROUP BY time() queries do not return the data whose timestamps occur after the time returned by the now() function. To obtain the data whose timestamps occur after the time returned by the now() function, you must specify the end time of the time range in GROUP BY time() queries.

Identifier names

Another common reason is related to schemas. In your queries, a field name is the same as a tag key name in the data. If this occurs, the field key has a higher priority as filters than the tag key in queries. In queries, you must use the ::tag syntax to specify the tag key.

Why does the response of a GROUP BY time() query exclude the data whose timestamps occur after the time returned by the now() function?

By default, most SELECT statements query the data whose timestamps range from 1677-09-21 00:12:43.145224194 (UTC+0) to 2262-04-11T23:47:16.854775806Z (UTC+0). If your SELECT statements include GROUP BY time() clauses, the system returns only the points whose timestamps fall in a specific time range. By default, the start time of the time range is specified by the 1677-09-21 00:12:43.145224194 timestamp. The end time of the time range is the time returned by the now() function.

To obtain the data whose timestamps occur after the time returned by the now() function, specify the end time of the time range in the WHERE clauses of the SELECT statements. The prerequisite is that the SELECT statements include GROUP BY time() clauses.

In the following examples, the first query covers the data whose timestamps fall in the time range specified by the 2015-09-18T21:30:00Z timestamp and the now() function. The second query covers the data whose timestamps fall in the time range specified by the 2015-09-18T21:30:00Z timestamp and the now() + 180w expression. This expression indicates the 180 weeks that follows the time returned by the now() function.

  1. > SELECT MEAN("boards") FROM "hillvalley" WHERE time >= '2015-09-18T21:30:00Z' GROUP BY time(12m) fill(none)
  2. > SELECT MEAN("boards") FROM "hillvalley" WHERE time >= '2015-09-18T21:30:00Z' AND time <= now() + 180w GROUP BY time(12m) fill(none)

Note that you must specify the end time of the query time range in the WHERE clauses to override the end time specified by the now() function. In the following query statement, the start time of the query time range is set to the time that is returned by the now() function. Therefore, you can execute the statement to query the data whose timestamps indicate the time specified by the now() function.

  1. > SELECT MEAN("boards") FROM "hillvalley" WHERE time >= now() GROUP BY time(12m) fill(none)
  2. >

For more information about the time syntax, see Data exploration.

Can I perform mathematical operations on timestamps?

No, you cannot perform mathematical operations on timestamps in TSDB for InfluxDB®. Time computing must be performed by the clients that receive the query results.

TSDB for InfluxDB® provides limited support for using InfluxQL functions on timestamps. The ELAPSED() function returns the difference between timestamps for a single field.

Can I identify the time granularities for data writes based on the returned timestamps?

No, you cannot identify the time granularities for data writes based on the returned timestamps. Regardless of the provided time granularity for data writes, TSDB for InfluxDB® stores all timestamps as nanosecond values. Note that when query results are returned, the database deletes zeros from the end of the timestamps without sending notifications. Therefore, the time granularity for data writes cannot be identified based on the returned timestamps.

In the following example, the precision_supplied tag indicates the time granularities that the user provided when the user wrote data. The timestamp_supplied tag indicates the timestamps that the user provided when the user wrote data. TSDB for InfluxDB® deleted zeros from the end of the returned timestamps. Therefore, the time granularities for data writes cannot be identified based on the returned timestamps.

  1. name: trails
  2. -------------
  3. time value precision_supplied timestamp_supplied
  4. 1970-01-01T01:00:00Z 3 n 3600000000000
  5. 1970-01-01T01:00:00Z 5 h 1
  6. 1970-01-01T02:00:00Z 4 n 7200000000000
  7. 1970-01-01T02:00:00Z 6 h 2

When do I use single quotation marks (‘) and double quotation marks (“) in queries?

Use single quotation marks (‘) to enclose string values, such as tag values. You cannot use single quotation marks (‘) to enclose identifiers, such as database names, retention policy names, usernames, measurement names, tag keys, and field keys.

Use double quotation marks (“) to enclose identifiers if the identifiers start with a digit, contain characters that are not letters, digits, or underscores (_), or are InfluxQL keywords. In other scenarios, you do not need to use double quotation marks (“) to enclose identifiers. However, we recommend that you use double quotation marks (“) to enclose identifiers in all scenarios.

Examples

Valid query: SELECT bikes_available FROM bikes WHERE station_id='9'

Valid query: SELECT "bikes_available" FROM "bikes" WHERE "station_id"='9'

Valid query: SELECT MIN("avgrq-sz") AS "min_avgrq-sz" FROM telegraf

Valid query: SELECT * from "cr@zy" where "p^e"='2'

Invalid query: SELECT 'bikes_available' FROM 'bikes' WHERE 'station_id'="9"

Invalid query: SELECT * from cr@zy where p^e='2'

Use single quotation marks (‘) to enclose date and time strings. If you use double quotation marks (“) to enclose date and time strings, TSDB for InfluxDB® returns the following error: ERR: invalid operation: time and *influxql.VarRef are not compatible.

Examples

Valid query: SELECT "water_level" FROM "h2o_feet" WHERE time > '2015-08-18T23:00:01.232000000Z' AND time < '2015-09-19'

Invalid query: SELECT "water_level" FROM "h2o_feet" WHERE time > "2015-08-18T23:00:01.232000000Z" AND time < "2015-09-19"

For more information about the time syntax, see Data exploration.

Why is data lost after I create a new default retention policy (DEFAULT)?

The reason is described as follows. After you create a new default retention policy in a database, the data that was written to the previous default retention policy remains in the previous default retention policy. If no retention policy is specified for a query, the system queries data from the new default retention policy by default. In this case, the data that was written to the previous default retention policy cannot be returned. To query the data that was written to the previous default retention policy, you must fully qualify the relevant data in the query statement.

Examples

All data in the fleeting measurement belongs to the one_hour default retention policy.

  1. > SELECT count(flounders) FROM fleeting
  2. name: fleeting
  3. --------------
  4. time count
  5. 1970-01-01T00:00:00Z 8

Create a new default retention policy named two_hour, and run the same query.

  1. > SELECT count(flounders) FROM fleeting
  2. >

To query the data that was written to the previous default retention policy, specify the previous default retention policy by fully qualifying the fleeting measurement.

  1. > SELECT count(flounders) FROM fish.one_hour.fleeting
  2. name: fleeting
  3. --------------
  4. time count
  5. 1970-01-01T00:00:00Z 8

Why does the response of a WHERE clause that uses the OR operator to specify multiple time ranges contain no data?

This is because TSDB for InfluxDB® does not support the use of the OR operator in the WHERE clause to specify multiple time ranges. If the OR operator is used in the WHERE clause to specify multiple time ranges, TSDB for InfluxDB® returns no data.

Example

  1. > SELECT * FROM "absolutismus" WHERE time = '2016-07-31T20:07:00Z' OR time = '2016-07-31T23:07:17Z'
  2. >

Why does the fill(previous) function fail to return values?

This is because the previous value falls out of the specified query time range. If this occurs, the fill(previous) function does not use the previous value to fill the missing value in the query time range.

In the following example, TSDB for InfluxDB® does not use the value in the time range between 2016-07-12T16:50:00Z and 2016-07-12T16:50:10Z to fill the missing value in the time range between 2016-07-12T16:50:20Z and 2016-07-12T16:50:30Z. This is because the former time range is excluded from the query time range

Raw data:

  1. > SELECT * FROM "cupcakes"
  2. name: cupcakes
  3. --------------
  4. time chocolate
  5. 2016-07-12T16:50:00Z 3
  6. 2016-07-12T16:50:10Z 2
  7. 2016-07-12T16:50:40Z 12
  8. 2016-07-12T16:50:50Z 11

GROUP BY time() query:

  1. > SELECT max("chocolate") FROM "cupcakes" WHERE time >= '2016-07-12T16:50:20Z' AND time <= '2016-07-12T16:51:10Z' GROUP BY time(20s) fill(previous)
  2. name: cupcakes
  3. --------------
  4. time max
  5. 2016-07-12T16:50:20Z
  6. 2016-07-12T16:50:40Z 12
  7. 2016-07-12T16:51:00Z 12

Why is data lost when I run SELECT INTO queries?

The reason is described as follows. By default, SELECT INTO queries convert tags in the raw data into fields in the newly written data. As a result, TSDB for InfluxDB® overwrites the points that are differentiated by tags. You can add GROUP BY * clauses to SELECT INTO query statements to retain tags in the newly written data.

This method does not apply to the queries that use TOP() or BOTTOM() functions. For more information about TOP() and BOTTOM() functions, see InfluxQL functions.

Examples

Raw data

The french_bulldogs measurement includes the color tag and the name field.

  1. > SELECT * FROM "french_bulldogs"
  2. name: french_bulldogs
  3. ---------------------
  4. time color name
  5. 2016-05-25T00:05:00Z peach nugget
  6. 2016-05-25T00:05:00Z grey rumple
  7. 2016-05-25T00:10:00Z black prince

SELECT INTO statement that excludes the GROUP BY * clause

The SELECT INTO statement that excludes the GROUP BY * clause converts the color tag into a field in the newly written data. In the raw data, the nugget and rumple points are differentiated only by the color tag. If the color tag is converted into a field, TSDB for InfluxDB® considers that nugget and rumple are duplicate points. Therefore, TSDB for InfluxDB® overwrites the nugget point with the rumple point.

  1. > SELECT * INTO "all_dogs" FROM "french_bulldogs"
  2. name: result
  3. ------------
  4. time written
  5. 1970-01-01T00:00:00Z 3
  6. > SELECT * FROM "all_dogs"
  7. name: all_dogs
  8. --------------
  9. time color name
  10. 2016-05-25T00:05:00Z grey rumple <---- no more nugget
  11. 2016-05-25T00:10:00Z black prince

SELECT INTO statement that includes the GROUP BY * clause

The SELECT INTO statement that includes the GROUP BY * clause retains the color tag in the newly written data. In this case, the nugget and rumple points remain as different points and TSDB for InfluxDB® does not overwrite points.

  1. > SELECT "name" INTO "all_dogs" FROM "french_bulldogs" GROUP BY *
  2. name: result
  3. ------------
  4. time written
  5. 1970-01-01T00:00:00Z 3
  6. > SELECT * FROM "all_dogs"
  7. name: all_dogs
  8. --------------
  9. time color name
  10. 2016-05-25T00:05:00Z peach nugget
  11. 2016-05-25T00:05:00Z grey rumple
  12. 2016-05-25T00:10:00Z black prince

How do I query the data that has the same tag key name and the same field key name?

Use the:: syntax to specify whether a key is a field key or a tag key.

Examples

Sample data:

  1. > INSERT candied,almonds=true almonds=50,half_almonds=51 1465317610000000000
  2. > INSERT candied,almonds=true almonds=55,half_almonds=56 1465317620000000000
  3. > SELECT * FROM "candied"
  4. name: candied
  5. -------------
  6. time almonds almonds_1 half_almonds
  7. 2016-06-07T16:40:10Z 50 true 51
  8. 2016-06-07T16:40:20Z 55 true 56

As a field key:

  1. > SELECT * FROM "candied" WHERE "almonds"::field > 51
  2. name: candied
  3. -------------
  4. time almonds almonds_1 half_almonds
  5. 2016-06-07T16:40:20Z 55 true 56

As a tag key:

  1. > SELECT * FROM "candied" WHERE "almonds"::tag='true'
  2. name: candied
  3. -------------
  4. time almonds almonds_1 half_almonds
  5. 2016-06-07T16:40:10Z 50 true 51
  6. 2016-06-07T16:40:20Z 55 true 56

How can I query data across measurements?

Currently, you cannot perform mathematical operations or group data across measurements. You can only query data that belongs to the same measurement. TSDB for InfluxDB® is not a relational database. Therefore, we recommend that you do not use cross-measurement data mapping for schemas.

Does the sequence of timestamp filters in query statements have a significant effect on the response time?

No, the sequence of timestamp filters in query statements does not have a significant effect on the response time. The test result shows that the response time of TSDB for InfluxDB® for the first query is nearly the same as that for the second query.

  1. SELECT ... FROM ... WHERE time > 'timestamp1' AND time < 'timestamp2'
  2. SELECT ... FROM ... WHERE time < 'timestamp2' AND time > 'timestamp1'

How do I specify a SELECT statement if I want to query the tags that have no values?

Use '' to specify an empty tag value in the SELECT statement. Example

  1. > SELECT * FROM "vases" WHERE priceless=''
  2. name: vases
  3. -----------
  4. time origin priceless
  5. 2016-07-20T18:42:00Z 8

Why is the series cardinality important?

The reason is described as follows. TSDB for InfluxDB® maintains an in-memory index for each series in the system. The random-access memory (RAM) usage grows if the number of series increases. If the series cardinality is excessively high, the operating system terminates the TSDB for InfluxDB® process and throws an out of memory (OOM) exception. For more information about InfluxQL commands for series cardinality, see InfluxQL reference.

How do I write a field value of the integer data type?

To write a field value of the integer data type, add i to the end of the field value. If you do not add i, TSDB for InfluxDB® processes the value as a floating-point number.

Write an integer: value=100i
Write a floating-point number: value=100

How does TSDB for InfluxDB® process duplicate points?

A point is uniquely identified by a measurement name, a tag set, and a timestamp. If two points have the same measurement name, tag set, and timestamp, they are considered as duplicate points. If you submit a duplicate point that has a different field set from an existing point, the field set of the point becomes the sum of the previous and new field sets. If a conflict occurs, the new field set prevails. This is the expected result.

Examples

Previous point: cpu_load,hostname=server02,az=us_west val_1=24.5,val_2=7 1234567890000000

New point: cpu_load,hostname=server02,az=us_west val_1=5.24 1234567890000000

After you submit a new point, TSDB for InfluxDB® uses the new val_1 value to overwrite the previous val_1 value. The value of val_2 is retained in the field set of the point.

  1. > SELECT * FROM "cpu_load" WHERE time = 1234567890000000
  2. name: cpu_load
  3. --------------
  4. time az hostname val_1 val_2
  5. 1970-01-15T06:56:07.89Z us_west server02 5.24 7

To store the previous and new points, you can use the following methods:

  • Introduce a new tag to ensure uniqueness.

    Previous point: cpu_load,hostname=server02,az=us_west,uniq=1 val_1=24.5,val_2=7 1234567890000000

    New point: cpu_load,hostname=server02,az=us_west,uniq=2 val_1=5.24 1234567890000000

    After you write the new point to TSDB for InfluxDB®, the following result appears:

  1. > SELECT * FROM "cpu_load" WHERE time = 1234567890000000
  2. name: cpu_load
  3. --------------
  4. time az hostname uniq val_1 val_2
  5. 1970-01-15T06:56:07.89Z us_west server02 1 24.5 7
  6. 1970-01-15T06:56:07.89Z us_west server02 2 5.24
  • Add a nanosecond to the timestamp of the new point.

    Previous point: cpu_load,hostname=server02,az=us_west val_1=24.5,val_2=7 1234567890000000

    New point: cpu_load,hostname=server02,az=us_west val_1=5.24 1234567890000001

    After you write the new point to TSDB for InfluxDB®, the following result appears:

  1. > SELECT * FROM "cpu_load" WHERE time >= 1234567890000000 and time <= 1234567890000001
  2. name: cpu_load
  3. --------------
  4. time az hostname val_1 val_2
  5. 1970-01-15T06:56:07.89Z us_west server02 24.5 7
  6. 1970-01-15T06:56:07.890000001Z us_west server02 5.24

What is the line feed that is required in HTTP API requests?

In the line protocol of TSDB for InfluxDB®, the \n line feed is used to indicate the end of a line and the beginning of a new line. The ASCII code of the line feed is 0x0A. If other line feeds (not \n) are used in files or data, the following errors occur: bad timestamp and unable to parse.

Note that Windows uses the \r\n line feed or the carriage return to indicate the end of a line and the beginning of a new line.

What are the characters and words that I must avoid if possible when I write data to TSDB for InfluxDB®?

InfluxQL keywords

If you use InfluxQL keywords as identifiers, you must enclose the keywords in double quotation marks (“) in each query. Otherwise, errors occur. Identifiers can be continuous query names, database names, field keys, measurement names, retention policy names, tag keys, or usernames.

Time

The time keyword is a special case. You can use time as a continuous query name, a database name, a measurement name, a retention policy name, or a username. In these cases, you do not need to use double quotation marks (“) to enclose time in queries. However, you cannot use time as a field key or a tag key. TSDB for InfluxDB® rejects data writes where time is a field key or a tag key, and reports an error.

Examples

Use time as a measurement name to write data and query the measurement

  1. > INSERT time value=1
  2. > SELECT * FROM time
  3. name: time
  4. time value
  5. ---- -----
  6. 2017-02-07T18:28:27.349785384Z 1

In TSDB for InfluxDB®, time is a valid measurement name.

Use time as a field key to write data and attempt to query the field key

  1. > INSERT mymeas time=1
  2. ERR: {"error":"partial write: invalid field name: input field \"time\" on measurement \"mymeas\" is invalid dropped=1"}

In TSDB for InfluxDB®, time is an invalid field key. The system fails to write the point and returns the 400 error code.

Use time as a tag key to write data and attempt to query the tag key

  1. > INSERT mymeas,time=1 value=1
  2. ERR: {"error":"partial write: invalid tag key: input tag \"time\" on measurement \"mymeas\" is invalid dropped=1"}

In TSDB for InfluxDB®, time is an invalid tag key. The system fails to write the point and returns the 400 error code.

Characters

To keep regular expressions and quoting simple, we recommend that you do not use the following characters in identifiers:
Backslashes (\)
Carets (^)
Dollar signs ($)
Single quotation marks (')
Double quotation marks (")
Equal signs (=)
Commas (,)

When do I use single quotation marks (‘) and double quotation marks (“) to write data?

  • If you write data based on the line protocol, do not use single quotation marks (‘) or double quotation marks (“) to enclose identifiers. In the following examples, queries are more complicated after quotation marks are used. Identifiers can be continuous query names, database names, field keys, measurement names, retention policy names, subscription names, tag keys, or usernames.

    Write a measurement that is enclosed with double quotation marks (“): INSERT "bikes" bikes_available=3
    Valid query: SELECT * FROM "\"bikes\""

    Write a measurement that is enclosed with single quotation marks (‘’): INSERT 'bikes' bikes_available=3
    Valid query: SELECT * FROM "\'bikes\'"

    Write a measurement that is not enclosed by quotation marks: INSERT bikes bikes_available=3
    Valid query: SELECT * FROM "bikes"

  • Use double quotation marks (“) to enclose field values of the string data type.

    Write data: INSERT bikes happiness="level 2"
    Valid query: SELECT * FROM "bikes" WHERE "happiness"='level 2'

  • Do not use quotation marks to enclose special characters. Use backslash () escape characters to format special characters.

    Write data: INSERT wacky va\"ue=4
    Valid query: SELECT "va\"ue" FROM "wacky"

For more information, see Line protocol reference.

Does the timestamp granularity have a significant effect on system performance?

Yes, the timestamp granularity has a significant effect on system performance. To maximize system performance, we recommend that you use a coarse-grained time granularity to write data to TSDB for InfluxDB®.

In the following two examples, the default time granularity of nanoseconds is used for the first request. The time granularity of seconds is used for the second request:

  1. curl -i -XPOST "https://<Domain name>:3242/write?db=weather&u=<Username>&p=<Password>" --data-binary 'temperature,location=1 value=90 1472666050000000000'
  2. curl -i -XPOST "https://<Domain name>:3242/write?db=weather&precision=s&u=<Username>&p=<Password>" --data-binary 'temperature,location=1 value=90 1472666050'

However, if you use a more coarse-grained time granularity to write data, duplicate points that have the same timestamp are more likely to occur. In this case, certain points may be overwritten.


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