Group and aggregate query results by tags and time - Time Series Database

The GROUP BY clause groups query results by tag values, time intervals, or both. Use it with aggregate functions to compute statistics across groups of points.

Topic	Description
GROUP BY tags	Group by one tag, multiple tags, or all tags
GROUP BY time() — basic syntax	Group by fixed time intervals
GROUP BY time() — advanced syntax	Shift time boundaries with an offset
fill()	Control values returned for empty time intervals

GROUP BY tags

Group points by one or more tag values.

Syntax

SELECT_clause FROM_clause [WHERE_clause] GROUP BY [* | <tag_key> [,<tag_key>]]

Parameters

Expression	Behavior
`GROUP BY *`	Groups by the values of all tags in the measurement
`GROUP BY <tag_key>`	Groups by the values of a single tag
`GROUP BY <tag_key>,<tag_key>`	Groups by the combined values of multiple tags; tag key order does not affect results

Place GROUP BY after the WHERE clause when both are present. Regular expressions are supported in GROUP BY clauses.

Examples

Group by one tag

SELECT MEAN("water_level") FROM "h2o_feet" GROUP BY "location"

name: h2o_feet
tags: location=coyote_creek
time                     mean
----                     ----
1970-01-01T00:00:00Z     5.359342451341401

name: h2o_feet
tags: location=santa_monica
time                     mean
----                     ----
1970-01-01T00:00:00Z     3.530863470081006

This query calculates the mean water_level for each value of the location tag. TSDB for InfluxDB® returns one series per unique tag value.

When no time range is specified, TSDB for InfluxDB® returns epoch 0 (1970-01-01T00:00:00Z) as the timestamp. Epoch 0 is a placeholder that indicates no specific time window was queried.

Group by 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:00Z     50.69033760186263

name: h2o_quality
tags: location=coyote_creek, randtag=2
time                     mean
----                     ----
1970-01-01T00:00:00Z     49.661867544220485

name: h2o_quality
tags: location=coyote_creek, randtag=3
time                     mean
----                     ----
1970-01-01T00:00:00Z     49.360939907550076

name: h2o_quality
tags: location=santa_monica, randtag=1
time                     mean
----                     ----
1970-01-01T00:00:00Z     49.132712456344585

name: h2o_quality
tags: location=santa_monica, randtag=2
time                     mean
----                     ----
1970-01-01T00:00:00Z     50.2937984496124

name: h2o_quality
tags: location=santa_monica, randtag=3
time                     mean
----                     ----
1970-01-01T00:00:00Z     49.99919903884662

This query groups index values by the combination of location (2 values) and randtag (3 values), producing 6 series. Separate multiple tag keys with commas.

Group by all tags

SELECT MEAN("index") FROM "h2o_quality" GROUP BY *

Results are identical to GROUP BY location,randtag because h2o_quality contains only those two tags. GROUP BY * is a convenient shorthand when you want to group by every tag in a measurement without listing them individually.

GROUP BY time intervals

Group points into fixed time windows and apply an aggregate function across each window.

Basic syntax

SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>)[,<tag_key>][fill(<fill_option>)]

Requirements

The SELECT clause must include an aggregate function (MEAN(), COUNT(), MAX(), etc.).
The WHERE clause must specify a time range.
Place GROUP BY after the WHERE clause.

Parameters

Parameter	Description
`time_interval`	The width of each time window. For example, `12m` creates 12-minute windows. Points whose timestamps fall within each window are aggregated together.
`fill(<fill_option>)`	Optional. Specifies the value returned when a time window contains no data. See fill().

How time boundaries work

TSDB for InfluxDB® divides time into windows based on round-number boundaries anchored to the Unix epoch, not to the start of your query's time range. For example, with GROUP BY time(12m), windows start at 00:00, 00:12, 00:24, and so on — regardless of when your query starts.

This means a returned timestamp may fall before the start of your WHERE time range, but the data counted in that window only includes points within your specified range.

Sample data for the following examples

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:00Z     8.12          coyote_creek
2015-08-18T00:00:00Z     2.064         santa_monica
2015-08-18T00:06:00Z     8.005         coyote_creek
2015-08-18T00:06:00Z     2.116         santa_monica
2015-08-18T00:12:00Z     7.887         coyote_creek
2015-08-18T00:12:00Z     2.028         santa_monica
2015-08-18T00:18:00Z     7.762         coyote_creek
2015-08-18T00:18:00Z     2.126         santa_monica
2015-08-18T00:24:00Z     7.635         coyote_creek
2015-08-18T00:24:00Z     2.041         santa_monica
2015-08-18T00:30:00Z     7.5           coyote_creek
2015-08-18T00:30:00Z     2.051         santa_monica

Group by 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:00Z     2
2015-08-18T00:12:00Z     2
2015-08-18T00:24:00Z     2

Each timestamp marks the start of a 12-minute window. The window [00:00:00, 00:12:00) contains 2 points, [00:12:00, 00:24:00) contains 2 points, and [00:24:00, 00:36:00) contains 2 points.

Combine tag grouping with time intervals

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

name: h2o_feet
tags: location=santa_monica
time                     count
----                     -----
2015-08-18T00:00:00Z     2
2015-08-18T00:12:00Z     2
2015-08-18T00:24:00Z     2

Combining time() with a tag key produces one series per tag value, each broken into 12-minute windows. Separate the time() function and the tag key with a comma.

Common issues with basic syntax

Unexpected timestamps in results

When a query's time range starts mid-window, TSDB for InfluxDB® returns the window's start timestamp even if it falls before the WHERE start time. Data before the WHERE start time is never included — only the returned timestamp shifts.

Example

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

Query with a time range that starts mid-window:

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:00Z     1     <- timestamp predates the query start
2015-08-18T00:12:00Z     1

The first returned timestamp is 00:00:00Z because the 12-minute window boundary starts there. The count of 1 reflects only the single point at 00:06:00Z, which is within the WHERE range.

The following table shows how TSDB for InfluxDB® determines each window's content and timestamp:

Window	System time boundary	Data within `WHERE` range	Points	Returned timestamp
1	`[00:00:00Z, 00:12:00Z)`	`[00:06:00Z, 00:12:00Z)`	`8.005`	`2015-08-18T00:00:00Z`
2	`[00:12:00Z, 00:24:00Z)`	`[00:12:00Z, 00:18:00Z)`	`7.887`	`2015-08-18T00:12:00Z`

To align returned timestamps with your query start, use the advanced syntax with an offset_interval.

Advanced syntax

Shift time boundaries forward or backward with an offset_interval.

SELECT <function>(<field_key>) FROM_clause WHERE <time_range> GROUP BY time(<time_interval>,<offset_interval>)[,<tag_key>][fill(<fill_option>)]

Parameters

Parameter	Description
`time_interval`	Width of each time window. Same as in the basic syntax.
`offset_interval`	Amount to shift the system's preset time boundaries. Accepts positive values (shift forward) and negative values (shift backward).
`fill(<fill_option>)`	Optional. See fill().

How it works

Adding an offset_interval shifts all window boundaries by the specified duration. A positive offset moves boundaries forward; a negative offset moves them backward. An equivalent positive offset and negative offset produce the same windows.

Sample data for the following examples

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:00Z     8.12
2015-08-18T00:06:00Z     8.005
2015-08-18T00:12:00Z     7.887
2015-08-18T00:18:00Z     7.762
2015-08-18T00:24:00Z     7.635
2015-08-18T00:30:00Z     7.5
2015-08-18T00:36:00Z     7.372
2015-08-18T00:42:00Z     7.234
2015-08-18T00:48:00Z     7.11
2015-08-18T00:54:00Z     6.982

Shift boundaries forward by 6 minutes

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:00Z     7.884666666666667
2015-08-18T00:24:00Z     7.502333333333333
2015-08-18T00:42:00Z     7.108666666666667

The 6m offset shifts 18-minute boundaries from [00:00, 00:18, 00:36, 00:54] to [00:06, 00:24, 00:42, 01:00]. Each window boundary now aligns with the query start.

Compare the result without the offset:

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:00Z     7.946
2015-08-18T00:18:00Z     7.6323333333333325
2015-08-18T00:36:00Z     7.238666666666667
2015-08-18T00:54:00Z     6.982

The following table compares both configurations:

Without offset (GROUP BY time(18m)):

Window	System time boundary	Data within `WHERE` range	Points	Returned timestamp
1	`[00:00:00Z, 00:18:00Z)`	`[00:06:00Z, 00:18:00Z)`	`8.005`, `7.887`	`2015-08-18T00:00:00Z`
2	`[00:18:00Z, 00:36:00Z)`	same	`7.762`, `7.635`, `7.5`	`2015-08-18T00:18:00Z`
3	`[00:36:00Z, 00:54:00Z)`	same	`7.372`, `7.234`, `7.11`	`2015-08-18T00:36:00Z`
4	`[00:54:00Z, 01:12:00Z)`	`time = 00:54:00Z`	`6.982`	`2015-08-18T00:54:00Z`

With 6-minute offset (GROUP BY time(18m,6m)):

Window	System time boundary	Data within `WHERE` range	Points	Returned timestamp
1	`[00:06:00Z, 00:24:00Z)`	same	`8.005`, `7.887`, `7.762`	`2015-08-18T00:06:00Z`
2	`[00:24:00Z, 00:42:00Z)`	same	`7.635`, `7.5`, `7.372`	`2015-08-18T00:24:00Z`
3	`[00:42:00Z, 01:00:00Z)`	same	`7.234`, `7.11`, `6.982`	`2015-08-18T00:42:00Z`
4	`[01:00:00Z, 01:18:00Z)`	—	—	—

Shift boundaries backward by 12 minutes

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:00Z     7.884666666666667
2015-08-18T00:24:00Z     7.502333333333333
2015-08-18T00:42:00Z     7.108666666666667

A -12m backward offset produces the same windows as a +6m forward offset for an 18-minute interval. Choose whichever offset value is more intuitive for your use case.

The following table shows how the backward offset shifts boundaries:

Window	System time boundary	Data within `WHERE` range	Points	Returned timestamp
1	`[2015-08-17T23:48:00Z, 2015-08-18T00:06:00Z)`	—	—	—
2	`[00:06:00Z, 00:24:00Z)`	same	`8.005`, `7.887`, `7.762`	`2015-08-18T00:06:00Z`
3	`[00:24:00Z, 00:42:00Z)`	same	`7.635`, `7.5`, `7.372`	`2015-08-18T00:24:00Z`
4	`[00:42:00Z, 01:00:00Z)`	same	`7.234`, `7.11`, `6.982`	`2015-08-18T00:42:00Z`

Resolve unexpected timestamps with an offset

Continuing the example from Common issues with basic syntax: adding a 6m offset aligns the window boundary with the query start, eliminating the out-of-range timestamp.

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:00Z     2

With the offset applied, the single window [00:06:00Z, 00:18:00Z) captures both points (8.005 and 7.887) and returns the timestamp 00:06:00Z.

Compare the result without the offset:

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

Window	System time boundary (no offset)	System time boundary (offset 6m)	Returned timestamp
1	`[00:00:00Z, 00:12:00Z)` → `[00:06:00Z, 00:12:00Z)`	`[00:06:00Z, 00:18:00Z)`	`00:00:00Z` → `00:06:00Z`
2	`[00:12:00Z, 00:24:00Z)` → `[00:12:00Z, 00:18:00Z)`	outside range	`00:12:00Z` → —

GROUP BY time() and fill()

By default, TSDB for InfluxDB® returns null for any time window that contains no data. Use fill() to replace those null values with a specified alternative.

Syntax

SELECT <function>(<field_key>) FROM_clause WHERE <time_range>
  GROUP BY time(<time_interval>[,<offset_interval>])[,<tag_key>]
  [fill(<fill_option>)]

Place fill() after the GROUP BY clause.

fill() options

Option	Behavior
`fill(<number>)`	Returns the specified number for empty windows
`fill(linear)`	Returns a linearly interpolated value between the surrounding windows within the time range
`fill(none)`	Omits empty windows entirely — no timestamp or value is returned
`fill(null)`	Returns `null` for empty windows (default behavior)
`fill(previous)`	Returns the value from the previous window

Examples

The following examples use data from the h2o_feet measurement. The time window 2015-09-18T16:36:00Z to 2015-09-18T16:48:00Z contains no data, which makes it useful for demonstrating fill behavior.

fill(\<number\>)

Without fill:

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:00Z     3.599
2015-09-18T16:12:00Z     3.402
2015-09-18T16:24:00Z     3.235
2015-09-18T16:36:00Z

With fill(100):

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
time                     max
----                     ---
2015-09-18T16:00:00Z     3.599
2015-09-18T16:12:00Z     3.402
2015-09-18T16:24:00Z     3.235
2015-09-18T16:36:00Z     100

The empty 16:36:00Z window returns 100 instead of null.

fill(linear)

Without fill:

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:00Z     1
2016-11-11T21:12:00Z
2016-11-11T21:24:00Z     3
2016-11-11T21:36:00Z
2016-11-11T21:48:00Z
2016-11-11T22:00:00Z     6

With fill(linear):

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:00Z     1
2016-11-11T21:12:00Z     2
2016-11-11T21:24:00Z     3
2016-11-11T21:36:00Z     4
2016-11-11T21:48:00Z     5
2016-11-11T22:00:00Z     6

TSDB for InfluxDB® interpolates values between 21:00:00Z (mean=1) and 21:24:00Z (mean=3), and between 21:24:00Z (mean=3) and 22:00:00Z (mean=6).

The pond measurement data in these examples is not stored in the NOAA_water_database database. To reproduce these results, create a dataset with regular sparse data.

fill(none)

With fill(none), empty windows produce no output — neither a timestamp nor a value.

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:00Z     3.599
2015-09-18T16:12:00Z     3.402
2015-09-18T16:24:00Z     3.235

The 16:36:00Z window is omitted entirely.

fill(null)

fill(null) is the default. It produces a timestamp with a blank value for empty windows.

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:00Z     3.599
2015-09-18T16:12:00Z     3.402
2015-09-18T16:24:00Z     3.235
2015-09-18T16:36:00Z

fill(previous)

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:00Z     3.599
2015-09-18T16:12:00Z     3.402
2015-09-18T16:24:00Z     3.235
2015-09-18T16:36:00Z     3.235

The empty 16:36:00Z window uses the value from the previous window (3.235).

FAQ about fill()

What happens if there is no data anywhere in the WHERE time range?

fill() has no effect. TSDB for InfluxDB® returns no 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)

No results are returned because no data exists in the specified range.

What does fill(previous) return when the previous window is outside the WHERE range?

If the previous window falls outside the WHERE time range, fill(previous) returns null for that window.

In this example, the time range starts at 16:24:00Z. The window at 16:36:00Z is empty, and the previous window at 16:24:00Z is within range, so fill(previous) returns 3.235:

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:00Z     3.235
2015-09-18T16:36:00Z     3.235
2015-09-18T16:48:00Z     4

In this example, the time range starts at 16:36:00Z. The window at 16:36:00Z is empty, and the previous window at 16:24:00Z is outside the range, so fill(previous) returns null:

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:00Z     4

What does fill(linear) return when a boundary window is outside the WHERE range?

If the previous or subsequent time interval of a time interval is not in the specified time range, fill(linear) does not return a value for the time interval.

In this example, both 21:36:00Z and 21:48:00Z can be interpolated because 21:24:00Z and 22:00:00Z are within range:

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:00Z     3
2016-11-11T21:36:00Z     4
2016-11-11T21:48:00Z     5
2016-11-11T22:00:00Z     6

In this example, 21:24:00Z is outside the WHERE range. Without a preceding anchor point, TSDB for InfluxDB® cannot interpolate 21:36:00Z or 21:48:00Z:

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:00Z     6

The pond data in these examples is not stored in the NOAA_water_database database. Create a sparse dataset to reproduce these results.