Date histogram in Elasticsearch

Introduction

A date histogram groups data by time intervals, like days or months. It helps you see trends over time.

You want to count how many events happened each day.

You need to analyze sales totals by month.

You want to track website visits per hour.

You want to compare data grouped by weeks.

You want to visualize time-based data in charts.

Syntax

Elasticsearch

{
  "aggs": {
    "group_by_date": {
      "date_histogram": {
        "field": "date_field",
        "calendar_interval": "interval",
        "format": "date_format"
      }
    }
  }
}

field is the date field in your data.

calendar_interval sets the time bucket, like day, week, or month.

Examples

Groups sales by each month.

Elasticsearch

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

Groups website visits by day and formats the date.

Elasticsearch

{
  "aggs": {
    "visits_per_day": {
      "date_histogram": {
        "field": "visit_time",
        "calendar_interval": "day",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Sample Program

This query counts logins grouped by each week, showing the start date of the week.

Elasticsearch

{
  "size": 0,
  "aggs": {
    "logins_per_week": {
      "date_histogram": {
        "field": "login_date",
        "calendar_interval": "week",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Important Notes

Use calendar_interval for natural time units like day, week, month.

You can also use fixed_interval for exact time spans like 30d (30 days).

The format option helps display dates in a readable way.

Summary

Date histogram groups data by time intervals.

Use it to analyze trends over days, weeks, or months.

Set the date field and interval to get time-based buckets.

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