FAQ

All functions

Which InfluxQL functions support nesting in a SELECT statement?

The following 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 all other InfluxQL functions, use subqueries as an alternative. For more information, see the "Data exploration" topic.

What happens when the query time range extends past now()?

For most SELECT statements, the default time range spans from 1677-09-21 00:12:43.145224194 UTC to 2262-04-11T23:47:16.854775806Z UTC. When a SELECT statement includes InfluxQL functions and a GROUP BY time() clause, the default time range narrows: it runs from 1677-09-21 00:12:43.145224194 up to the time returned by now().

To query data with timestamps after now(), explicitly set the end time in the GROUP BY time() clause. The statement must also include an InfluxQL function and a WHERE clause.

Aggregate functions

What timestamp does an aggregate function query return?

The returned timestamp depends on whether the WHERE clause includes a time range:

TSDB for InfluxDB® uses epoch 0 as a null timestamp when no single timestamp can be returned — for example, when SUM() aggregates data points across multiple timestamps.

Example 1: Aggregate with no time range

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

name: h2o_feet
time                   sum
-------
1970-01-01T00:00:00Z   67777.66900000004

SUM() aggregates data points across different timestamps, so no single timestamp exists. The query returns epoch 0.

Example 2: Aggregate with a time range

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

name: h2o_feet
time                  sum
-------
2015-08-18T00:00:00Z  67777.66900000004

The query returns the lower bound of the WHERE clause time range: 2015-08-18T00:00:00Z.

Example 3: Aggregate with GROUP BY time() and a time range

> 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:00Z  20.305
2015-08-18T00:12:00Z  19.802999999999997

The query returns the start time of each 12-minute interval defined by GROUP BY time().

Can I mix aggregate and non-aggregate fields in the same SELECT statement?

No. An aggregate function returns a single calculated value, so there is no meaningful value to return for unaggregated field keys or tag keys in the same statement. Including both produces an error:

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

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

Why does the same aggregate function return slightly different results for the same data?

This can happen with FLOAT64 data. TSDB for InfluxDB® does not sort data points before applying aggregate functions, which can cause minor floating-point differences when the same function is applied to the same set of points.

Selector functions

What timestamp does a selector function query return?

The returned timestamp depends on the number of functions, the number of field keys, and whether GROUP BY time() is used:

When GROUP BY time() is combined with SAMPLE(), the timestamps returned by SAMPLE() differ from other selector functions.

Example 1: Single selector, single field key, no time range

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

name: h2o_feet
time                  max
-------
2015-08-29T07:24:00Z  9.964

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

name: h2o_feet
time                  max
-------
2015-08-29T07:24:00Z  9.964

Both queries return the raw timestamp of the data point with the largest value.

Example 2: Single selector, multiple field keys, no 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:00Z  9.964

FIRST(*) returns epoch 0 because the two field keys in h2o_feet produce two different timestamps — when no single timestamp can be returned, the system uses epoch 0. MAX(*) returns a raw timestamp because h2o_feet contains only one numeric field, so there is only one timestamp to return.

Example 3: Multiple selectors, no time range

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

name: h2o_feet
time                  max    min
----------
1970-01-01T00:00:00Z  9.964  -0.61

MAX() and MIN() select data points at different timestamps. With no single timestamp to return, the query returns epoch 0.

Example 4: Multiple selectors, with 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:00Z  9.964  -0.61

The query returns the lower bound of the WHERE clause time range: 2015-08-18T00:00:00Z.

Example 5: Single selector with GROUP BY time()

> 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:00Z  8.12
2015-08-18T00:12:00Z  7.887

The query returns the start time of each 12-minute interval defined by GROUP BY time().