Type to search.

ES|QL TBUCKET function

Serverless

Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud.

Availability

  • Generally available

    This functionality is generally available and ready for production usage.

Serverless interfaces and procedures might differ from classic Elastic Stack deployments.

Stack 9.2+

The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats.

Availability

  • Generally available since 9.2

    This functionality is generally available and ready for production usage.

Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments.

This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.

Creates timestamp-based buckets aligned to calendar boundaries.

Embedded
Embedded
buckets
Target number of buckets, or desired bucket size. When a number is provided, the actual bucket size is derived from from/to or the @timestamp range in the query filter The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. . When a duration or period is provided, it is used as the explicit bucket size.
from
Start of the range. Required with a numeric buckets when no @timestamp range is in the query filter The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. .
to
End of the range. Required with a numeric buckets when no @timestamp range is in the query filter The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. .
options Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Generally available This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.

(Optional) Additional options as function named parameters.

Creates groups of values - buckets - out of a @timestamp attribute. The size of the buckets can be provided directly as a duration or period. Alternatively, the bucket size can be chosen based on a recommended count and a range The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. . When using ES|QL in Kibana, the range can be derived automatically from the @timestamp filter that Kibana adds to the query.

buckets from to options result
date_period date
date_period date_nanos
integer date date named parameters date
integer date date named parameters date_nanos
integer date date date
integer date date date_nanos
integer date keyword named parameters date
integer date keyword named parameters date_nanos
integer date keyword date
integer date keyword date_nanos
integer date text named parameters date
integer date text named parameters date_nanos
integer date text date
integer date text date_nanos
integer keyword date named parameters date
integer keyword date named parameters date_nanos
integer keyword date date
integer keyword date date_nanos
integer keyword keyword named parameters date
integer keyword keyword named parameters date_nanos
integer keyword keyword date
integer keyword keyword date_nanos
integer keyword text named parameters date
integer keyword text named parameters date_nanos
integer keyword text date
integer keyword text date_nanos
integer text date named parameters date
integer text date named parameters date_nanos
integer text date date
integer text date date_nanos
integer text keyword named parameters date
integer text keyword named parameters date_nanos
integer text keyword date
integer text keyword date_nanos
integer text text named parameters date
integer text text named parameters date_nanos
integer text text date
integer text text date_nanos
time_duration date
time_duration date_nanos
include_empty_buckets Elastic Cloud Serverless projects are autoscaled environments, fully managed by Elastic and available on Elastic Cloud. Availability Generally available This functionality is generally available and ready for production usage. Serverless interfaces and procedures might differ from classic Elastic Stack deployments. The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Planned We plan to add this functionality in a future Elastic Stack update. Subject to changes. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.

(boolean) When true, empty buckets (filled with default aggregate values) are output across the from..to range (from inclusive, to exclusive). Requires the from and to arguments. Defaults to false.

TBUCKET can work in two modes: one in which the size of the bucket is computed based on a buckets count recommendation and a range, and another in which the bucket size is provided directly as a duration or period.

Using a target number of buckets, a start of a range, and an end of a range, TBUCKET picks an appropriate bucket size to generate the target number of buckets or fewer. For example, asking for at most 3 buckets over a 2 hour range results in hourly buckets The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. :

ES|QL 
FROM sample_data
| STATS count = COUNT(*) BY bucket = TBUCKET(3, "2023-10-23T12:00:00Z", "2023-10-23T14:00:00Z")
| SORT bucket
count:long bucket:datetime
2 2023-10-23T12:00:00.000Z
5 2023-10-23T13:00:00.000Z

The goal isn't to provide exactly the target number of buckets, it's to pick a range that people are comfortable with that provides at most the target number of buckets.

Asking for more buckets can result in a finer granularity. For example, asking for at most 20 buckets in the same range results in 10-minute buckets The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats. Availability Generally available since 9.4 This functionality is generally available and ready for production usage. Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments. This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes. :

ES|QL 
FROM sample_data
| STATS count = COUNT(*) BY bucket = TBUCKET(20, "2023-10-23T12:00:00Z", "2023-10-23T14:00:00Z")
| SORT bucket
count:long bucket:datetime
1 2023-10-23T12:10:00.000Z
1 2023-10-23T12:20:00.000Z
1 2023-10-23T13:30:00.000Z
4 2023-10-23T13:50:00.000Z
Note

TBUCKET does not filter any rows. It only uses the provided range to pick a good bucket size. For rows with a value outside of the range, it returns a bucket value that corresponds to a bucket outside the range. Combine TBUCKET with WHERE to filter rows.

If the desired bucket size is known in advance, simply provide it as the first argument, leaving the range out:

ES|QL 
FROM sample_data
| STATS min = MIN(@timestamp), max = MAX(@timestamp) BY bucket = TBUCKET(1 hour)
| SORT min
min:datetime max:datetime bucket:datetime
2023-10-23T12:15:03.360Z 2023-10-23T12:27:28.948Z 2023-10-23T12:00:00.000Z
2023-10-23T13:33:34.937Z 2023-10-23T13:55:01.543Z 2023-10-23T13:00:00.000Z
Note

When providing the bucket size, it must be a time duration or date period. Also the reference is epoch, which starts at 0001-01-01T00:00:00Z.

The bucket size can also be provided as a string:

ES|QL 
FROM sample_data
| STATS min = MIN(@timestamp), max = MAX(@timestamp) BY bucket = TBUCKET("1 hour")
| SORT min
min:datetime max:datetime bucket:datetime
2023-10-23T12:15:03.360Z 2023-10-23T12:27:28.948Z 2023-10-23T12:00:00.000Z
2023-10-23T13:33:34.937Z 2023-10-23T13:55:01.543Z 2023-10-23T13:00:00.000Z

Stack Planned

The Elastic Stack includes Elastic's core products such as Elasticsearch, Kibana, Logstash, and Beats.

Availability

  • Planned

    We plan to add this functionality in a future Elastic Stack update. Subject to changes.

Unless stated otherwise on the page, this functionality is available when your Elastic Stack is deployed on Elastic Cloud Hosted, Elastic Cloud Enterprise, Elastic Cloud on Kubernetes, and self-managed environments.

This documentation corresponds to the latest patch available for each minor version. If you're not using the latest patch, check the release notes for changes.

By default, TBUCKET only returns buckets that contain at least one row. Set the include_empty_buckets option to true to also return the empty buckets in the from..to range, filled with default aggregate values:

ES|QL 
TS k8s
| STATS max_cost = MAX(MAX_OVER_TIME(network.cost))
  BY time_bucket = TBUCKET(6, "2024-05-10T00:00:00Z", "2024-05-10T00:30:00Z", {"include_empty_buckets": true})
max_cost:double time_bucket:datetime
12.375 2024-05-10T00:00:00.000Z
12.5 2024-05-10T00:05:00.000Z
11.875 2024-05-10T00:10:00.000Z
12.375 2024-05-10T00:15:00.000Z
11.875 2024-05-10T00:20:00.000Z
null 2024-05-10T00:25:00.000Z