== Looking at Time
If search is the most popular activity in Elasticsearch, building date histograms must be the second most popular.((("date histograms, building")))((("histograms", "building date histograms")))((("aggregations", "building date histograms from"))) Why would you want to use a date histogram?
Imagine your data has a timestamp.((("time, analytics over", id="ixtimeanalyze"))) It doesn't matter what the data is--Apache log events, stock buy/sell transaction dates, baseball game times--anything with a timestamp can benefit from the date histogram. When you have a timestamp, you often want to build metrics that are expressed _over time:
- How many cars sold each month this year?
- What was the price of this stock for the last 12 hours?
- What was the average latency of our website every hour in the last week?
While regular histograms are often represented as bar charts, date histograms
tend to be converted into line graphs representing time series.((("analytics", "over time"))) Many
companies use Elasticsearch solely for analytics over time series data. The date_histogram bucket is their bread and butter.
The date_histogram bucket works((("buckets", "date_histogram"))) similarly to the regular histogram. Rather
than building buckets based on a numeric field representing numeric ranges,
it builds buckets based on time ranges. Each bucket is therefore defined as a
certain calendar size (for example, 1 month or 2.5 days).
[role="pagebreak-before"] .Can a Regular Histogram Work with Dates?
Technically, yes.((("histogram bucket", "dates and"))) A regular histogram bucket will work with dates. However,
it is not calendar-aware. With the date_histogram, you can specify intervals
such as 1 month, which knows that February is shorter than December. The
date_histogram also has the advantage of being able to work with time zones,
which allows you to customize graphs to the time zone of the user, not the server.
The regular histogram will interpret dates as numbers, which means you must specify intervals in terms of milliseconds. And the aggregation doesn't know about calendar intervals, which makes it largely useless for dates.
Our first example ((("line charts, building from aggregations")))will build a simple line chart to answer this question: how many cars were sold each month?
[source,js]
GET /cars/transactions/_search?search_type=count { "aggs": { "sales": { "date_histogram": { "field": "sold", "interval": "month", <1> "format": "yyyy-MM-dd" <2> } } }
}
// SENSE: 300_Aggregations/35_date_histogram.json
<1> The interval is requested in calendar terminology (for example, one month per bucket). // "pretty"-> "readable by humans". mention that otherwise get back ms-since-epoch?
<2> We provide a date format so that bucket keys are pretty.
Our query has a single aggregation, which builds a bucket
per month. This will give us the number of cars sold in each month. An additional
format parameter is provided so the buckets have "pretty" keys. Internally,
dates are simply represented as a numeric value. This tends to make UI designers
grumpy, however, so a prettier format can be specified using common date formatting.
The response is both expected and a little surprising (see if you can spot the surprise):
[source,js]
{ ... "aggregations": { "sales": { "buckets": [ { "key_as_string": "2014-01-01", "key": 1388534400000, "doc_count": 1 }, { "key_as_string": "2014-02-01", "key": 1391212800000, "doc_count": 1 }, { "key_as_string": "2014-05-01", "key": 1398902400000, "doc_count": 1 }, { "key_as_string": "2014-07-01", "key": 1404172800000, "doc_count": 1 }, { "key_as_string": "2014-08-01", "key": 1406851200000, "doc_count": 1 }, { "key_as_string": "2014-10-01", "key": 1412121600000, "doc_count": 1 }, { "key_as_string": "2014-11-01", "key": 1414800000000, "doc_count": 2 } ] ...
}
The aggregation is represented in full. As you can see, we have buckets
that represent months, a count of docs in each month, and our pretty key_as_string.
[[_returning_empty_buckets]] === Returning Empty Buckets
Notice something odd about that last response?
Yep, that's right.((("aggregations", "returning empty buckets")))((("buckets", "empty, returning"))) We are missing a few months! By default, the date_histogram
(and histogram too) returns only buckets that have a nonzero
document count.
This means your histogram will be a minimal response. Often, this is not the behavior you want. For many applications, you would like to dump the response directly into a graphing library without doing any post-processing.
Essentially, we want buckets even if they have a count of zero. We can set two additional parameters that will provide this behavior:
[source,js]
GET /cars/transactions/_search?search_type=count { "aggs": { "sales": { "date_histogram": { "field": "sold", "interval": "month", "format": "yyyy-MM-dd", "min_doc_count" : 0, <1> "extended_bounds" : { <2> "min" : "2014-01-01", "max" : "2014-12-31" } } } }
}
// SENSE: 300_Aggregations/35_date_histogram.json
<1> This parameter forces empty buckets to be returned.
<2> This parameter forces the entire year to be returned.
The two additional parameters will force the response to return all months in the
year, regardless of their doc count.((("min_doc_count parameter"))) The min_doc_count is very understandable:
it forces buckets to be returned even if they are empty.
The extended_bounds parameter requires a little explanation.((("extended_bounds parameter"))) The min_doc_count
parameter forces empty buckets to be returned, but by default Elasticsearch will return only buckets that are between the minimum and maximum value in your data.
So if your data falls between April and July, you'll have buckets representing only those months (empty or otherwise). To get the full year, we need to tell Elasticsearch that we want buckets even if they fall before the minimum value or after the maximum value.
The extended_bounds parameter does just that. Once you add those two settings,
you'll get a response that is easy to plug straight into your graphing libraries
and give you a graph like <
[[date-histo-ts1]] .Cars sold over time image::images/elas_29in01.png["Cars sold over time"]
=== Extended Example
Just as we've seen a dozen times already, buckets can be nested in buckets for more-sophisticated behavior.((("buckets", "nested in other buckets", "extended example")))((("aggregations", "extended example"))) For illustration, we'll build an aggregation that shows the total sum of prices for all makes, listed by quarter. Let's also calculate the sum of prices per individual make per quarter, so we can see which car type is bringing in the most money to our business:
[source,js]
GET /cars/transactions/_search?search_type=count { "aggs": { "sales": { "date_histogram": { "field": "sold", "interval": "quarter", <1> "format": "yyyy-MM-dd", "min_doc_count" : 0, "extended_bounds" : { "min" : "2014-01-01", "max" : "2014-12-31" } }, "aggs": { "per_make_sum": { "terms": { "field": "make" }, "aggs": { "sum_price": { "sum": { "field": "price" } <2> } } }, "total_sum": { "sum": { "field": "price" } <3> } } } }
}
// SENSE: 300_Aggregations/35_date_histogram.json
<1> Note that we changed the interval from month to quarter.
<2> Calculate the sum per make.
<3> And the total sum of all makes combined together.
This returns a (heavily truncated) response:
[source,js]
{ .... "aggregations": { "sales": { "buckets": [ { "key_as_string": "2014-01-01", "key": 1388534400000, "doc_count": 2, "total_sum": { "value": 105000 }, "per_make_sum": { "buckets": [ { "key": "bmw", "doc_count": 1, "sum_price": { "value": 80000 } }, { "key": "ford", "doc_count": 1, "sum_price": { "value": 25000 } } ] } }, ...
}
We can take this response and put it into a graph, ((("line charts, building from aggregations")))((("bar charts, building from aggregations")))showing a line chart for
total sale price, and a bar chart for each individual make (per quarter), as shown in <
[[date-histo-ts2]] .Sales per quarter, with distribution per make image::images/elas_29in02.png["Sales per quarter, with distribution per make"]
=== The Sky's the Limit
These were obviously simple examples, but the sky really is the limit
when it comes to charting aggregations. ((("dashboards", "building from aggregations")))((("Kibana", "dashboard in"))) For example, <
[[kibana-img]] .Kibana--a real time analytics dashboard built with aggregations image::images/elas_29in03.png["Kibana - a real time analytics dashboard built with aggregations"]
Because of the real-time nature of aggregations, dashboards like this are easy to query, manipulate, and interact with. This makes them ideal for nontechnical employees and analysts who need to analyze the data but cannot build a Hadoop job.
To build powerful dashboards like Kibana, however, you'll likely need some of the more advanced concepts such as scoping, filtering, and sorting aggregations. ((("time, analytics over", startref ="ix_timeanalyze")))