---
title: ES|QL TS command
description: 
url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/ts
products:
  - Elasticsearch
---

# ES|QL TS command
<applies-to>
  - Elastic Cloud Serverless: Preview
  - Elastic Stack: Preview since 9.2
</applies-to>

The `TS` source command is similar to the [`FROM`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/from)
source command, with the following key differences:
- Targets only [time series indices](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3028/manage-data/data-store/data-streams/time-series-data-stream-tsds)
- Enables the use of [time series aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions) inside the
  [STATS](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/stats-by) command


## Syntax

```esql
TS index_pattern [METADATA fields]
```


## Parameters

<definitions>
  <definition term="index_pattern">
    A list of indices, data streams or aliases. Supports wildcards and date math.
  </definition>
  <definition term="fields">
    A comma-separated list of [metadata fields](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/esql-metadata-fields) to retrieve.
  </definition>
</definitions>


## Description

The `TS` source command enables time series semantics and adds support for
[time series aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions) to the `STATS` command, such as
[`AVG_OVER_TIME()`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/avg_over_time),
or [`RATE`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/rate).
These functions are implicitly evaluated per time series, then aggregated by group using a secondary aggregation
function. For an example, refer to [Calculate the rate of search requests per host](#calculate-the-rate-of-search-requests-per-host).
This paradigm (a pair of aggregation functions) is standard for time series
querying. For supported inner (time series) functions per
[metric type](https://docs-v3-preview.elastic.dev/elastic/docs-builder/docs/3028/manage-data/data-store/data-streams/time-series-data-stream-tsds#time-series-metric), refer to
[ES|QL time series aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions). These functions also
apply to downsampled data, with the same semantics as for raw data.
<note>
  If a query is missing an inner (time series) aggregation function,
  [`LAST_OVER_TIME()`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions/last_over_time)
  is assumed and used implicitly. For example, two equivalent queries that return the average of the last memory usage values per time series are shown in [Aggregate with implicit LAST_OVER_TIME](#aggregate-with-implicit-last_over_time). To calculate the average memory usage across per-time-series averages, refer to [Calculate the average of per-time-series averages](#calculate-the-average-of-per-time-series-averages).
</note>

You can use [time series aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions)
directly in the `STATS` command (<applies-to>Elastic Stack: Preview since 9.3</applies-to>). The output will contain one aggregate value per time series and time bucket (if specified). For an example, refer to [Use time series aggregation functions directly](#use-time-series-aggregation-functions-directly).
You can also combine time series aggregation functions with regular [aggregation functions](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/aggregation-functions) such as `SUM()`, as outer aggregation functions. For examples, refer to [Combine SUM and RATE](#combine-sum-and-rate) and [Combine SUM and AVG_OVER_TIME](#combine-sum-and-avg_over_time).
However, using a time series aggregation function in combination with an inner time series function causes an error. For an example, refer to [Invalid query: nested time series functions](#invalid-query-nested-time-series-functions).

## Best practices

- Avoid aggregating multiple metrics in the same query when those metrics have different dimensional cardinalities.
  For example, in `STATS max(rate(foo)) + rate(bar))`, if `foo` and `bar` don't share the same dimension values, the rate
  for one metric will be null for some dimension combinations. Because the + operator returns null when either input
  is null, the entire result becomes null for those dimensions. Additionally, queries that aggregate a single metric
  can filter out null values more efficiently.
- Use the `TS` command for aggregations on time series data, rather than `FROM`. The `FROM` command is still available
  (for example, for listing document contents), but it's not optimized for processing time series data and may produce
  unexpected results.
- The `TS` command can't be combined with certain operations (such as
  [`FORK`](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/commands/fork)) before the `STATS` command is applied. Once `STATS` is
  applied, you can process the tabular output with any applicable ES|QL operations.
- Add a time range filter on `@timestamp` to limit the data volume scanned and improve query performance.
- Time series aggregations large result sets, especially if they involve many dimensions and small time buckets.
  The limits are updated accordingly, with the default result truncation size increased to 10,000 rows. For more
  information on the limits and how to adjust them, refer to
  [Result set size limitation](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/_snippets/common/result-set-size-limitation).


## Examples

The following examples demonstrate common time series query patterns using `TS`.

### Calculate the rate of search requests per host

Calculate the total rate of search requests (tracked by the `search_requests` counter) per host and hour. The `RATE()`
function is applied per time series in hourly buckets. These rates are summed for each
host and hourly bucket (since each host can map to multiple time series):
```esql
TS metrics
  | WHERE @timestamp >= now() - 1 hour
  | STATS SUM(RATE(search_requests)) BY TBUCKET(1 hour), host
```


### Aggregate with implicit LAST_OVER_TIME

The following two queries are equivalent, returning the average of the last memory usage values per time series. If a query is missing an inner (time series) aggregation function, `LAST_OVER_TIME()` is assumed and used implicitly:
```esql
TS metrics | STATS AVG(memory_usage)

TS metrics | STATS AVG(LAST_OVER_TIME(memory_usage))
```


### Calculate the average of per-time-series averages

This query calculates the average memory usage across per-time-series averages, rather than the average of all raw values:
```esql
TS metrics | STATS AVG(AVG_OVER_TIME(memory_usage))
```


### Use time series aggregation functions directly

You can use a [time series aggregation function](https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/esql/functions-operators/time-series-aggregation-functions) directly in `STATS` (<applies-to>Elastic Stack: Preview since 9.3</applies-to>):
```esql
TS metrics
| WHERE TRANGE(1 day)
| STATS RATE(search_requests) BY TBUCKET(1 hour)
```


### Combine SUM and RATE

Use `SUM` as the outer aggregation to sum counter rates across groups:
```esql
TS metrics | STATS SUM(RATE(search_requests)) BY host
```


### Combine SUM and AVG_OVER_TIME

Use `AVG_OVER_TIME` to compute per-time-series averages, then group the results by host and time bucket:
```esql
TS metrics
| WHERE @timestamp >= now() - 1 day
| STATS SUM(AVG_OVER_TIME(memory_usage)) BY host, TBUCKET(1 hour)
```


### Invalid query: nested time series functions

Using a time series aggregation function in combination with an inner time series function causes an error:
```esql
TS metrics | STATS AVG_OVER_TIME(RATE(memory_usage))
```