Elasticsearch Elasticsearch Group by Date

By Opster Team

Updated: Aug 27, 2023

| 2 min read

Introduction

Elasticsearch provides a robust feature set for data grouping based on date fields. This capability is particularly useful when dealing with time-series data or logs, where insights can be derived from the aggregation of data points over specific time intervals.

The key to date grouping in Elasticsearch is the Date Histogram Aggregation. This aggregation is similar to the standard Histogram, but it can only be used with date values. It groups the values of a specific date field into date or time intervals, such as minute, hour, day, week, month, quarter, or year.

The practical application of date grouping in Elasticsearch: A step-by-step guide

Step 1: Basic date histogram aggregation

The following example demonstrates a basic date histogram aggregation. It groups documents by the date field “timestamp”, using a one-day interval.

json
GET /_search
{
  "size": 0,
  "aggs": {
    "group_by_date": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day"
      }
    }
  }
}

Step 2: Advanced date histogram aggregation

You can also add sub-aggregations to the date histogram to further group the data. The following example groups documents by date, and then for each date, it groups documents by the “category” field.

json
GET /_search
{
  "size": 0,
  "aggs": {
    "group_by_date": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day"
      },
      "aggs": {
        "group_by_category": {
          "terms": {
            "field": "category"
          }
        }
      }
    }
  }
}

Step 3: Date histogram with time zone

Elasticsearch allows you to specify a time zone for the date histogram aggregation. This is useful when dealing with data from different time zones. The following example groups documents by date, taking into account the “America/New_York” time zone.

json
GET /_search
{
  "size": 0,
  "aggs": {
    "group_by_date": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "time_zone": "America/New_York"
      }
    }
  }
}

Step 4: Date histogram with ranges

In some cases, you might want to group documents by date ranges. Elasticsearch provides the Date Range Aggregation for this purpose. The following example groups documents into two date ranges: before and after January 1, 2023.

json
GET /_search
{
  "size": 0,
  "aggs": {
    "group_by_date_range": {
      "date_range": {
        "field": "timestamp",
        "ranges": [
          { "to": "2023-01-01" },
          { "from": "2023-01-01" }
        ]
      }
    }
  }
}

Step 5: Date histogram with missing values

If your data contains missing values for the date field, you can use the “missing” parameter to specify how these documents should be grouped. The following example groups documents with missing “timestamp” values into a separate bucket.

json
GET /_search
{
  "size": 0,
  "aggs": {
    "group_by_date": {
      "date_histogram": {
        "field": "timestamp",
        "calendar_interval": "day",
        "missing": "2000-01-01" // documents with missing "timestamp" will be grouped here
      }
    }
  }
}