Auto-interval date histogram

Similar to the date histogram aggregation, in which you must specify an interval, the auto_date_histogram is a multi-bucket aggregation that automatically creates date histogram buckets based on the number of buckets you provide and the time range of your data. The actual number of buckets returned is always less than or equal to the number of buckets you specify. This aggregation is particularly useful when you are working with time-series data and want to visualize or analyze data over different time intervals without manually specifying the interval size.

Intervals

The bucket interval is chosen based on the collected data to ensure that the number of returned buckets is less than or equal to the requested number.

The following table lists the possible returned intervals for each time unit.

UnitIntervals
SecondsMultiples of 1, 5, 10, and 30
MinutesMultiples of 1, 5, 10, and 30
HoursMultiples of 1, 3, and 12
DaysMultiples of 1 and 7
MonthsMultiples of 1 and 3
YearsMultiples of 1, 5, 10, 20, 50, and 100

If an aggregation returns too many buckets (for example, daily buckets), OpenSearch will automatically reduce the number of buckets to ensure a manageable result. Instead of returning the exact number of requested daily buckets, it will reduce them by a factor of about 1/7. For example, if you ask for 70 buckets but the data contains too many daily intervals, OpenSearch might return only 10 buckets, grouping the data into larger intervals (such as weeks) to avoid an overwhelming number of results. This helps optimize the aggregation and prevent excessive detail when too much data is available.

Example

In the following example, you’ll search an index containing blog posts.

First, create a mapping for this index and specify the date_posted field as the date type:

  1. PUT blogs
  2. {
  3.   "mappings" : {
  4.     "properties" :  {
  5.       "date_posted" : {
  6.         "type" : "date",
  7.         "format" : "yyyy-MM-dd"
  8.       }
  9.     }
  10.   }
  11. }

copy

Next, index the following documents into the blogs index:

  1. PUT blogs/_doc/1
  2. {
  3.   "name": "Semantic search in OpenSearch",
  4.   "date_posted": "2022-04-17"
  5. }

copy

  1. PUT blogs/_doc/2
  2. {
  3.   "name": "Sparse search in OpenSearch",
  4.   "date_posted": "2022-05-02"
  5. }

copy

  1. PUT blogs/_doc/3
  2. {
  3.   "name": "Distributed tracing with Data Prepper",
  4.   "date_posted": "2022-04-25"
  5. }

copy

  1. PUT blogs/_doc/4
  2. {
  3.   "name": "Observability in OpenSearch",
  4.   "date_posted": "2023-03-23"
  5. }

copy

To use the auto_date_histogram aggregation, specify the field containing the date or timestamp values. For example, to aggregate blog posts by date_posted into two buckets, send the following request:

  1. GET /blogs/_search
  2. {
  3.   "size": 0,
  4.   "aggs": {
  5.     "histogram": {
  6.       "auto_date_histogram": {
  7.         "field": "date_posted",
  8.         "buckets": 2
  9.       }
  10.     }
  11.   }
  12. }

copy

The response shows that the blog posts were aggregated into two buckets. The interval was automatically set to 1 year, with all three 2022 blog posts collected in one bucket and the 2023 blog post in another:

  1. {
  2.   "took": 20,
  3.   "timed_out": false,
  4.   "_shards": {
  5.     "total": 1,
  6.     "successful": 1,
  7.     "skipped": 0,
  8.     "failed": 0
  9.   },
  10.   "hits": {
  11.     "total": {
  12.       "value": 4,
  13.       "relation": "eq"
  14.     },
  15.     "max_score": null,
  16.     "hits": []
  17.   },
  18.   "aggregations": {
  19.     "histogram": {
  20.       "buckets": [
  21.         {
  22.           "key_as_string": "2022-01-01",
  23.           "key": 1640995200000,
  24.           "doc_count": 3
  25.         },
  26.         {
  27.           "key_as_string": "2023-01-01",
  28.           "key": 1672531200000,
  29.           "doc_count": 1
  30.         }
  31.       ],
  32.       "interval": "1y"
  33.     }
  34.   }
  35. }

Returned buckets

Each bucket contains the following information:

  1. {
  2.   "key_as_string": "2023-01-01",
  3.   "key": 1672531200000,
  4.   "doc_count": 1
  5. }

In OpenSearch, dates are internally stored as 64-bit integers representing timestamps in milliseconds since the epoch. In the aggregation response, each bucket key is returned as such a timestamp. The key_as_string value shows the same timestamp but formatted as a date string based on the format parameter. The doc_count field contains the number of documents in the bucket.

Parameters

Auto-interval date histogram aggregations accept the following parameters.

ParameterData typeDescription
fieldStringThe field on which to aggregate. The field must contain the date or timestamp values. Either field or script is required.
bucketsIntegerThe desired number of buckets. The returned number of buckets is less than or equal to the desired number. Optional. Default is 10.
minimum_intervalStringThe minimum interval to be used. Specifying a minimum interval can make the aggregation process more efficient. Valid values are year, month, day, hour, minute, and second. Optional.
time_zoneStringSpecifies to use a time zone other than the default (UTC) for bucketing and rounding. You can specify the time_zone parameter as a UTC offset, such as -04:00, or an IANA time zone ID, such as America/New_York. Optional. Default is UTC. For more information, see Time zone.
formatStringThe format for returning dates representing bucket keys. Optional. Default is the format specified in the field mapping. For more information, see Date format.
scriptStringA document-level or value-level script for aggregating values into buckets. Either field or script is required.
missingStringSpecifies how to handle documents in which the field value is missing. By default, such documents are ignored. If you specify a date value in the missing parameter, all documents in which the field value is missing are collected into the bucket with the specified date.

Date format

If you don’t specify the format parameter, the format defined in the field mapping is used (as seen in the preceding response). To modify the format, specify the format parameter:

  1. GET /blogs/_search
  2. {
  3.   "size": 0,
  4.   "aggs": {
  5.     "histogram": {
  6.       "auto_date_histogram": {
  7.         "field": "date_posted",
  8.         "format": "yyyy-MM-dd HH:mm:ss"
  9.       }
  10.     }
  11.   }
  12. }

copy

The key_as_string field is now returned in the specified format:

  1. {
  2.   "key_as_string": "2023-01-01 00:00:00",
  3.   "key": 1672531200000,
  4.   "doc_count": 1
  5. }

Alternatively, you can specify one of the built-in date formats:

  1. GET /blogs/_search
  2. {
  3.   "size": 0,
  4.   "aggs": {
  5.     "histogram": {
  6.       "auto_date_histogram": {
  7.         "field": "date_posted",
  8.         "format": "basic_date_time_no_millis"
  9.       }
  10.     }
  11.   }
  12. }

copy

The key_as_string field is now returned in the specified format:

  1. {
  2.   "key_as_string": "20230101T000000Z",
  3.   "key": 1672531200000,
  4.   "doc_count": 1
  5. }

Time zone

By default, dates are stored and processed in UTC. The time_zone parameter allows you to specify a different time zone for bucketing. You can specify the time_zone parameter as a UTC offset, such as -04:00, or an IANA time zone ID, such as America/New_York.

As an example, index the following documents into an index:

  1. PUT blogs1/_doc/1
  2. {
  3.   "name": "Semantic search in OpenSearch",
  4.   "date_posted": "2022-04-17T01:00:00.000Z"
  5. }

copy

  1. PUT blogs1/_doc/2
  2. {
  3.   "name": "Sparse search in OpenSearch",
  4.   "date_posted": "2022-04-17T04:00:00.000Z"
  5. }

copy

First, run an aggregation without specifying a time zone:

  1. GET /blogs1/_search
  2. {
  3.   "size": 0,
  4.   "aggs": {
  5.     "histogram": {
  6.       "auto_date_histogram": {
  7.         "field": "date_posted",
  8.         "buckets": 2,
  9.         "format": "yyyy-MM-dd HH:mm:ss"
  10.       }
  11.     }
  12.   }
  13. }

copy

The response contains two 3-hour buckets, starting at midnight UTC on April 17, 2022:

  1. {
  2.   "took": 6,
  3.   "timed_out": false,
  4.   "_shards": {
  5.     "total": 1,
  6.     "successful": 1,
  7.     "skipped": 0,
  8.     "failed": 0
  9.   },
  10.   "hits": {
  11.     "total": {
  12.       "value": 2,
  13.       "relation": "eq"
  14.     },
  15.     "max_score": null,
  16.     "hits": []
  17.   },
  18.   "aggregations": {
  19.     "histogram": {
  20.       "buckets": [
  21.         {
  22.           "key_as_string": "2022-04-17 01:00:00",
  23.           "key": 1650157200000,
  24.           "doc_count": 1
  25.         },
  26.         {
  27.           "key_as_string": "2022-04-17 04:00:00",
  28.           "key": 1650168000000,
  29.           "doc_count": 1
  30.         }
  31.       ],
  32.       "interval": "3h"
  33.     }
  34.   }
  35. }

Now, specify a time_zone of -02:00:

  1. GET /blogs1/_search
  2. {
  3.   "size": 0,
  4.   "aggs": {
  5.     "histogram": {
  6.       "auto_date_histogram": {
  7.         "field": "date_posted",
  8.         "buckets": 2,
  9.         "format": "yyyy-MM-dd HH:mm:ss",
  10.         "time_zone": "-02:00"
  11.       }
  12.     }
  13.   }
  14. }

The response contains two buckets in which the start time is shifted by 2 hours and starts at 23:00 on April 16, 2022:

  1. {
  2.   "took": 17,
  3.   "timed_out": false,
  4.   "_shards": {
  5.     "total": 1,
  6.     "successful": 1,
  7.     "skipped": 0,
  8.     "failed": 0
  9.   },
  10.   "hits": {
  11.     "total": {
  12.       "value": 2,
  13.       "relation": "eq"
  14.     },
  15.     "max_score": null,
  16.     "hits": []
  17.   },
  18.   "aggregations": {
  19.     "histogram": {
  20.       "buckets": [
  21.         {
  22.           "key_as_string": "2022-04-16 23:00:00",
  23.           "key": 1650157200000,
  24.           "doc_count": 1
  25.         },
  26.         {
  27.           "key_as_string": "2022-04-17 02:00:00",
  28.           "key": 1650168000000,
  29.           "doc_count": 1
  30.         }
  31.       ],
  32.       "interval": "3h"
  33.     }
  34.   }
  35. }

When using time zones with daylight saving time (DST) changes, the sizes of buckets that are near the transition may differ slightly from the sizes of neighboring buckets.