ES|QL time series aggregation functions

The first STATS under a TS source command supports the following time series aggregation functions:

[preview] ABSENT_OVER_TIME
[preview] AVG_OVER_TIME
[preview] COUNT_OVER_TIME
[preview] COUNT_DISTINCT_OVER_TIME
[preview] FIRST_OVER_TIME
[preview] LAST_OVER_TIME
[preview] MAX_OVER_TIME
[preview] MIN_OVER_TIME
[preview] PRESENT_OVER_TIME
[preview] RATE
[preview] SUM_OVER_TIME

`ABSENT_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the absence of a field in the output result over time range.

Supported types

field	result
boolean	boolean
cartesian_point	boolean
cartesian_shape	boolean
date	boolean
date_nanos	boolean
double	boolean
geo_point	boolean
geo_shape	boolean
geohash	boolean
geohex	boolean
geotile	boolean
integer	boolean
ip	boolean
keyword	boolean
long	boolean
text	boolean
unsigned_long	boolean
version	boolean

Example

		TS k8s
| WHERE cluster == "prod" AND pod == "two"
| STATS events_received = max(absent_over_time(events_received)) BY pod, time_bucket = tbucket(2 minute)
		
	

events_received:boolean	pod:keyword	time_bucket:datetime
false	two	2024-05-10T00:02:00.000Z
false	two	2024-05-10T00:08:00.000Z
true	two	2024-05-10T00:10:00.000Z
true	two	2024-05-10T00:12:00.000Z

`AVG_OVER_TIME`

Stack Planned

Syntax

Parameters

number: Expression that outputs values to average.

Description

Calculates the average over time of a numeric field.

Supported types

number	result
double	double
integer	double
long	double

Example

		TS k8s
| STATS max_cost=max(avg_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

max_cost:double	cluster:keyword	time_bucket:datetime
12.375	prod	2024-05-10T00:17:00.000Z
12.375	qa	2024-05-10T00:01:00.000Z
12.25	prod	2024-05-10T00:19:00.000Z
12.0625	qa	2024-05-10T00:06:00.000Z

`COUNT_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the count over time value of a field.

Supported types

field	result
boolean	long
cartesian_point	long
cartesian_shape	long
date	long
date_nanos	long
double	long
geo_point	long
geo_shape	long
geohash	long
geohex	long
geotile	long
integer	long
ip	long
keyword	long
long	long
text	long
unsigned_long	long
version	long

Example

		TS k8s
| STATS count=count(count_over_time(network.cost))
  BY cluster, time_bucket = bucket(@timestamp,1minute)
		
	

count:long	cluster:keyword	time_bucket:datetime
3	staging	2024-05-10T00:22:00.000Z
3	prod	2024-05-10T00:20:00.000Z
3	prod	2024-05-10T00:19:00.000Z

`COUNT_DISTINCT_OVER_TIME`

Stack Planned

Syntax

Parameters

field
precision: Precision threshold. Refer to AGG-COUNT-DISTINCT-APPROXIMATE. The maximum supported value is 40000. Thresholds above this number will have the same effect as a threshold of 40000. The default value is 3000.

Description

Calculates the count of distinct values over time for a field.

Supported types

field	precision	result
boolean	integer	long
boolean	long	long
boolean	unsigned_long	long
boolean		long
date	integer	long
date	long	long
date	unsigned_long	long
date		long
date_nanos	integer	long
date_nanos	long	long
date_nanos	unsigned_long	long
date_nanos		long
double	integer	long
double	long	long
double	unsigned_long	long
double		long
integer	integer	long
integer	long	long
integer	unsigned_long	long
integer		long
ip	integer	long
ip	long	long
ip	unsigned_long	long
ip		long
keyword	integer	long
keyword	long	long
keyword	unsigned_long	long
keyword		long
long	integer	long
long	long	long
long	unsigned_long	long
long		long
text	integer	long
text	long	long
text	unsigned_long	long
text		long
version	integer	long
version	long	long
version	unsigned_long	long
version		long

Example

		TS k8s
| STATS distincts=count_distinct(count_distinct_over_time(network.cost)),
        distincts_imprecise=count_distinct(count_distinct_over_time(network.cost, 100))
  BY cluster, time_bucket = bucket(@timestamp,1minute)
		
	

distincts:long	distincts_imprecise:long	cluster:keyword	time_bucket:datetime
3	3	qa	2024-05-10T00:17:00.000Z
3	3	qa	2024-05-10T00:15:00.000Z
3	3	prod	2024-05-10T00:09:00.000Z

`FIRST_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the earliest value of a field, where recency determined by the @timestamp field.

Supported types

field	result
double	double
integer	integer
long	long

Example

		TS k8s
| STATS max_cost=max(first_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

max_cost:double	cluster:keyword	time_bucket:datetime
12.375	prod	2024-05-10T00:17:00.000Z
12.375	qa	2024-05-10T00:01:00.000Z
12.25	prod	2024-05-10T00:19:00.000Z

`LAST_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the latest value of a field, where recency determined by the @timestamp field.

Supported types

field	result
double	double
integer	integer
long	long

Example

		TS k8s
| STATS max_cost=max(last_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

max_cost:double	cluster:keyword	time_bucket:datetime
12.5	staging	2024-05-10T00:09:00.000Z
12.375	prod	2024-05-10T00:17:00.000Z
12.375	qa	2024-05-10T00:06:00.000Z
12.375	qa	2024-05-10T00:01:00.000Z

`MAX_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the maximum over time value of a field.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long Stack Planned	unsigned_long
version	version

Example

		TS k8s
| STATS cost=sum(max_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

cost:double	cluster:keyword	time_bucket:datetime
32.75	qa	2024-05-10T00:17:00.000Z
32.25	staging	2024-05-10T00:09:00.000Z
31.75	qa	2024-05-10T00:06:00.000Z
29.0	prod	2024-05-10T00:19:00.000Z

`MIN_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the minimum over time value of a field.

Supported types

field	result
boolean	boolean
date	date
date_nanos	date_nanos
double	double
integer	integer
ip	ip
keyword	keyword
long	long
text	keyword
unsigned_long Stack Planned	unsigned_long
version	version

Example

		TS k8s
| STATS cost=sum(min_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

cost:double	cluster:keyword	time_bucket:datetime
29.0	prod	2024-05-10T00:19:00.000Z
27.625	qa	2024-05-10T00:06:00.000Z
24.25	qa	2024-05-10T00:09:00.000Z

`PRESENT_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the presence of a field in the output result over time range.

Supported types

field	result
boolean	boolean
cartesian_point	boolean
cartesian_shape	boolean
date	boolean
date_nanos	boolean
double	boolean
geo_point	boolean
geo_shape	boolean
geohash	boolean
geohex	boolean
geotile	boolean
integer	boolean
ip	boolean
keyword	boolean
long	boolean
text	boolean
unsigned_long	boolean
version	boolean

Example

		TS k8s
| WHERE cluster == "prod" AND pod == "two"
| STATS events_received = max(present_over_time(events_received)) BY pod, time_bucket = tbucket(2 minute)
		
	

events_received:boolean	pod:keyword	time_bucket:datetime
true	two	2024-05-10T00:02:00.000Z
true	two	2024-05-10T00:08:00.000Z
false	two	2024-05-10T00:10:00.000Z
false	two	2024-05-10T00:12:00.000Z

`RATE`

Stack Planned

Syntax

Parameters

field

Description

Calculates the rate of a counter field.

Supported types

field	result
counter_double	double
counter_integer	double
counter_long	double

Example

		TS k8s
| STATS max(rate(network.total_bytes_in)) BY time_bucket = bucket(@timestamp,5minute)

max(rate(network.total_bytes_in)): double	time_bucket:date
6.980660660660663	2024-05-10T00:20:00.000Z
23.702205882352942	2024-05-10T00:15:00.000Z

`SUM_OVER_TIME`

Stack Planned

Syntax

Parameters

field

Description

Calculates the sum over time value of a field.

Supported types

field	result
double	double
integer	long
long	long

Example

		TS k8s
| STATS sum_cost=sum(sum_over_time(network.cost)) BY cluster, time_bucket = bucket(@timestamp,1minute)

sum_cost:double	cluster:keyword	time_bucket:datetime
67.625	qa	2024-05-10T00:17:00.000Z
65.75	staging	2024-05-10T00:09:00.000Z