All Products
Search
Document Center

Time Series Database:OFFSET and SOFFSET clauses

Last Updated:Aug 05, 2020

The OFFSET clause excludes first N points from the query results. The SOFFSET clause excludes first N series from the query results.

OFFSET clause

The OFFSET <N> clause excludes the first N points from the query results.

Syntax

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

Description

N indicates that the first N points are excluded from the query results. If you need to use the LIMIT clause in your query, you must include the LIMIT clause in your query and place the LIMIT clause before the OFFSET clause. If a query has the OFFSET clause but does not have the LIMIT clause, inconsistent query results may be returned.

Notes: If the WHERE clause includes a time range, TSDB for InfluxDB® returns no results. If the OFFSET clause is used, TSDB for InfluxDB® may return the points whose timestamps fall out of the time range.

Examples

Specify first N points to be excluded from the query results

  1. > SELECT "water_level","location" FROM "h2o_feet" LIMIT 3 OFFSET 3
  2. name: h2o_feet
  3. time water_level location
  4. ---- ----------- --------
  5. 2015-08-18T00:06:00Z 2.116 santa_monica
  6. 2015-08-18T00:12:00Z 7.887 coyote_creek
  7. 2015-08-18T00:12:00Z 2.028 santa_monica

The query returns the fourth, fifth, and sixth points from the h2o_feet measurement. If the preceding statement does not include the OFFSET 3 clause, the query returns the first, second, and third points from the measurement.

Specify first N points to be excluded from the query results, and include multiple clauses

  1. > 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
  2. name: h2o_feet
  3. tags: location=coyote_creek
  4. time mean
  5. ---- ----
  6. 2015-08-18T00:12:00Z 7.8245
  7. 2015-08-18T00:00:00Z 8.0625

The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.

  • The SELECT clause includes an InfluxQL function.
  • The FROM clause specifies a measurement.
  • The WHERE clause specifies the time range.
  • The GROUP BY clause groups query results based on all the tags and a 12-minute interval. The asterisk (*) represents all the tags.
  • The ORDER BY time DESC clause returns query results based on timestamps in descending order.
  • The LIMIT 2 clause limits the number of returned points to two.
  • The OFFSET 2 clause excludes the first two average values from the query results.
  • The SLIMIT 1 clause limits the number of series returned to one.

If the preceding statement does not include the OFFSET 2 clause, the query returns the first two average values of the query results.

  1. name: h2o_feet
  2. tags: location=coyote_creek
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:36:00Z 7.303
  6. 2015-08-18T00:24:00Z 7.5675

SOFFSET clause

The SOFFSET<N> clause excludes the first N series from the query results.

Syntax

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

Description

N indicates that the first N series are excluded from the query results. If you need to use the SLIMIT clause in your query, you must include the SLIMIT clause in your query and place the SLIMIT clause before the SOFFSET clause. If a query has the SOFFSET clause but does not have the SLIMIT clause, inconsistent query results may be returned.

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

Examples

Specify first N series to be excluded from the query results

  1. > SELECT "water_level" FROM "h2o_feet" GROUP BY * SLIMIT 1 SOFFSET 1
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time water_level
  5. ---- -----------
  6. 2015-08-18T00:00:00Z 2.064
  7. 2015-08-18T00:06:00Z 2.116
  8. [...]
  9. 2015-09-18T21:36:00Z 5.066
  10. 2015-09-18T21:42:00Z 4.938

The query returns the data for the series that are associated with the h2o_feet measurement and the location = santa_monica tag. If the preceding statement does not include the SOFFSET 1 clause, the query returns the data for the series associated with the h2o_feet measurement and the location = coyote_creek tag.

Specify first N series to be excluded from the query results, and include multiple clauses

  1. > 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
  2. name: h2o_feet
  3. tags: location=santa_monica
  4. time mean
  5. ---- ----
  6. 2015-08-18T00:12:00Z 2.077
  7. 2015-08-18T00:00:00Z 2.09

The query in this example includes multiple clauses and is complex. The following section explains each clause that is included in the query.

  • The SELECT clause includes an InfluxQL function.
  • The FROM clause specifies a measurement.
  • The WHERE clause specifies the time range.
  • The GROUP BY clause groups query results based on all the tags and a 12-minute interval. The asterisk (*) represents all the tags.
  • The ORDER BY time DESC clause returns query results based on timestamps in descending order.
  • The LIMIT 2 clause limits the number of returned points to two.
  • The OFFSET 2 clause excludes the first two average values from the query results.
  • The SLIMIT 1 clause limits the number of series returned to one.
  • The SOFFSET 1 clause excludes the first one series from the query results.

If the preceding statement does not include the SOFFSET 1 clause, the query returns the result of the other series.

  1. name: h2o_feet
  2. tags: location=coyote_creek
  3. time mean
  4. ---- ----
  5. 2015-08-18T00:12:00Z 7.8245
  6. 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®.