Using the Date Histogram Aggregation for Time Series Analysis

1. Why the date histogram aggregation is essential for time series

The date histogram aggregation is the standard way to group documents in Elasticsearch by a date field into evenly sized time periods. Orders per day, error rates per hour, sessions per week: wherever a metric needs to be shown over time, a date histogram builds the buckets into which metric aggregations such as sum, avg or cardinality get attached. Unlike a plain terms aggregation on a rounded date field, the date_histogram aggregation handles bucket alignment, calendar logic and ordering automatically.

The practical benefit shows up mostly in dashboards and reporting systems where users pick a time range and expect a clean time series, without backend code manually computing bucket boundaries. A date histogram also handles edge cases that are easy to get wrong by hand: varying month lengths, leap years and the switch between summer and winter time. Ignore these and you end up with bar charts where weekends suddenly shift or where the February bar looks twice as wide as the January bar.

The following sections walk through the date histogram aggregation from basic syntax through the differences between calendar_interval and fixed_interval to a complete sales dashboard with several sub-aggregations. Every example uses real query syntax as it runs against a production Elasticsearch or OpenSearch cluster.

2. Basics: syntax and buckets of the date histogram aggregation

A minimal date histogram aggregation needs three things: the date field via the field attribute, the bucket size via calendar_interval or fixed_interval, and optionally an output format via format. Every result bucket contains the bucket key as epoch milliseconds, a formatted key_as_string, and doc_count, the number of documents in that period. These buckets are sorted chronologically by default, which makes time series charts directly consumable without extra client-side sorting.

The query context matters: a date histogram aggregation without a restricting range filter aggregates over the entire index. In practice it is therefore almost always combined with a bool query that restricts the time range via a range filter on the same field. Without that filter the aggregation still runs correctly, but is unnecessarily expensive because documents outside the displayed range are searched too.

GET /orders/_search
{
  "size": 0,
  "query": {
    "range": {
      "order_date": { "gte": "2026-01-01", "lt": "2026-07-01" }
    }
  },
  "aggs": {
    "orders_per_day": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd",
        "min_doc_count": 0
      }
    }
  }
}

3. Calendar intervals vs fixed intervals in detail

The most important choice for any date histogram aggregation is calendar_interval versus fixed_interval, because both options give different results once the buckets get larger. calendar_interval understands calendar semantics: "month" produces buckets that start exactly on the first of the month and span 28 to 31 days depending on the month. fixed_interval instead counts in fixed millisecond multiples, so "30d" is always exactly 30 days long, regardless of where the month starts or ends.

For reports like "revenue per month", calendar_interval is almost always the right choice, because business users expect a monthly bar that matches the calendar month, not an arbitrary 30-day slice. For technical metrics like "requests per fixed 5-minute window", fixed_interval fits better, because what matters there is the exact, unchanging window width rather than calendar logic. A date histogram with calendar_interval also supports units like week, quarter and year that do not exist for fixed_interval at all, because their length depends on the calendar.

An often overlooked effect: a fixed_interval of "1d" is not the same as calendar_interval "day" once a daylight saving transition falls within the observed range. The fixed_interval day always has exactly 86,400,000 milliseconds, while the calendar day on a transition day is 23 or 25 hours long. In a date histogram spanning several months, fixed_interval slowly shifts the visible day boundaries relative to local time, which shows up in dashboards as seemingly wrong daily revenue.

GET /orders/_search
{
  "size": 0,
  "aggs": {
    "revenue_per_month_calendar": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "month",
        "format": "yyyy-MM"
      }
    },
    "revenue_per_30_days_fixed": {
      "date_histogram": {
        "field": "order_date",
        "fixed_interval": "30d",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

4. Handling timezones correctly

Elasticsearch always stores date fields internally as UTC, regardless of the timezone the data was originally captured in. Without an explicit setting, a date histogram aggregation therefore also computes its bucket boundaries in UTC. For a shop with customers in Germany that means: a daily bucket "2026-07-24" starts at 00:00 UTC, which is already 02:00 local time under Central European Summer Time. Orders placed between 00:00 and 02:00 local time incorrectly end up in the previous day's bucket.

The time_zone parameter solves this by shifting the bucket boundaries of the date_histogram aggregation into a named timezone before calendar logic is applied. It is important to use an IANA timezone such as "Europe/Berlin" rather than a fixed UTC offset like "+02:00", because only the named timezone automatically switches between summer and winter time. A fixed offset would produce exactly the wrong shift in winter that you were trying to fix in summer. The correct query pattern with "time_zone": "Europe/Berlin" is shown in section 9 side by side with the faulty variant that omits the timezone.

A second timezone effect affects international shops serving customers across several timezones. A single date histogram can only apply one timezone at a time. Anyone wanting to evaluate customers by local wall-clock time needs either a stored timezone per document with downstream computation, or several parallel aggregations per target region. For most reporting use cases, however, a single fixed business timezone is entirely sufficient, as long as it is used consistently across the whole dashboard.

5. Sub-aggregations: revenue and metrics per period

The actual analysis only emerges once a metric aggregation is attached under each bucket of the date histogram aggregation. sum computes the total of a numeric field per period, for example daily revenue. avg returns the average order value, cardinality the number of distinct customers per day. These sub-aggregations run independently for every bucket and deliver a complete multi-dimensional time series in a single query.

For sales dashboards, combining sum, avg and value_count is especially common, because a single request delivers revenue, average order value and order count per period. In addition, a bucket_selector pipeline aggregation can be attached to retroactively keep only buckets with revenue above a threshold, for example to highlight outlier days in a separate panel.

GET /orders/_search
{
  "size": 0,
  "query": {
    "range": { "order_date": { "gte": "now-90d/d", "lte": "now/d" } }
  },
  "aggs": {
    "sales_per_day": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "day",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 0
      },
      "aggs": {
        "daily_revenue": { "sum": { "field": "grand_total" } },
        "avg_order_value": { "avg": { "field": "grand_total" } },
        "unique_customers": { "cardinality": { "field": "customer_id" } }
      }
    }
  }
}

6. Gap-free time series: min_doc_count and extended_bounds

By default, a date histogram aggregation skips buckets with no documents, leaving gaps in the time series on days without revenue. For most line charts that is undesirable, because a missing data point looks different from a data point whose value is zero. The min_doc_count parameter set to 0 forces the aggregation to return empty buckets too, which enables continuous time series without any client-side post-processing.

A second, related problem concerns the edges of the time series: when the chosen range has no documents at the start or end, min_doc_count alone does not create buckets at those edges, because the aggregation derives its bucket boundaries from the data actually present. extended_bounds solves that by explicitly providing min and max, ensuring that a date histogram spans exactly from the desired start to the desired end date, even if the first or last days are completely empty.

GET /orders/_search
{
  "size": 0,
  "aggs": {
    "sales_per_day_no_gaps": {
      "date_histogram": {
        "field": "order_date",
        "calendar_interval": "day",
        "time_zone": "Europe/Berlin",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "2026-01-01",
          "max": "2026-07-24"
        }
      },
      "aggs": {
        "daily_revenue": { "sum": { "field": "grand_total" } }
      }
    }
  }
}

7. Performance on large indices

A date histogram aggregation over billions of documents is inherently efficient, because Elasticsearch runs the aggregation over the inverted index and the doc-values structure without loading individual documents. What still matters for performance is the restricting range filter in the query part: without it the aggregation searches the entire dataset, with it the candidate set shrinks to the actually relevant period, which drastically reduces the search space especially on time-based indices with index rotation.

With very fine-grained buckets over long periods, for example hourly buckets over several years, the number of returned buckets quickly climbs into the tens of thousands, significantly inflating the response and making it expensive to render client-side. A proven pattern is therefore to dynamically couple the granularity of a date histogram to the requested range: days for a month, weeks for a quarter, months for a year. Combining this with rolled-up aggregation indices, where daily sums are already precomputed, further reduces the computational cost for far-back time ranges.

8. Sales-over-time dashboards in practice

In a typical sales dashboard, a date histogram is combined with several parallel facets: a top level by time period, and below it optionally a terms aggregation by product category or sales channel. This combination delivers both the overall trend and the breakdown by category per period in a single query, enabling a stacked bar chart client-side without extra requests.

For comparison views like "this month vs. last month", instead of two separate date_histogram aggregations it is often better to run a single date histogram over the combined range and split it client-side, because the buckets are then guaranteed to be consistently aligned. For real-time dashboards that reload every few seconds, a request_cache at the index level pays off when the range rarely changes, along with a deliberately chosen preference routing to bundle repeated requests onto the same shard replicas and increase the cache hit rate.

9. Common mistakes and debugging

The most common mistake with a date histogram aggregation is missing time_zone, even though the application expects local day boundaries. This shows up as an apparently random shift of orders between two adjacent daily bars, most noticeably shortly after midnight. The second common mistake is confusing fixed_interval and calendar_interval for monthly or yearly reports, resulting in bars of inconsistent width and incorrectly labeled periods.

// WRONG: no time_zone, no extended_bounds, gaps show as missing days
{
  "date_histogram": {
    "field": "order_date",
    "calendar_interval": "day"
  }
}

// RIGHT: explicit time zone, no gaps, aligned to the full report range
{
  "date_histogram": {
    "field": "order_date",
    "calendar_interval": "day",
    "time_zone": "Europe/Berlin",
    "min_doc_count": 0,
    "extended_bounds": { "min": "2026-01-01", "max": "2026-07-24" }
  }
}

A third mistake concerns the date field itself: if order_date is mapped as text instead of date, the date_histogram aggregation refuses the field outright and fails with a mapping error. Anyone running a date histogram against a field with the wrong type must fix the mapping before a reindex, since date_histogram only works on genuine date or date_nanos fields and performs no implicit string-to-date conversion at query time.

10. Summary

The date histogram aggregation is the central tool for time series in Elasticsearch and OpenSearch, because it combines bucket creation, calendar logic and ordering into a single declarative query. calendar_interval delivers business-correct monthly, weekly and yearly buckets, fixed_interval delivers exactly equal-length technical time windows. The time_zone parameter is practically always required for locally oriented reports, because Elasticsearch computes internally in UTC.

min_doc_count set to 0 and extended_bounds produce gap-free time series without any client-side post-processing, while a restricting range filter in the query part protects performance even on very large indices. Combine these building blocks and you get sales dashboards that show correct numbers across month boundaries, leap years and daylight saving transitions, instead of producing seemingly random shifts.

11. FAQ: Date Histogram for Time Series Analysis