Time Series Database:FAQ

All functions

Which InfluxQL functions can be nested in a SELECT statement?

The following InfluxQL functions can be nested in a SELECT statement:

• COUNT() nested in DISTINCT()

• CUMULATIVE_SUM()

• DERIVATIVE()

• DIFFERENCE()

• ELAPSED()

• MOVING_AVERAGE()

• NON_NEGATIVE_DERIVATIVE()

• HOLT_WINTERS() and HOLT_WINTERS_WITH_FIT()

For other InfluxQL functions, you can use InfluxQL subqueries as an alternative to nested functions. For more information about subqueries, see the "Data exploration" topic.

What are the impacts if the end time of the time range in a query is later than the time specified by the now() function?

For most SELECT statements, the default time range is from 1677-09-21 00:12:43.145224194 UTC to 2262-04-11T23:47:16.854775806Z UTC. If your SELECT statements include InfluxQL functions and GROUP BY time() clauses, the queries return the data whose timestamps fall in the default time range. The default time range is from 1677-09-21 00:12:43.145224194 to the time that is specified 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. This requires that the SELECT statements include InfluxQL functions and WHERE clauses.

Aggregate functions

Which types of timestamps are returned for queries?

If a query uses aggregate functions and you do not specify a time range in the WHERE clause, the query returns 1970-01-01T00:00:00Z (epoch 0) as the timestamp. TSDB for InfluxDB® uses epoch 0 as a null timestamp. If a query uses aggregate functions and you specify a time range in the WHERE clause, the query returns the start time of the time range as the timestamp.

Example 1: Use an aggregate function in a query where no time range is specified

> SELECT SUM("water_level") FROM "h2o_feet"

name: h2o_feet
time                   sum
-------
1970-01-01T00:00:00Z67777.66900000004

This query returns 1970-01-01T00:00:00Z as the timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp. The SUM() function aggregates points that are attached with different timestamps. Therefore, no single timestamp can be returned.

Example 2: Use an aggregate function in a query where a time range is specified

> SELECT SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z'

name: h2o_feet
time                  sum
-------
2015-08-18T00:00:00Z67777.66900000004

This query returns the start time of the time range as the timestamp. In the WHERE clause, the start time of the time range is specified as WHERE time >= '2015-08-18T00:00:00Z'.

Example 3: Use a GROUP BY time() clause and an aggregate function in a query where a time range is specified

> SELECT SUM("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:18:00Z' GROUP BY time(12m)

name: h2o_feet
time                  sum
-------
2015-08-18T00:00:00Z20.305
2015-08-18T00:12:00Z19.802999999999997

This query returns the start time of each time interval as the timestamp. The time intervals are specified by the GROUP BY time() clause.

Can I use an aggregate function in conjunction with the data that is not aggregated?

No, an aggregate function cannot be used in conjunction with the data that is not aggregated. If you use an aggregate function in a SELECT statement, you cannot specify the field keys or the tag keys that are not aggregated in the statement. An aggregate function returns a single aggregated value. No single value can be returned for the fields or tags that are not aggregated. If a SELECT statement includes both an aggregate function and the field keys or the tag keys that are not aggregated, the following error is returned.

> SELECT SUM("water_level"),"location" FROM "h2o_feet"

ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported

Why do I obtain slightly different results if I apply the same aggregate function to the same set of points?

For some aggregate functions, you may obtain slightly different results if you apply the same function to the same set of points of the FLOAT64 data type. The possible cause is that TSDB for InfluxDB® does not sort points before the aggregate function is applied.

Selector functions

What types of timestamps are returned for queries?

The timestamps that are returned by selector functions depend on the clauses and the number of functions in queries.

If your query includes a single selector function, a single field key, and no GROUP BY time() clauses, the query returns the timestamps of the points in raw data. If your query includes a single selector function, multiple field keys, and no GROUP BY time()clauses, the query returns the timestamps of the points in raw data or null timestamps. TSDB for InfluxDB® uses epoch 0 (1970-01-01T00:00:00Z). If your query includes multiple functions, multiple field keys, and the GROUP BY time() clause where no time range is specified, the query returns null timestamps. TSDB for InfluxDB® uses epoch 0 (1970-01-01T00:00:00Z). If your query includes multiple functions, multiple field keys, and the GROUP BY time() clause where a time range is specified, the query returns the start time of the time range as the timestamp. If your query includes a single selector function and a GROUP BY time() clause, the query returns the start time of each time interval as the timestamp. The time intervals are specified by the GROUP BY time() clause.

Example 2: Use a single selector function, specify multiple field keys, and do not specify a time range

> SELECT MAX("water_level") FROM "h2o_feet"

name: h2o_feet
time                  max
-------
2015-08-29T07:24:00Z9.964

> SELECT MAX("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z'

name: h2o_feet
time                  max
-------
2015-08-29T07:24:00Z9.964

This query returns the timestamp of the point that has the largest value in raw data.

Example 2: Use a single selector function, specify multiple field keys, and do not specify a time range

> SELECT FIRST(*) FROM "h2o_feet"

name: h2o_feet
time                  first_level description  first_water_level
--------------------------------------------
1970-01-01T00:00:00Z  between 6 and 9 feet     8.12

> SELECT MAX(*) FROM "h2o_feet"

name: h2o_feet
time                  max_water_level
-------------------
2015-08-29T07:24:00Z9.964

The first query returns the 1970-01-01T00:00:00Z timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp. The FIRST(*) function returns two timestamps of the two field keys in the h2o_feet measurement. Therefore, the system overwrites the two timestamps with a null timestamp.

The second query returns the timestamp of the point that has the largest value in raw data. The h2o_feet measurement contains only one numeric field. The MAX(*) function returns only one timestamp. Therefore, the system does not overwrite the original timestamp.

Example 3: Use multiple selector functions and do not specify a time range

> SELECT MAX("water_level"),MIN("water_level") FROM "h2o_feet"

name: h2o_feet
time                  max    min
----------
1970-01-01T00:00:00Z9.964-0.61

This query returns the 1970-01-01T00:00:00Z timestamp. TSDB for InfluxDB® uses this timestamp (epoch 0) as a null timestamp. The MAX() and MIN() functions return different timestamps. Therefore, no single timestamp can be returned for this query.

Example 4: Use multiple selector functions and specify a time range

> SELECT MAX("water_level"),MIN("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z'

name: h2o_feet
time                  max    min
----------
2015-08-18T00:00:00Z9.964-0.61

This query returns the start time of the time range as the timestamp. In the WHERE clause, the start time of the time range is specified as 2015-08-18T00:00:00Z.

Example 5: Use a single selector function and specify a time range

> SELECT MAX("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:18:00Z' GROUP BY time(12m)

name: h2o_feet
time                  max
-------
2015-08-18T00:00:00Z8.12
2015-08-18T00:12:00Z7.887

This query returns the start time of each time interval as the timestamp. The time intervals are specified by the GROUP BY time() clause.