The GROUP BY clause groups query results based on the specified tags or time intervals.
GROUP BY tags
GROUP BY<tag> groups query results based on the specified tags.
Syntax
SELECT_clause FROM_clause [WHERE_clause] GROUP BY [*|<tag_key>[,<tag_key]]Description
GROUP BY * group query results by all tags.
GROUP BY <tag_key> group the query results by a specified tag.
GROUP BY <tag_key>,<tag_key> groups query results based on multiple tags. The order of the tag keys does not affect the query results.
If the query statement contains a WHERE clause, the GROUP BY clause must be placed after the WHERE clause.
If a query includes a WHERE clause and a GROUP BY clause, the GROUP BY clause must appear after the WHERE clause.
Examples
Group query results by a single tag
> SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location" name: h2o_feet tags: location=coyote_creek time mean -------- 1970-01-01T00:00:00Z5.359342451341401 name: h2o_feet tags: location=santa_monica time mean -------- 1970-01-01T00:00:00Z3.530863470081006The query uses an InfluxQL function to calculate the average field value of the water_level field key for each tag value of location in the h2o_feet measurement. TSDB for InfluxDB® returns the data in two series, and one series is returned for each tag value of location.
In TSDB for InfluxDB®, epoch 0 is used as a null timestamp in most cases. Note that epoch 0 is also known as 1970-01-01T00:00:00Z. If your query has no timestamp to return, TSDB for InfluxDB® returns epoch 0 as the timestamp. For example, this occurs if your query includes an aggregate function where an unbounded time range is specified.
Group query results based on multiple tags
> SELECT MEAN("index") FROM "h2o_quality" GROUP BY location,randtag name: h2o_quality tags: location=coyote_creek, randtag=1 time mean -------- 1970-01-01T00:00:00Z50.69033760186263 name: h2o_quality tags: location=coyote_creek, randtag=2 time mean -------- 1970-01-01T00:00:00Z49.661867544220485 name: h2o_quality tags: location=coyote_creek, randtag=3 time mean -------- 1970-01-01T00:00:00Z49.360939907550076 name: h2o_quality tags: location=santa_monica, randtag=1 time mean -------- 1970-01-01T00:00:00Z49.132712456344585 name: h2o_quality tags: location=santa_monica, randtag=2 time mean -------- 1970-01-01T00:00:00Z50.2937984496124 name: h2o_quality tags: location=santa_monica, randtag=3 time mean -------- 1970-01-01T00:00:00Z49.99919903884662The query uses an InfluxQL function to calculate the average field value of the index field key for each combination of the location tag values and the randtag tag values in the h2o_quality measurement. The location tag has two unique values and the randtag tag has three unique values. Therefore, a total of six combinations are available for the tag values of the two tag keys. Separate tags with commas (,) in the GROUP BY clause.
Group query results based on all the tags
> SELECT MEAN("index") FROM "h2o_quality" GROUP BY * name: h2o_quality tags: location=coyote_creek, randtag=1 time mean -------- 1970-01-01T00:00:00Z50.69033760186263 name: h2o_quality tags: location=coyote_creek, randtag=2 time mean -------- 1970-01-01T00:00:00Z49.661867544220485 name: h2o_quality tags: location=coyote_creek, randtag=3 time mean -------- 1970-01-01T00:00:00Z49.360939907550076 name: h2o_quality tags: location=santa_monica, randtag=1 time mean -------- 1970-01-01T00:00:00Z49.132712456344585 name: h2o_quality tags: location=santa_monica, randtag=2 time mean -------- 1970-01-01T00:00:00Z50.2937984496124 name: h2o_quality tags: location=santa_monica, randtag=3 time mean -------- 1970-01-01T00:00:00Z49.99919903884662The query uses an InfluxQL function to calculate the average field value of the index field key for each combination of the tag values in the h2o_quality measurement.
Note that the query results are the same as the results of the query in the preceding example. This is because the h2o_quality measurement has only two tag keys: location and randtag.
GROUP BY time intervals
GROUP BY time() groups query results based on the specified time interval.
Basic syntax
SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>),[tag_key][fill(<fill_option>)]Description
Basic GROUP BY time() queries require the SELECT clauses that include InfluxQL functions and the WHERE clauses where time ranges are specified. Note that the GROUP BY clause must appear after the WHERE clause.
time(time_interval)
The time_interval parameter in the GROUP BY time() clause specifies a duration literal, and determines how TSDB for InfluxDB® groups query results in terms of the time dimension. For example, if the time_interval parameter is set to 5m, the time range specified in the WHERE clause is divided into 5-minute intervals and the query results are grouped based on each 5-minute interval.
fill(<fill_option>)
The fill(<fill_option>) function is optional. For time intervals in which no data is reported, the function returns the value specified by the fill_option parameter.
Coverage:
Basic GROUP BY time() queries determine the raw data reported in each time interval and the timestamps returned by the query based on the settings of time_interval and the preset time boundaries of TSDB for InfluxDB®.
Examples of basic syntax
The following example uses the following data:
> SELECT "water_level","location" FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' name: h2o_feet -------------- time water_level location 2015-08-18T00:00:00Z8.12 coyote_creek 2015-08-18T00:00:00Z2.064 santa_monica 2015-08-18T00:06:00Z8.005 coyote_creek 2015-08-18T00:06:00Z2.116 santa_monica 2015-08-18T00:12:00Z7.887 coyote_creek 2015-08-18T00:12:00Z2.028 santa_monica 2015-08-18T00:18:00Z7.762 coyote_creek 2015-08-18T00:18:00Z2.126 santa_monica 2015-08-18T00:24:00Z7.635 coyote_creek 2015-08-18T00:24:00Z2.041 santa_monica 2015-08-18T00:30:00Z7.5 coyote_creek 2015-08-18T00:30:00Z2.051 santa_monicaGroup query results at 12-minute intervals
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m) name: h2o_feet -------------- time count 2015-08-18T00:00:00Z2 2015-08-18T00:12:00Z2 2015-08-18T00:24:00Z2The query uses an InfluxQL function to calculate the number of the points of the water_level field key for the location = coyote_creek tag in the h2o_feet measurement. The query groups the results based on a 12-minute interval.
The result associated with each timestamp indicates the result that corresponds to each 12-minute interval. The result associated with the first timestamp covers the raw data that occurs from 2015-08-18T00:00:00Z to 2015-08-18T00:12:00Z. Note that data associated with the 2015-08-18T00:12:00Z timestamp is excluded. The result associated with the first timestamp covers the raw data that occurs from 2015-08-18T00:12:00Z to 2015-08-18T00:24:00Z. Note that data associated with the 2015-08-18T00:24:00Z timestamp is excluded.
Group query results based on a 12-minute interval and a tag key
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:30:00Z' GROUP BY time(12m),"location" name: h2o_feet tags: location=coyote_creek time count --------- 2015-08-18T00:00:00Z2 2015-08-18T00:12:00Z2 2015-08-18T00:24:00Z2 name: h2o_feet tags: location=santa_monica time count --------- 2015-08-18T00:00:00Z2 2015-08-18T00:12:00Z2 2015-08-18T00:24:00Z2The query uses an InfluxQL function to calculate the number of the points of the water_level field key and groups results based on the location tag and a 12-minute interval. Note that the time interval and the tag key are separated with commas (,) in the GROUP BY clause.
The query returns the data in two series, and one series is returned for each tag value of location. The result associated with each timestamp indicates the result that corresponds to each 12-minute interval. The result associated with the first timestamp covers the raw data that occurs from 2015-08-18T00:00:00Z to 2015-08-18T00:12:00Z. Note that data associated with the 2015-08-18T00:12:00Z timestamp is excluded. The result associated with the first timestamp covers the raw data that occurs from 2015-08-18T00:12:00Z to 2015-08-18T00:24:00Z. Note that data associated with the 2015-08-18T00:24:00Z timestamp is excluded.
FAQ about basic syntax
Why do the query results contain unexpected timestamps and values?
In basic syntax, TSDB for InfluxDB® determines the raw data reported in each time interval and the returned timestamps based on the preset time boundaries and the time interval that is specified by GROUP BY time(). In certain cases, unexpected results may occur.
Examples:
Raw data:
> SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:18:00Z' name: h2o_feet -------------- time water_level 2015-08-18T00:00:00Z8.12 2015-08-18T00:06:00Z8.005 2015-08-18T00:12:00Z7.887 2015-08-18T00:18:00Z7.762Query and results:
The following query covers a time range of 12 minutes and groups the results at 12-minute intervals, but it returns two results:
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <'2015-08-18T00:18:00Z' GROUP BY time(12m) name: h2o_feet time count --------- 2015-08-18T00:00:00Z1<-----Note that this timestamp occurs before the start of the query's time range 2015-08-18T00:12:00Z 1TSDB for InfluxDB® uses the round-off number of the preset time boundaries for the time intervals specified by GROUP BY. The preset time boundaries are not affected by the time conditions specified in the WHERE clause. When TSDB for InfluxDB® calculates the results, the timestamps of all the returned data must fall within the time range that is specified in the query. However, the time intervals specified by GROUP BY are based on the preset time boundaries.
The following table describes the preset time boundaries, relevant time intervals specified by GROUP BY time(), returned points, and returned timestamps for each time interval specified by GROUP BY time() in the results.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
|
|
|
|
2 |
|
|
|
|
The preset time boundaries of the first 12-minute time range are 00:00 and 12:00. Note that the data associated with the 12:00 timestamp is excluded. Only the point 8.005 falls within the first time interval specified by GROUP BY time() and the first time range specified by the corresponding preset time boundaries. The returned timestamp occurs before the start time of the time range specified in the query. However, the query results exclude the data that occurs before the start time of the specified time range.
The preset time boundaries of the second 12-minute time range are 12:00 and 24:00. Note that the data associated with the 24:00 timestamp is excluded. Only the point 7.887 falls within the second time interval specified by GROUP BY time() and the second time range specified by the corresponding preset time boundaries.
The advanced GROUP BY time() syntax allows you to change the start time of the preset time boundaries of TSDB for InfluxDB®. The third example in the following advanced syntax section continues to explore the query shown in the current example. The query in the third example shifts the preset time boundaries forward by 6 minutes so that TSDB for InfluxDB® returns the following result:
name: h2o_feet time count --------- 2015-08-18T00:06:00Z2Advanced GROUP BY time() syntax
SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>,<offset_interval>),[tag_key][fill(<fill_option>)]Description
Advanced GROUP BY time() queries require the SELECT clauses that include InfluxQL functions and the WHERE clauses where time ranges are specified. Note that the GROUP BY clause must appear after the WHERE clause.
time(time_interval,offset_interval)
For more information about time_interval, see the “Basic GROUP BY time() syntax” section.
The offset_interval parameter specifies a duration literal and shifts forward or backward the preset time boundaries of TSDB for InfluxDB®. The value of the offset_interval parameter can be a positive number or a negative number.
fill(<fill_option>)
The fill(<fill_option>) function is optional. For time intervals in which no data is reported, the function returns the value specified by the fill_option parameter.
Examples
The following example uses the following data:
> SELECT "water_level" FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:00:00Z' AND time <='2015-08-18T00:54:00Z' name: h2o_feet -------------- time water_level 2015-08-18T00:00:00Z8.12 2015-08-18T00:06:00Z8.005 2015-08-18T00:12:00Z7.887 2015-08-18T00:18:00Z7.762 2015-08-18T00:24:00Z7.635 2015-08-18T00:30:00Z7.5 2015-08-18T00:36:00Z7.372 2015-08-18T00:42:00Z7.234 2015-08-18T00:48:00Z7.11 2015-08-18T00:54:00Z6.982Group query results based on an 18-minute interval and shift the preset time boundaries forward
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(18m,6m) name: h2o_feet time mean -------- 2015-08-18T00:06:00Z7.884666666666667 2015-08-18T00:24:00Z7.502333333333333 2015-08-18T00:42:00Z7.108666666666667The query uses an InfluxQL function to calculate the average field value of the water_level field key. The query groups results based on an 18-minute interval and shifts the preset time boundaries forward by 6 minutes.
For the queries where the offset_interval parameter is not specified, the time boundaries and the returned timestamps are based on the preset time boundaries of TSDB for InfluxDB®. The offset_interval parameter is not specified in the query and the following result is returned:
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(18m) name: h2o_feet time mean -------- 2015-08-18T00:00:00Z7.946 2015-08-18T00:18:00Z7.6323333333333325 2015-08-18T00:36:00Z7.238666666666667 2015-08-18T00:54:00Z6.982For the queries where the offset_interval parameter is not specified, the time boundaries and the returned timestamps are based on the preset time boundaries of TSDB for InfluxDB®.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
|
|
|
|
2 |
| <--same |
|
|
3 |
| <--same |
|
|
4 |
|
|
|
|
The preset time boundaries of the first 18-minute time range are 00:00 and 18:00. Note that the data associated with the 18:00 timestamp is excluded. The two points 8.005 and 7.887 fall within the first time interval specified by GROUP BY time() and the first time range specified by the corresponding preset time boundaries. The returned timestamp occurs before the start time of the time range specified in the query. However, the query results exclude the data that occurs before the start time of the specified time range.
The preset time boundaries of the second 18-minute time range are 18:00 and 36:00. Note that the data associated with the 36:00 timestamp is excluded. The three points 7.762, 7.635, and 7.5 fall within the second time interval specified by GROUP BY time() and the second time range specified by the corresponding preset time boundaries. In this case, the time range specified by the preset time boundaries is the same as the time interval specified by GROUP BY time().
The preset time boundaries of the fourth 18-minute time range are 54:00 and 01:12:00. Note that the data associated with the 01:12:00 timestamp is excluded. Only the point 6.982 falls within the fourth time interval specified by GROUP BY time() and the fourth time range specified by the corresponding preset time boundaries.
For the queries where the offset_interval parameter is specified, the time boundaries and the returned timestamps are based on the specified offset time boundaries.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
| <--same |
|
|
2 |
| <--same |
|
|
3 |
| <--same |
|
|
4 |
| NA | NA | NA |
The 6-minute offset interval shifts forward the time range specified by the preset time boundaries. Therefore, the time range specified by the offset time boundaries and the relevant time interval specified by GROUP BY time() are always the same. The offset_interval parameter is specified in the query, and the query performs calculation on the three points for each time interval. The returned timestamp matches the start time of the time range specified by the offset time boundaries and the start time of the time interval specified by GROUP BY time().
Note that offset_interval forces the fourth time boundary to be out of the time range specified in the query. Therefore, the query returns no results for that last interval.
Group query results based on an 18-minute interval and shift the preset time boundaries backward
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(18m,-12m) name: h2o_feet time mean -------- 2015-08-18T00:06:00Z7.884666666666667 2015-08-18T00:24:00Z7.502333333333333 2015-08-18T00:42:00Z7.108666666666667The query uses an InfluxQL function to calculate the average field value of the water_level field key. The query groups results based on an 18-minute interval and shifts the preset time boundaries backward by 12 minutes.
The query in the current example returns the same results as the query in the preceding example Group query results based on an 18-minute interval and shift the preset time boundaries forward. However, the offset_interval parameter is set to a negative number in the current example and a positive number in the preceding example.` The query performance for the two examples is the same. You can choose the most intuitive option when you decide between a positive number and a negative number for offset_interval`.
For queries without offset_interval, the time boundary and returned timestamp are still TSDB For InfluxDB®The preset time boundary. Let's first look at the query results that are not offset_interval:
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <='2015-08-18T00:54:00Z' GROUP BY time(18m) name: h2o_feet time mean -------- 2015-08-18T00:00:00Z7.946 2015-08-18T00:18:00Z7.6323333333333325 2015-08-18T00:36:00Z7.238666666666667 2015-08-18T00:54:00Z6.982For the queries where the offset_interval parameter is not specified, the time boundaries and the returned timestamps are based on the preset time boundaries of TSDB for InfluxDB®. The offset_interval parameter is not specified in the query and the following result is returned:
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
|
|
|
|
2 |
| <--same |
|
|
3 |
| <--same |
|
|
4 |
|
|
|
|
The preset time boundaries of the first 18-minute time range are 00:00 and 18:00. Note that the data associated with the 18:00 timestamp is excluded. The two points 8.005 and 7.887 fall within the first time interval specified by GROUP BY time() and the first time range specified by the corresponding preset time boundaries. The returned timestamp occurs before the start time of the time range specified in the query. However, the query results exclude the data that occurs before the start time of the specified time range.
The preset time boundaries of the second 18-minute time range are 18:00 and 36:00. Note that the data associated with the 36:00 timestamp is excluded. The three points 7.762, 7.635, and 7.5 fall within the second time interval specified by GROUP BY time() and the second time range specified by the corresponding preset time boundaries. In this case, the time range specified by the preset time boundaries is the same as the time interval specified by GROUP BY time().
The preset time boundaries of the fourth 18-minute time range are 54:00 and 01:12:00. Note that the data associated with the 01:12:00 timestamp is excluded. Only the point 6.982 falls within the fourth time interval specified by GROUP BY time() and the fourth time range specified by the corresponding preset time boundaries.
For the queries where the offset_interval parameter is specified, the time boundaries and the returned timestamps are based on the specified offset time boundaries.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
| NA | NA | NA |
2 |
| <--same |
|
|
3 |
| <--same |
|
|
4 |
| <--same |
|
|
The negative 12-minute offset interval shifts backward the time range specified by the preset time boundaries. Therefore, the time range specified by the offset time boundaries and the relevant time interval specified by GROUP BY time() are always the same. The offset_interval parameter is specified in the query, and the query performs calculation on the three points for each time interval. The returned timestamp matches the start time of the time range specified by the offset time boundaries and the start time of the time interval specified by GROUP BY time().
Note that offset_interval forces the first time boundary to be out of the time range specified in the query. Therefore, the query returns no results for the first interval.
Group query results based on a 12-minute interval and shift the preset time boundaries forward
This example is an extension of the example that is described in the “FAQ about basic syntax” section.
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <'2015-08-18T00:18:00Z' GROUP BY time(12m,6m) name: h2o_feet time count --------- 2015-08-18T00:06:00Z2The query uses an InfluxQL function to calculate the number of the points of water_level. The query groups results based on a 12-minute interval and shifts the preset time boundaries forward by 6 minutes.
For the queries where the offset_interval parameter is not specified, the time boundaries and the returned timestamps are based on the preset time boundaries of TSDB for InfluxDB®. The offset_interval parameter is not specified in the query and the following result is returned:
> SELECT COUNT("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-08-18T00:06:00Z' AND time <'2015-08-18T00:18:00Z' GROUP BY time(12m) name: h2o_feet time count --------- 2015-08-18T00:00:00Z1 2015-08-18T00:12:00Z1For the queries where the offset_interval parameter is not specified, the time boundaries and the returned timestamps are based on the preset time boundaries of TSDB for InfluxDB®.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
|
|
|
|
2 |
|
|
|
|
The preset time boundaries of the first 12-minute time range are 00:00 and 12:00. Note that the data associated with the 12:00 timestamp is excluded. Only the point 8.005 falls within the first time interval specified by GROUP BY time() and the first time range specified by the corresponding preset time boundaries. The returned timestamp occurs before the start time of the time range specified in the query. However, the query results exclude the data that occurs before the start time of the specified time range.
The preset time boundaries of the second 12-minute time range are 12:00 and 24:00. Note that the data associated with the 24:00 timestamp is excluded. Only the point 7.887 falls within the second time interval specified by GROUP BY time() and the second time range specified by the corresponding preset time boundaries.
For the queries where the offset_interval parameter is specified, the time boundaries and the returned timestamps are based on the specified offset time boundaries.
Time interval No. | Preset time boundary |
| Returned point | Returned timestamp |
|---|---|---|---|---|
1 |
| <--same |
|
|
2 |
| NA | NA | NA |
The 6-minute offset interval shifts forward the time range specified by the preset time boundaries. Therefore, the time range specified by the offset time boundaries and the relevant time interval specified by GROUP BY time() are always the same. When the offset_interval parameter is set, the query returns a single result. The returned timestamp matches the start time of the time range specified by the offset time boundaries and the start time of the time interval specified by GROUP BY time().
Note that offset_interval forces the second time boundary to be out of the time range specified in the query. Therefore, the query returns no results for the second interval.
GROUP BY time intervals and fill()
The fill() function changes the value returned for time intervals in which no data is reported.
Syntax
SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(time_interval,[<offset_interval])[,tag_key][fill(<fill_option>)]Description
By default, null values are returned to the output columns for time intervals in which no data is reported. The time intervals are specified by the GROUP BY time() clauses. To change the values returned for time intervals in which no data is reported, use the fill() function. Note that fill() must appear after the GROUP BY clause if you perform the GROUP BY operation based on multiple objects, such as tags and time intervals.
fill_option
Numerical value: returns the specified numerical value for time intervals in which no point is reported.
linear: returns the results of [linear interpolation] (https://en.wikipedia.org/wiki/Linear_interpolation) for time intervals in which no point is reported.none: returns no timestamps and values for time intervals in which no point is reported.null: returns timestamps andnullvalues for time intervals in which no point is reported. The returned results are the same as those when you use the default values.previous: returns the value that corresponds to the previous time interval for time intervals in which no point is reported.
Example 1: Use a query where the fill(100) function is not used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00ZExample 2: Use a query where the fill(100) function is used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) fill(100) name: h2o_feet -------------- ime max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00Z100fill(100) changed the value of the time interval without data points. There was no data display for the fourth time interval, but after using the fill(100), the return value changed to 100.
Example 3: Use a query where the fill(linear) function is not used
> SELECT MEAN("tadpoles") FROM "pond" WHERE time >='2016-11-11T21:00:00Z' AND time <='2016-11-11T22:06:00Z' GROUP BY time(12m) name: pond time mean -------- 2016-11-11T21:00:00Z1 2016-11-11T21:12:00Z 2016-11-11T21:24:00Z3 2016-11-11T21:36:00Z 2016-11-11T21:48:00Z 2016-11-11T22:00:00Z6Example 4: Use a query where the fill(linear) function is used
> SELECT MEAN("tadpoles") FROM "pond" WHERE time >='2016-11-11T21:00:00Z' AND time <='2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear) name: pond time mean -------- 2016-11-11T21:00:00Z1 2016-11-11T21:12:00Z2 2016-11-11T21:24:00Z3 2016-11-11T21:36:00Z4 2016-11-11T21:48:00Z5 2016-11-11T22:00:00Z6fill(linear) change the return value of the time interval without data points to the result of linear interpolation.
Note: The data in Example 2 is not in the database NOAA_water_database. In order to use fill(linear), we created a dataset with less conventional data.
Example 5: Use a query where the fill(none) function is not used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00ZExample 6: Use a query where the fill(none) function is used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) fill(none) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235fill(null), for intervals without data points, neither timestamps nor values are returned.
Example 7: Use a query where the fill(null) function is not used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00ZExample 8: Use a query where the fill(null) function is used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) fill(null) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00ZThe fill(null) function returns null as the value for the time interval in which no point is reported. The returned results are the same as those when the fill(null) function is not used in the query.
Example 9: Use a query where the fill(previous) function is not used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00ZExample 10: Use a query where the fill(previous) function is used
> SELECT MAX("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T16:00:00Z' AND time <='2015-09-18T16:42:00Z' GROUP BY time(12m) fill(previous) name: h2o_feet -------------- time max 2015-09-18T16:00:00Z3.599 2015-09-18T16:12:00Z3.402 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00Z3.235The fill(previous) function changes the returned value to 3.235 for the time interval in which no point is reported. The returned value is the same as that reported for the previous time interval.
FAQ about the fill() function
What are the impacts if I use the fill() function when no data is reported in the time range specified in the query?
The fill() function does not take effect if no data is reported for the time range specified in the query. This is the expected result.
Example:
The following query returns no data because no points are reported for the water_level field key in the specified time range. Note that fill(800) does not affect the query results.
> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='coyote_creek' AND time >='2015-09-18T22:00:00Z' AND time <='2015-09-18T22:18:00Z' GROUP BY time(12m) fill(800) >What are the impacts if I use fill(previous) when the previous result falls out of the time range specified in the query?
The fill(previous) function does not fill the result for a time interval if the previous value falls out of the time range specified in the query.
Example:
The following query covers the time range from 2015-09-18T16:24:00Z to 2015-09-18T16:54:00Z. The fill(previous) function fills the value for 2015-09-18T16:36:00Z by using the value that corresponds to 2015-09-18T16:24:00Z.
> SELECT MAX("water_level") FROM "h2o_feet" WHERE location ='coyote_creek' AND time >='2015-09-18T16:24:00Z' AND time <='2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous) name: h2o_feet -------------- time max 2015-09-18T16:24:00Z3.235 2015-09-18T16:36:00Z3.235 2015-09-18T16:48:00Z4The next query shortens the time range that is used in the previous query. The current query covers the time range from 2015-09-18T16:36:00Z to 2015-09-18T16:54:00Z. The fill(previous) function does not fill the value for 2015-09-18T16:36:00Z by using the value that corresponds to 2015-09-18T16:24:00Z. This is because the value that corresponds to 2015-09-18T16:24:00Z falls out of the shortened time range specified in the query.
> SELECT MAX("water_level") FROM "h2o_feet" WHERE location ='coyote_creek' AND time >='2015-09-18T16:36:00Z' AND time <='2015-09-18T16:54:00Z' GROUP BY time(12m) fill(previous) name: h2o_feet -------------- time max 2015-09-18T16:36:00Z 2015-09-18T16:48:00Z4What are the impacts if I use fill(linear) when the previous or next result falls out of the time range specified in the query?
If the previous or next result falls out of the time range specified in the query, fill(linear) does not fill the result for time intervals in which no point is reported.
Example:
The following query covers the time range from 2016-11-11T21:24:00Z to 2016-11-11T22:06:00Z. The fill(linear) function fills the value for 2016-11-11T21:36:00Z by using the value that corresponds to 2016-11-11T21:24:00Z. This function also fills the value for 2016-11-11T21:48:00Z by using the value that corresponds to 2016-11-11T22:00:00Z.
> SELECT MEAN("tadpoles") FROM "pond" WHERE time >'2016-11-11T21:24:00Z' AND time <='2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear) name: pond time mean -------- 2016-11-11T21:24:00Z3 2016-11-11T21:36:00Z4 2016-11-11T21:48:00Z5 2016-11-11T22:00:00Z6The next query shortens the time range that is used in the previous query. The current query covers the time range from 2016-11-11T21:36:00Z to 2016-11-11T22:06:00Z. The fill(linear) function does not fill the values for 2016-11-11T21:36:00Z and 2016-11-11T21:48:00Z. This is because the value that corresponds to 2016-11-11T21:24:00Z falls out of the shortened time range specified in the query and TSDB for InfluxDB® cannot perform linear interpolation.
> SELECT MEAN("tadpoles") FROM "pond" WHERE time >='2016-11-11T21:36:00Z' AND time <='2016-11-11T22:06:00Z' GROUP BY time(12m) fill(linear) name: pond time mean -------- 2016-11-11T21:36:00Z 2016-11-11T21:48:00Z 2016-11-11T22:00:00Z6The sample data used in this example is not stored in the NOAA_water_database database. Therefore, a data set that has a small amount of regular data is created to use fill(linear).