# Aggregate count based on Time **URL:** \ **Category:** Kibana\ **Created:** [November 28, 2018, 7:27pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642 "2018-11-28T19:27:47Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![kb2295](https://avatars.discourse-cdn.com/v4/letter/k/4491bb/32.png) [@kb2295](https://discuss.elastic.co/u/kb2295)\ **Post date:** [November 28, 2018, 7:27pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/1 "2018-11-28T19:27:47Z")
I have an index in which each line has the field "name" and there are only about 15 names I'm dealing with. I also have a field called "lastUpdated" which is a of type Date. What I have is a pie chart of names by count. For example: "Alex" appears 200 times, "Charlie" appears 100 times etc. The problem is, I don't want names counted more than once if they appear in the same second of the "lastUpdated" timestamp. What I want is to get the count by timestamp. For example, if "Bob" appears 156 times in between the "lastUpdated" stamp of 12:00:00 and 12:00:01, I want "Bob" to be counted only once. So, if "lastUpdated" timestamp is between 12:00:00 and 12:00:01, and we have "Bob" 156 times, "Charlie" 100 times and "Alice" 20 times, each name will get counted only once (Alice =1, Bob =1, Charlie =1). Now, between 12:00:01 and 12:00:02 if Bob appears 2 times, Alice appears 50 times, the aggregated values should be (Alice =2, Bob =2, charlie=1). I simply want all of this to appear in a pie chart. Can anyone help me out with this? Thank you. ---
**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\ **Post date:** [November 28, 2018, 9:52pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/2 "2018-11-28T21:52:05Z")
Hey @kb2295, you can use the Unique Count aggregation similar to the following to get close to what you're looking for: ![27%20PM](https://us1.discourse-cdn.com/elastic/original/3X/b/c/bc9f85c7d039d730277fbcb03db6696d7bb0b976.png) However, the count are approximate and the following does a good job of explaining the details between the counts: [https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-metrics-cardinality-aggregation.html#\_counts\_are\_approximate](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/search-aggregations-metrics-cardinality-aggregation.html#_counts_are_approximate) ---
**Author:** ![kb2295](https://avatars.discourse-cdn.com/v4/letter/k/4491bb/32.png) [@kb2295](https://discuss.elastic.co/u/kb2295)\ **Post date:** [November 28, 2018, 10:13pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/3 "2018-11-28T22:13:23Z")
Yes, but my timestamps also include miliseconds. Is there a way to round to the nearest second and then aggregate it? ---
**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\ **Post date:** [November 28, 2018, 10:41pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/4 "2018-11-28T22:41:34Z")
@kb2295 you can use a Scripted Field to truncate the milliseconds and then do a Unique Count. The following scripted field will truncate the millisecond: `doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)` ---
**Author:** ![kb2295](https://avatars.discourse-cdn.com/v4/letter/k/4491bb/32.png) [@kb2295](https://discuss.elastic.co/u/kb2295)\ **Post date:** [November 29, 2018, 7:14pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/5 "2018-11-29T19:14:13Z")
The following error occurs when I use that script: _Error: Request to Elasticsearch failed: {"error":{"root\_cause":[{"type":"script\_exception","reason":"runtime error","script\_stack":["doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)"," ^---- HERE"],"script":"doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)","lang":"painless"}],"type":"search\_phase\_execution\_exception","reason":"all shards failed","phase":"fetch","grouped":true,"failed\_shards":[{"shard":0,"index":"fusion\_clearingtrade","node":"GcYQdh54S3a5Zvq8hkyEcg","reason":{"type":"script\_exception","reason":"runtime error","script\_stack":["doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)"," ^---- HERE"],"script":"doc['@timestamp'].value.minus(doc['@timestamp'].value.getMillisOfSecond(), ChronoUnit.MILLIS)","lang":"painless","caused\_by":{"type":"illegal\_argument\_exception","reason":"Unable to find dynamic method [getMillisOfSecond] with [0] arguments for class [java.lang.Long]."}}}]},_"status":500} ---
**Author:** ![kb2295](https://avatars.discourse-cdn.com/v4/letter/k/4491bb/32.png) [@kb2295](https://discuss.elastic.co/u/kb2295)\ **Post date:** [November 29, 2018, 7:20pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/6 "2018-11-29T19:20:08Z")
![image](https://us1.discourse-cdn.com/elastic/original/3X/8/4/84e4fe903f38915d2c530a7a8747a04a88cfca6f.png) ---
**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\ **Post date:** [November 29, 2018, 7:41pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/7 "2018-11-29T19:41:53Z")
> [@kb2295](#): > > Unable to find dynamic method [getMillisOfSecond] with [0] arguments for class [java.lang.Long]." Is your @timestamp field a Date type? What version of ES/Kibana are you running? ---
**Author:** ![kb2295](https://avatars.discourse-cdn.com/v4/letter/k/4491bb/32.png) [@kb2295](https://discuss.elastic.co/u/kb2295)\ **Post date:** [November 29, 2018, 7:48pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/8 "2018-11-29T19:48:43Z")
Yes, it's a date. I tried with another Date field too, didn't work. Version: 5.6.12 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 27, 2018, 7:48pm UTC](https://discuss.elastic.co/t/aggregate-count-based-on-time/158642/9 "2018-12-27T19:48:50Z")
Closed on December 27, 2018, 7:48pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.