FAQ - Time Series Database - Alibaba Cloud Documentation Center

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 is TSDB for InfluxDB® unable 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 type

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 timestamps that TSDB for InfluxDB® can store?
What are the smallest and the largest integers that TSDB for InfluxDB® can store?
How can I view the data types of fields?
Can I convert the data types of fields?

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?

Query data

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 that is 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 default retention policy (DEFAULT)?
Why does the response of a WHERE OR 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 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?

Write data

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 are not recommended 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.

$ curl -i 'https://<Network address>:3242/ping?u=<Account name>&p=<Password>'
HTTP/1.1204NoContent
Content-Type: application/json
X-Influxdb-Build: OSS
X-Influxdb-Version:1.7.x

Use the CLI of TSDB for InfluxDB®.

$ influx -ssl -username <Account name>-password <Password>-host <Network address>-port 3242

Connected to https://<Network address>: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 relationships 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 following causes may exist:

By default, TSDB for InfluxDB® checks and enforces a retention policy every 30 minutes. TSDB for InfluxDB® may delete the data upon the next check. This applies if the data is obtained within a time range that is excluded from the duration. In this case, the duration is specified by the new retention policy.

Another potential 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 includes a specific retention policy and a time interval. If TSDB for InfluxDB® enforces a retention policy, TSDB for InfluxDB® deletes all the points in the shard group. In this case, individual points are not deleted. 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 specifies that the DURATION value of the new retention policy is less than the SHARD DURATION value of the previous retention policy. The second condition specifies 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 some 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 allows you to prevent against unexpected data retention.

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

The syntax to specify the microsecond time unit varies based on scenarios. These scenarios include 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	All queries	Configure time granularity settings in the CLI
u	√	√	√
us	❌	❌	❌
µ	❌	√	❌
µs	❌	❌	❌

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

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

$ influx -ssl -username <Account name>-password <Password>-host <Network address>-port 3242-precision rfc3339

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

$ influx -ssl -username <Account name>-password <Password>-host <Network address>-port 3242
Connected to https://<Network address>:3242 version 1.7.x
> precision rfc3339
>

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

If you are not an administrator, you can execute the USE <database_name> statement to specify a database. However, in this case, you must have the READ, WRITE, or full access to the database. If you are not an administrator, make sure that you have the READ, WRITE, or full access to a database before you execute the USE statement to specify the database. Otherwise, the system returns the following error:

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

Note

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?

Execute the INSERT INTO [<database>.]<retention_policy> <line_protocol> statement 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 through HTTP, you must use the db and rp parameters to specify the database and the retention policy, respectively. The rp parameter is optional.

Example:

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

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

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

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

The syntax to write BOOLEAN values is different from the syntax to query BOOLEAN values.

Syntax for BOOLEAN values	Write	Query
`t`,`f`	√	❌
`T`,`F`	√	❌
`true`,`false`	√	√
`True`,`False`	√	√
`TRUE`,`FALSE`	√	√

For example, the SELECT * FROM "hamlet" WHERE "bool"=True statement returns all points where the value of bool is TRUE. However, the SELECT * FROM "hamlet" WHERE "bool"=T statement returns no result.

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

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

SELECT statement

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® performs the required operations to convert data types (if applicable). 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. The data types of these values for the my_field field are different from each other. These data types include FLOAT, INT, STRING, and BOOLEAN.

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

> SELECT * FROM just_my_type

name: just_my_type
------------------
time                      my_field
2016-06-03T15:45:00Z9.87034
2016-06-03T16:45:00Z7

SELECT <field_key>::<type> [...]The statement returns 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. TSDB for InfluxDB® can be used to convert the data type of a field value into another data type. However, this is based on specific requirements. In the following example, TSDB for InfluxDB® converts the 7 integer to the floating-point number that is included in the first column. TSDB for InfluxDB® converts the 9.879034 floating-point number into the integer that is included in the second column. TSDB for InfluxDB® cannot convert a floating-point number or an integer into a string or a Boolean value.

> SELECT "my_field"::float,"my_field"::integer,"my_field"::string,"my_field"::boolean FROM just_my_type

name: just_my_type
------------------
time                   my_field  my_field_1  my_field_2  my_field_3
2016-06-03T15:45:00Z9.870349
2016-06-03T16:45:00Z77
2016-06-03T17:45:00Z                         a string
2016-06-03T18:45:00Z                                     true

SHOW FIELD KEYS statement

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

Examples

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

> SHOW FIELD KEYS

name: just_my_type
fieldKey   fieldType
-----------------
my_field   float
my_field   string
my_field   integer
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 smallest valid Int64 value is -9023372036854775808. The largest valid Int64 value is 9023372036854775807. For more information, see Go builtins.

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

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

The minimum timestamp is -9223372036854775806 or 1677-09-21T00:12:43.145224194Z. The maximum timestamp is 9223372036854775806 or 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.

Examples

> SHOW FIELD KEYS FROM all_the_types
name: all_the_types
-------------------
fieldKey  fieldType
blue      string
green     boolean
orange    integer
yellow    float

Can I convert the data types of fields?

Yes, you can change the data types of fields. However, you can convert only a limited number of data types into other data types through TSDB for InfluxDB®. You can use the <field_key>::<type> syntax to convert field values from integers into floating-point numbers. You can also convert field values from floating-point numbers into integers. For more information about data type conversion, see Data exploration. You cannot convert floating-point numbers or integers into strings or Boolean values. In similar cases, you cannot convert strings or Boolean values into 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. However, 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. TSDB for InfluxDB® can be used to write data of a different data type to an existing field. This applies if the timestamps of points occur after the time that is specified by end_time. 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?

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

Examples

IInfluxQL does not support the following syntax:

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

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

> 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. For example, this rule applies when no time ranges are specified for aggregate functions.

Which InfluxQL functions can be nested?

The following InfluxQL functions can be nested:

COUNT() nested in DISTINCT()
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 the sunflowers between 18:15 and 19:45, and group the average values by hour:

SELECT mean("sunflowers")
FROM "flower_orders"
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 that is 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 hour that starts from 18:00. 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 hour that starts from 19:00. The dotted lines show the points that are used to calculate each average value.

The first timestamp in the result is 2016-08-29T18:00:00Z. However, the query result for the 18:00 preset time bucket excludes the data that is 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: Results:

name: flower_orders                                name: flower_orders
------------------------------------
time                    sunflowers                 time                  mean
2016-08-29T18:00:00Z342016-08-29T18:00:00Z22.332
|--|2016-08-29T19:00:00Z62.75
2016-08-29T18:15:00Z|28|
2016-08-29T18:30:00Z|19|
2016-08-29T18:45:00Z|20|
|--|
|--|
2016-08-29T19:00:00Z|56|
2016-08-29T19:15:00Z|76|
2016-08-29T19:30:00Z|29|
2016-08-29T19:45:00Z|90|
|--|
2016-08-29T20:00:00Z70

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®:

SELECT mean("sunflowers")
FROM "flower_orders"
WHERE time >='2016-08-29T18:15:00Z' AND time <='2016-08-29T19:45:00Z' GROUP BY time(1h,15m)
---
|
                                                                                  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 that is generated between 18:15 and 19:15 is used to calculate the average value in the 18:00 preset time bucket. The data that is 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: The first timestamp in the result is 2016-08-29T18:15:00Z rather than 2016-08-29T18:00:00Z.

Raw data: Results:

name: flower_orders                                name: flower_orders
------------------------------------
time                    sunflowers                 time                  mean
2016-08-29T18:00:00Z342016-08-29T18:15:00Z30.75
|--|2016-08-29T19:15:00Z65
2016-08-29T18:15:00Z|28|
2016-08-29T18:30:00Z|19|
2016-08-29T18:45:00Z|20|
2016-08-29T19:00:00Z|56|
|--|
|--|
2016-08-29T19:15:00Z|76|
2016-08-29T19:30:00Z|29|
2016-08-29T19:45:00Z|90|
2016-08-29T20:00:00Z|70|
|--|

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

The potential causes vary based on the scenarios. The following causes are the most common ones:

Retention policy

The first one is related to retention policies. TSDB for InfluxDB® automatically 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 a 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 empty results. For more information, see Data exploration.

Query records by time range

Another potential cause is related to time ranges. By default, most SELECT statements query the data whose timestamps range from1677-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 specified 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 returned by the now() function. By default, GROUP BY time()queries do not return the data whose timestamps occur after the time that is returned by the now() function. To obtain the data whose timestamps occur after the time that is returned by the now() function, you must specify the end time of the time range in GROUP BY time() queries.

Identifier names

The last potential cause 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 that is returned by the `now()` function?

By default, most SELECT statements retrieve the data whose timestamps range from 1677-09-21 00:12:43.145224194 UTC to 2262-04-11T23:47:16.854775806Z UTC. 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 returned by the now() function.

To retrieve the data whose timestamps are later than the time specified by the now() function, specify the end time of the time range in each GROUP BY time() clause of the SELECT statements. The prerequisite is that the SELECT statements include InfluxQL functions and WHERE clauses.

In the following examples, the first query covers the data whose timestamps fall in the time range that is 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 that is specified by the 2015-09-18T21:30:00Z timestamp and the now() expression. This expression indicates the next 180 weeks after the time that is returned by the now() function.

> SELECT MEAN("boards") FROM "hillvalley" WHERE time >='2015-09-18T21:30:00Z' GROUP BY time(12m) fill(none)


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

Note: You must specify the end time of the specified time range in the WHERE clause to override the default end time that is 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.

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

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 which time granularity for data writes is specified, TSDB for InfluxDB® stores all timestamps as nanosecond values. Note: 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.

name: trails
-------------
time                  value  precision_supplied  timestamp_supplied
1970-01-01T01:00:00Z3      n                   3600000000000
1970-01-01T01:00:00Z5      h                   1
1970-01-01T02:00:00Z4      n                   7200000000000
1970-01-01T02:00:00Z6      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 and contain characters that exclude letters, digits, underscores (_), or InfluxQL keywords. In other cases, 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 cases.

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 default retention policy (`DEFAULT`)?

After you create a default retention policy in a database, the data that is written to the previous default retention policy remains in the previous default retention policy. By default, if no retention policy is specified for a query, the system queries data from the new retention policy. In this case, the data that is written to the previous default retention policy cannot be returned. To query the data that is written to the previous default retention policy, you must fully qualify the related data in the query statement.

Example:

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

> SELECT count(flounders) FROM fleeting
name: fleeting
--------------
time                     count
1970-01-01T00:00:00Z8

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

> SELECT count(flounders) FROM fleeting
>

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

> SELECT count(flounders) FROM fish.one_hour.fleeting
name: fleeting
--------------
time                     count
1970-01-01T00:00:00Z8

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

TSDB for InfluxDB® does not allow you to use 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 results.

Example:

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

Why does the `fill(previous)` function return null values?

The previous value may fall out of the specified query time range. In this case, 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 occurs because the former time range is excluded from the query time range.

Raw data:

> SELECT * FROM "cupcakes"
name: cupcakes
--------------
time                   chocolate
2016-07-12T16:50:00Z3
2016-07-12T16:50:10Z2
2016-07-12T16:50:40Z12
2016-07-12T16:50:50Z11

GROUP BY time() query:

> 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)
name: cupcakes
--------------
time                   max
2016-07-12T16:50:20Z
2016-07-12T16:50:40Z12
2016-07-12T16:51:00Z12

Why is data lost when I run `SELECT INTO` queries?

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 using tags. You can add GROUP BY * clauses to SELECT INTO statements to retain tags in the newly written data.

This method does not apply to the queries that use TOP() or BOTTOM() functions.

Examples

Raw data

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

> SELECT * FROM "french_bulldogs"
name: french_bulldogs
---------------------
time                  color  name
2016-05-25T00:05:00Z  peach  nugget
2016-05-25T00:05:00Z  grey   rumple
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 using the color tag. If the color tag is converted into a field, TSDB for InfluxDB® considers that the nugget and rumple points are duplicate. Therefore, TSDB for InfluxDB® overwrites the rumple point with the nugget point.

> SELECT * INTO "all_dogs" FROM "french_bulldogs"
name: result
------------
time                  written
1970-01-01T00:00:00Z3

> SELECT * FROM "all_dogs"
name: all_dogs
--------------
time                  color  name
2016-05-25T00:05:00Z  grey   rumple                <---- no more nugget
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 are different from each other and TSDB for InfluxDB® does not overwrite points.

> SELECT "name" INTO "all_dogs" FROM "french_bulldogs" GROUP BY *
name: result
------------
time                  written
1970-01-01T00:00:00Z3

> SELECT * FROM "all_dogs"
name: all_dogs
--------------
time                  color  name
2016-05-25T00:05:00Z  peach  nugget
2016-05-25T00:05:00Z  grey   rumple
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:

> INSERT candied,almonds=true almonds=50,half_almonds=511465317610000000000
> INSERT candied,almonds=true almonds=55,half_almonds=561465317620000000000

> SELECT * FROM "candied"
name: candied
-------------
time                   almonds  almonds_1  half_almonds
2016-06-07T16:40:10Z50       true       51
2016-06-07T16:40:20Z55       true       56

As a field key:

> SELECT * FROM "candied" WHERE "almonds"::field >51
name: candied
-------------
time                   almonds  almonds_1  half_almonds
2016-06-07T16:40:20Z55       true       56

As a tag key:

> SELECT * FROM "candied" WHERE "almonds"::tag='true'
name: candied
-------------
time                   almonds  almonds_1  half_almonds
2016-06-07T16:40:10Z50       true       51
2016-06-07T16:40:20Z55       true       56

How can I query data across measurements?

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 similar to the response time for the second query.

SELECT ... FROM ... WHERE time >'timestamp1' AND time <'timestamp2'
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:

> SELECT * FROM "vases" WHERE priceless=''
name: vases
-----------
time                   origin   priceless
2016-07-20T18:42:00Z8

Why is the series cardinality important?

TSDB for InfluxDB® maintains an in-memory index for each series in the system. The random-access memory (RAM) usage increases 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.

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 takes precedence. This is the expected result.

Example:

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 value val_1. The value val_2 is retained in the field set of the point.

> SELECT * FROM "cpu_load" WHERE time =1234567890000000
name: cpu_load
--------------
time                      az        hostname   val_1   val_2
1970-01-15T06:56:07.89Z   us_west   server02   5.247

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:

> SELECT * FROM "cpu_load" WHERE time =1234567890000000
name: cpu_load
--------------
time                      az        hostname   uniq   val_1   val_2
1970-01-15T06:56:07.89Z   us_west   server02   124.57
1970-01-15T06:56:07.89Z   us_west   server02   25.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:

> SELECT * FROM "cpu_load" WHERE time >=1234567890000000 and time <=1234567890000001
name: cpu_load
--------------
time                             az        hostname   val_1   val_2
1970-01-15T06:56:07.89Z          us_west   server02   24.57
1970-01-15T06:56:07.890000001Z   us_west   server02   5.24

Which line feed is required by 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 start of a new line. The ASCII code of the line feed is 0x0A. If other line feeds rather than \n are used in files or data, the following errors occur: bad timestamp and unable to parse.

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

Which characters and words do I need to avoid 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 your queries. Otherwise, errors may occur. Identifiers can be continuous query names, database names, field keys, measurement names, retention policy names, subscription names, tag keys, or usernames.

Time

The preceding rule does not apply to the time keyword. You can use the time keyword 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 the time keyword. However, you cannot use the time keyword as a field key or a tag key. TSDB for InfluxDB® rejects data writes where the time keyword is a field key or a tag key, and reports an error.

Example

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

> INSERT time value=1

> SELECT * FROM time

name: time
time                            value
---------
2017-02-07T18:28:27.349785384Z1

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

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

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

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

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

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

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

Characters

To keep the use of simple regular expressions and quotation marks, we recommend that you do not use the following characters in identifiers: backslashes (\), carets (^), dollar signs ($), single quotation marks ('), double quotation marks ("), equal signs (=), and 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 in double quotation marks ("): INSERT "bikes" bikes_available=3. Applicable query: SELECT * FROM "\"bikes\"".
Write a measurement that is enclosed in single quotation marks (''): INSERT 'bikes' bikes_available=3. Applicable query: SELECT * FROM "\'bikes\'".
Write a measurement that is not enclosed in quotation marks: INSERT bikes bikes_available=3. Applicable query: SELECT * FROM "bikes".
You must use double quotation marks (") to enclose string field values.
Write data: INSERT bikes happiness="level 2". Applicable 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. Applicable query: SELECT "va\"ue" FROM "wacky".

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 (in nanoseconds) is specified for the first request. The time granularity (in seconds) is specified for the second request:

curl -i -XPOST "https://<Network address>:3242/write?db=weather&u=<Account name>&p=<Password>"--data-binary 'temperature,location=1 value=90 1472666050000000000'

curl -i -XPOST "https://<Network address>:3242/write?db=weather&precision=s&u=<Account name>&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 prone to occur. In this case, some points may be overwritten.

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

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?

How can I enable the CLI of TSDB for InfluxDB® to return a user-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?

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 are the smallest and the largest timestamps that TSDB for InfluxDB® can store?

How can I view the data types of fields?

Can I convert the data types of fields?

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?

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 that is 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 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 return null 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?

Why is the series cardinality important?

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

How does TSDB for InfluxDB® process duplicate points?

Which line feed is required by HTTP API requests?

Which characters and words do I need to avoid 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 execute the `USE` statement to specify a database if I am not an administrator?

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

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

Why is data lost after I create a 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 return null values?

Why is data lost when I run `SELECT INTO` queries?

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