Use OFFSET and SOFFSET to exclude points and series from query results - Time Series Database

OFFSET paginates N points in query results. SOFFSET paginates N series in query results. Use them with LIMIT and SLIMIT to page through large result sets.

OFFSET clause

OFFSET <N> skips the first N points in query results, returning the next page of data.

Syntax

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] LIMIT_clause OFFSET <N> [SLIMIT_clause]

Usage notes

OFFSET requires a LIMIT clause. Place LIMIT before OFFSET in your query. Using OFFSET without LIMIT can cause inconsistent query results.

If your WHERE clause includes a time range, TSDB for InfluxDB® may return points whose timestamps fall outside that range when OFFSET is applied.

Examples

Return points starting from a specific position

The following query returns the 4th, 5th, and 6th points from the h2o_feet measurement. Without OFFSET 3, the query returns the 1st, 2nd, and 3rd points.

> SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3

name: h2o_feet
time                   water_level   location
----                   -----------   --------
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

Combine OFFSET with multiple clauses

This example combines OFFSET with several other clauses. Here's the clause-by-clause breakdown:

SELECT MEAN("water_level") — applies the MEAN() InfluxQL function.
FROM "h2o_feet" — specifies the measurement.
WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' — specifies the time range.
GROUP BY *,time(12m) — groups by all tags and a 12-minute interval.
ORDER BY time DESC — returns results in descending timestamp order.
LIMIT 2 — limits results to two points.
OFFSET 2 — skips the first two averages.
SLIMIT 1 — limits results to one series.

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:00:00Z   8.0625

Without OFFSET 2, the query returns the first two averages instead:

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:36:00Z   7.303
2015-08-18T00:24:00Z   7.5675

SOFFSET clause

SOFFSET <N> skips the first N series in query results, returning the next page of series.

Syntax

SELECT_clause [INTO_clause] FROM_clause [WHERE_clause] GROUP BY *[,time(time_interval)] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] SLIMIT_clause SOFFSET <N>

Usage notes

SOFFSET requires a SLIMIT clause. Place SLIMIT before SOFFSET in your query. Using SOFFSET without SLIMIT can cause inconsistent query results.

If N is greater than the total number of series, TSDB for InfluxDB® returns no results.

Examples

Return series starting from a specific position

The following query returns data for the series associated with the h2o_feet measurement and location=santa_monica. Without SOFFSET 1, the query returns the location=coyote_creek series instead.

> SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
[...]
2015-09-18T21:36:00Z   5.066
2015-09-18T21:42:00Z   4.938

Combine SOFFSET with multiple clauses

This example builds on the multi-clause OFFSET example by adding SOFFSET 1, which skips the first series (location=coyote_creek) and returns the second series (location=santa_monica).

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE time >= '2015-08-18T00:00:00Z' AND time <= '2015-08-18T00:42:00Z' GROUP BY *,time(12m) ORDER BY time DESC LIMIT 2 OFFSET 2 SLIMIT 1 SOFFSET 1

name: h2o_feet
tags: location=santa_monica
time                   mean
----                   ----
2015-08-18T00:12:00Z   2.077
2015-08-18T00:00:00Z   2.09

Without SOFFSET 1, the query returns the location=coyote_creek series:

name: h2o_feet
tags: location=coyote_creek
time                   mean
----                   ----
2015-08-18T00:12:00Z   7.8245
2015-08-18T00:00:00Z   8.0625

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