Date histogram in Elasticsearch - Step-by-Step Execution

Concept Flow - Date histogram

Start: Receive Query

↓

Parse Date Histogram Aggregation

↓

Group Documents by Date Interval

↓

Count Documents per Interval

↓

Return Buckets with Counts

↓

End

The date histogram groups documents by fixed time intervals and counts how many fall into each interval.

Execution Sample

Elasticsearch

{
  "aggs": {
    "sales_over_time": {
      "date_histogram": {
        "field": "date",
        "calendar_interval": "month"
      }
    }
  }
}

This query groups documents by month based on the 'date' field and counts documents in each month.

Execution Table

Step	Action	Input Data	Result
1	Receive query with date_histogram aggregation	{"date": ["2023-01-10", "2023-01-20", "2023-02-05", "2023-02-25", "2023-03-15"]}	Query parsed
2	Parse date_histogram aggregation	calendar_interval = 'month'	Interval set to month
3	Group documents by month	Dates grouped into buckets: Jan 2023, Feb 2023, Mar 2023	Buckets created
4	Count documents in each bucket	Jan: 2 docs, Feb: 2 docs, Mar: 1 doc	Counts calculated
5	Return aggregation result	Buckets with counts	{"buckets": [{"key_as_string": "2023-01-01T00:00:00.000Z", "doc_count": 2}, {"key_as_string": "2023-02-01T00:00:00.000Z", "doc_count": 2}, {"key_as_string": "2023-03-01T00:00:00.000Z", "doc_count": 1}]}

💡 All documents grouped and counted by month, aggregation complete

Variable Tracker

Variable	Start	After Step 3	After Step 4	Final
buckets	empty	{"2023-01": [], "2023-02": [], "2023-03": []}	{"2023-01": 2, "2023-02": 2, "2023-03": 1}	{"buckets": [{"key_as_string": "2023-01-01T00:00:00.000Z", "doc_count": 2}, {"key_as_string": "2023-02-01T00:00:00.000Z", "doc_count": 2}, {"key_as_string": "2023-03-01T00:00:00.000Z", "doc_count": 1}]}

Key Moments - 2 Insights

Why does the date histogram group dates into the first day of each month?

Because the calendar_interval is 'month', Elasticsearch groups all dates in the same month under the bucket key representing the first day of that month, as shown in execution_table step 5.

What happens if a document's date does not fall into any bucket?

All documents with dates within the range are grouped; if a date is outside the data range or missing, it won't appear in any bucket. This is implied in step 3 where only existing dates are grouped.

Visual Quiz - 3 Questions

Look at the execution_table, how many documents are counted in the February 2023 bucket?

A 1

B 3

C 2

D 0

💡 Hint

Check the 'Count documents in each bucket' row in execution_table step 4

At which step does Elasticsearch group documents by the date interval?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look for the action describing grouping in execution_table

If the calendar_interval changed to 'week', how would the buckets variable change after step 3?

A Buckets would remain grouped by month

B Buckets would group dates by week instead of month

C Buckets would be empty

D Buckets would group by day

💡 Hint

Refer to variable_tracker and how grouping depends on calendar_interval

Concept Snapshot

Date histogram groups documents by fixed time intervals.
Use 'calendar_interval' to set interval like 'month', 'week', or 'day'.
Each bucket key is the start of the interval.
Counts show how many documents fall into each bucket.
Useful for time series data aggregation.

Full Transcript

The date histogram aggregation in Elasticsearch groups documents by fixed time intervals such as months or weeks. The query specifies the field containing dates and the calendar interval. Elasticsearch parses the query, groups documents by the interval, counts how many documents fall into each group, and returns buckets with counts. Each bucket key represents the start of the interval, for example, the first day of the month. This helps analyze data trends over time by counting documents in each time slice.

Practice

1. What is the primary purpose of a date_histogram aggregation in Elasticsearch?

easy

A. To group documents into buckets based on time intervals

B. To sort documents by date in ascending order

C. To filter documents by a specific date range

D. To count the total number of documents in the index

Solution

1. Step 1: Understand the function of date_histogram

   The date_histogram aggregation groups documents into buckets based on specified time intervals like days or months.

2. Step 2: Compare with other options

   Sorting, filtering, and counting are different operations; only grouping by time intervals matches date_histogram.

3. Final Answer:

   To group documents into buckets based on time intervals -> Option A

4. Quick Check:

   date_histogram = group by time [OK]

Hint: Remember: date_histogram groups data by time intervals [OK]

Common Mistakes:

• Confusing date_histogram with sorting
• Thinking it filters documents
• Assuming it counts all documents without grouping

2. Which of the following is the correct syntax to create a date_histogram aggregation on the field timestamp with a daily interval?

easy

A. "date_histogram": { "field": "timestamp", "interval": "day" }

B. "date_histogram": { "field": "timestamp", "interval": "daily" }

C. "date_histogram": { "field": "timestamp", "interval": "1day" }

D. "date_histogram": { "field": "timestamp", "interval": "days" }

Solution

1. Step 1: Check valid interval values

   Elasticsearch expects intervals like "day", "week", "month" as strings without plural or numeric prefixes.

2. Step 2: Validate each option

   "date_histogram": { "field": "timestamp", "interval": "day" } uses "day" which is correct. Options B, C, and D use invalid interval formats.

3. Final Answer:

   "date_histogram": { "field": "timestamp", "interval": "day" } -> Option A

4. Quick Check:

   Use singular interval names like "day" [OK]

Hint: Use singular interval names like "day", not "daily" or "days" [OK]

Common Mistakes:

• Using plural interval names like "days"
• Using malformed numeric prefixes like "1day"
• Using incorrect interval strings like "daily"

3. Given this Elasticsearch aggregation query snippet:

{
  "aggs": {
    "sales_over_time": {
      "date_histogram": {
        "field": "sale_date",
        "calendar_interval": "month"
      }
    }
  }
}

What will be the result of this aggregation?

medium

A. Buckets grouping sales by each day based on sale_date

B. Buckets grouping sales by each month based on sale_date

C. A syntax error due to missing interval

D. Buckets grouping sales by year ignoring months

Solution

1. Step 1: Identify the interval type

   The query uses calendar_interval": "month", which groups data by calendar months.

2. Step 2: Understand the effect on buckets

   Each bucket will contain documents with sale_date in the same month.

3. Final Answer:

   Buckets grouping sales by each month based on sale_date -> Option B

4. Quick Check:

   calendar_interval month = monthly buckets [OK]

Hint: calendar_interval "month" means monthly buckets [OK]

Common Mistakes:

• Confusing calendar_interval with fixed_interval
• Assuming daily buckets instead of monthly
• Thinking missing interval causes syntax error

4. You wrote this date_histogram aggregation:

{
  "aggs": {
    "by_week": {
      "date_histogram": {
        "field": "created_at",
        "interval": "week"
      }
    }
  }
}

But Elasticsearch returns an error. What is the likely cause?

medium

A. Missing "time_zone" parameter causes error

B. Field "created_at" is missing from the index

C. Using deprecated "interval" instead of "calendar_interval" or "fixed_interval"

D. The interval value "week" is invalid

Solution

1. Step 1: Check interval parameter usage

   Since Elasticsearch 7.2, "interval" is deprecated; use "calendar_interval" or "fixed_interval" instead.

2. Step 2: Identify error cause

   Using "interval" causes an error; other options are less likely or cause different errors.

3. Final Answer:

   Using deprecated "interval" instead of "calendar_interval" or "fixed_interval" -> Option C

4. Quick Check:

   Use calendar_interval or fixed_interval, not interval [OK]

Hint: Replace deprecated "interval" with "calendar_interval" or "fixed_interval" [OK]

Common Mistakes:

• Ignoring deprecation and using "interval"
• Assuming missing field causes this specific error
• Thinking "week" is invalid interval

5. You want to create a date_histogram aggregation that groups documents by week but aligns weeks to start on Monday instead of Sunday. Which parameter should you add to your aggregation?

hard

A. "calendar_interval": "week"

B. "time_zone": "UTC"

C. "week_start": "monday"

D. "offset": "1d"

Solution

1. Step 1: Understand week alignment in date_histogram

   Elasticsearch weeks start on Sunday by default; to shift start day, use the "offset" parameter.

2. Step 2: Identify correct parameter

   Setting "offset": "1d" shifts buckets by one day, making weeks start on Monday.

3. Step 3: Check other options

   "time_zone" adjusts time zones, "week_start" is not a valid parameter, and "calendar_interval" defines interval but not start day.

4. Final Answer:

   "offset": "1d" -> Option D

5. Quick Check:

   Use offset to shift week start day [OK]

Hint: Use offset "1d" to start weeks on Monday [OK]

Common Mistakes:

• Trying to use non-existent "week_start" parameter
• Assuming time_zone changes week start day
• Not using offset to shift buckets