For time field, create buckets by day for a terms visualization

(Jennifer Young) #1

I want to create a visualization like the one in the image attached, where, instead of a straight time histogram, the dates are sorted in descending order by how many records there are.

My current time field goes all the way into the milliseconds. What do I need to do to make this work?

(Spencer Alger) #2

The ideal way to do this is by indexing the "day" value on each document, and then aggregating on it using a terms aggregation. It's a performant way of getting this information, but does require tweaking your data-ingestion pipeline and re-indexing historical records.

An alternate method is to use a scripted field to produce the "day" value and then aggregating on it just like you would if the value was actually in the document.

The day value can be faked using a kibana scripted field, though I don't have the equation on hand. Essentially, you have to modulo the milliseconds value of your time field to learn the number of days it has since January 1 1970, and then multiply that number by the number of milliseconds in a day. Of course this won't be taking into account time-shifts (like daylight savings time, or leap year) but it will get you most of the way there.

Soon (in the next major version ideally) elasticsearch will have it's own scripting language that will support extracting information like "day" from date fields accurately. With that available you would simply need to update your script to return the proper date for each time.

This method comes with a performance cost when running the aggregation, and as mentioned is not truly accurate. So keep that in mind :slight_smile:

(system) #3