# How to aggregate data by date range **URL:** \ **Category:** Elasticsearch\ **Created:** [September 4, 2015, 9:35am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666 "2015-09-04T09:35:13Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![z\_w\_w1981](https://avatars.discourse-cdn.com/v4/letter/z/41988e/32.png) [@z\_w\_w1981](https://discuss.elastic.co/u/z_w_w1981)\ **Post date:** [September 4, 2015, 9:35am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666/1 "2015-09-04T09:35:13Z")
Hi, I'm a fresh to ElasticSearch, I created the environment and insert some sample date to test, my data is very simple (just 2 rows from the Apache log): { "message" =\> "199.201.64.129 - - [19/Aug/2015:07:06:25 +0800] "GET /v2/brands/12 HTTP/1.1" 200 1031 "-" "Mode/2.0.7 (iPhone Simulator; iOS 8.3; Scale/2.00)"", "@version" =\> "1", "@timestamp" =\> "2015-08-18T23:06:25.000Z", "host" =\> "zhaoweiweideMacBook-Pro.local", "path" =\> "/Users/zhaoweiwei/logs/test/testLog.log", "clientip" =\> "199.201.64.129", "ident" =\> "-", "auth" =\> "-", "timestamp" =\> "19/Aug/2015:07:06:25 +0800", "verb" =\> "GET", "request" =\> "/v2/brands/12", "httpversion" =\> "1.1", "response" =\> "200", "bytes" =\> "1031", "referrer" =\> ""-"", "agent" =\> ""Mode/2.0.7 (iPhone Simulator; iOS 8.3; Scale/2.00)"" } { "message" =\> "211.144.202.170 - - [20/Aug/2015:07:06:25 +0800] "GET /v2/brands/12 HTTP/1.1" 200 1031 "-" "Mode/2.0.7 (iPhone Simulator; iOS 8.3; Scale/2.00)"", "@version" =\> "1", "@timestamp" =\> "2015-08-19T23:06:25.000Z", "host" =\> "zhaoweiweideMacBook-Pro.local", "path" =\> "/Users/zhaoweiwei/logs/test/testLog.log", "clientip" =\> "211.144.202.170", "ident" =\> "-", "auth" =\> "-", "timestamp" =\> "20/Aug/2015:07:06:25 +0800", "verb" =\> "GET", "request" =\> "/v2/brands/12", "httpversion" =\> "1.1", "response" =\> "200", "bytes" =\> "1031", "referrer" =\> ""-"", "agent" =\> ""Mode/2.0.7 (iPhone Simulator; iOS 8.3; Scale/2.00)"" } I want to get distinct client ip in a date range, So I execute the below query: curl -XGET '[http://localhost:9200/logstash-\*/\_search?search\_type=count](http://localhost:9200/logstash-*/_search?search_type=count)' -d ' { "aggregations": { "distinct\_value": { "terms": { "field": "clientip" }, "aggregations": { "dates\_between": { "range": { "field": "timestamp", "ranges": [ { "format": "dd/MMM/yyyy:HH:mm:ss", "gte": "20/08/2015:00:00:00", "lte": "20/08/2015:00:00:00"} ] } } } } } }' but it always throw exception: "reason":"ClassCastException[org.elasticsearch.index.fielddata.plain.PagedBytesIndexFieldData cannot be cast to org.elasticsearch.index.fielddata.IndexNumericFieldData]", How to implement the query? Thanks in advance! ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [September 4, 2015, 11:13am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666/2 "2015-09-04T11:13:34Z")
This is because your "timestamp" field is mapped as a String field and not as a Date field. There are two solutions you could use to fix this (both require re-indexing your 2 records into a new index): 1. Explicitly [map the "timestamp" field as a date field](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-core-types.html#date) when you [create the index](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-create-index.html#mappings). You will need to specify the [date format](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html) in the mapping as you are using a non-standard format. 2. Use the [date filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-date.html) in your Logstash config to convert the field to a date in Logstash before it gets sent to Elasticsearch The query you are using looks to be oke so if you implement one of the two solutions above you should get what you are after. Hope that helps ---
**Author:** ![z\_w\_w1981](https://avatars.discourse-cdn.com/v4/letter/z/41988e/32.png) [@z\_w\_w1981](https://discuss.elastic.co/u/z_w_w1981)\ **Post date:** [September 5, 2015, 4:22am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666/3 "2015-09-05T04:22:02Z")
Thanks for Reply. I already added date { match =\> ["timestamp" , "dd/MMM/yyyy:HH:mm:ss Z"] timezone =\> "Asia/Shanghai" } in the configuration file, I think it should convert 'timestamp' to date format just like your second proposal. Currently, I use below query to get the result with no exception, but result looks wrong. First I run the below query: curl -XGET '[http://localhost:9200/logstash-\*/\_search?search\_type=count](http://localhost:9200/logstash-*/_search?search_type=count)' -d ' { "query": { "filtered": { "query": { "match\_all": {} }, "filter": { "range": { "timestamp": { "gte": "18/08/2015", "lte": "19/08/2015", "format": "dd/MMM/yyyy" } } } } }, "aggs": { "group\_by\_device\_id": { "terms": { "field": "clientip" } } } }' the result is '211.144.202.170', my timestamp is shanghai time, why the result looks like get UTC time value? If it's UTC time, I changed the above 'range' part to: "range": { "timestamp": { "gte": "19/08/2015:00:00:00", "lte": "19/08/2015:23:59:59", "format": "dd/MMM/yyyy:HH:mm:ss" } } It should show the result, but there is nothing... Do you know what's the problem of my query? Thanks a lot! ---
**Author:** ![z\_w\_w1981](https://avatars.discourse-cdn.com/v4/letter/z/41988e/32.png) [@z\_w\_w1981](https://discuss.elastic.co/u/z_w_w1981)\ **Post date:** [September 5, 2015, 10:09am UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666/4 "2015-09-05T10:09:09Z")
Sorry, I got your point, after I added: 'target =\> "timestamp"' to parse the timestamp to date, it works fine. Thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:51pm UTC](https://discuss.elastic.co/t/how-to-aggregate-data-by-date-range/28666/5 "2017-07-05T23:51:59Z")