# Date histogram based on two date-type variables **URL:** \ **Category:** Kibana\ **Created:** [August 7, 2019, 8:38pm UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323 "2019-08-07T20:38:16Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![jcaballero](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jcaballero](https://discuss.elastic.co/u/jcaballero)\ **Post date:** [August 7, 2019, 8:38pm UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/1 "2019-08-07T20:38:16Z")
My apologies in advance if title of this topic is obscure, and I am not using right nomenclature. I am quite new to Kibana... Using Kibana 5.4 - I have in ElasticSearch some data, representing some type of events. **Each _event_ contains two date-type fields: _'start'_ and _'end'_.** - The content of _'start'_ is also the content of _'@timestamp'_. - Let's say that **an _event_ is _'active'_ between its _'start'_ and _'end'_ times**. I would like to **create a Date Histogram, where each bin** (or is it called bucket?) **contains the total number of events that were active during that period of time**. In other words, if the Nth bin goes from time t\_N to time t\_N+1, it should contains the number of events that 1. start \< t\_N+1 2. end \> t\_N Is this doable? Thanks a lot in advance. Jose ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [August 8, 2019, 9:31am UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/2 "2019-08-08T09:31:05Z")
Hi @jcaballero Sounds like this post is answering a pretty similar question using scripted fields to achieve that: [Display concurrency in data on Kibana](https://discuss.elastic.co/t/display-concurrency-in-data-on-kibana/26006/4) Let me know whether you can tweak the script to make it fit your use case ---
**Author:** ![ppisljar](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppisljar/32/11588_2.png) [@ppisljar](https://discuss.elastic.co/u/ppisljar)\ **Post date:** [August 8, 2019, 9:51am UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/3 "2019-08-08T09:51:26Z")
something like this will be hard, but i guess it could be possible by not doing date histogram but rather filters aggregation. then define a filter for each of your ranges (like start \>= now-7h AND end\<= now-6h) ---
**Author:** ![jcaballero](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jcaballero](https://discuss.elastic.co/u/jcaballero)\ **Post date:** [August 8, 2019, 11:41am UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/4 "2019-08-08T11:41:13Z")
Thanks for your prompt responses. BTW, I just noticed my original post wasn’t correct describing which events go under each bin, but I am pretty sure you got it. I just fixed it. ---
**Author:** ![jcaballero](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jcaballero](https://discuss.elastic.co/u/jcaballero)\ **Post date:** [August 19, 2019, 6:27pm UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/5 "2019-08-19T18:27:54Z")
hi @flash1293 I am having a look to that post you mentioned. I am still a little bit confused about it. If I try to run the \_search query example from that post, directly from command line, to start, I get an error message ``` curl -X GET "myhost:1234/concurrency/_search?pretty" -H 'Content-Type: application/json' -d' > { > "aggs": { > "my_histo": { > "date_histogram": { > "script": "start = doc['start'].value; duration = doc['duration'].value * 1000; l = []; for (long i = 0; i < duration; i += interval) { l.add(start + i); }; return l;", > "params": { > "interval": 3600 > }, > "interval": "hour" > } > } > } > } > ' { "error" : { "root_cause" : [ { "type" : "illegal_argument_exception", "reason" : "[date_histogram] unknown field [params], parser not found" } ], "type" : "illegal_argument_exception", "reason" : "[date_histogram] unknown field [params], parser not found" }, "status" : 400 } ``` I guess it was not supposed to be executed from command line. How should I use that snippet (or similar) when building a "Date Histogram"? Thanks, Jose ---
**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\ **Post date:** [August 20, 2019, 9:02am UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/6 "2019-08-20T09:02:34Z")
Hey, it looks like the script is written in groovy, which got deprecated. The go-to solution is now painless. The script and it's params should go into their own nested objects like shown here: [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#\_using\_a\_script\_to\_aggregate\_by\_day\_of\_the\_week](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#_using_a_script_to_aggregate_by_day_of_the_week) ```auto { "aggs": { "my_histo": { "date_histogram": { "script": { "lang": "painless", "source": "def start = doc['start'].value.millis; def duration = doc['duration'].value * 1000; def l = []; for (long i = 0; i < duration; i += params.interval) { l.add(start + i); } return l;", "params": { "interval": 3600 } }, "interval": "hour" } } } } ``` Of course the "start" and "duration" fields have to exist on your index for that to work ---
**Author:** ![jcaballero](https://avatars.discourse-cdn.com/v4/letter/j/278dde/32.png) [@jcaballero](https://discuss.elastic.co/u/jcaballero)\ **Post date:** [August 20, 2019, 2:42pm UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/7 "2019-08-20T14:42:35Z")
hi @flash1293 Thanks a lot for your prompt response. I will keep investigating. Maybe I don't have the right version of Kibana and/or ElasticSearch. Still unsure how to make it work. I created an index and put some data in it, following the example from the another thread, as: ``` curl -X PUT "my.es.host:1234/concurrency?pretty" -H 'Content-Type: application/json' -d' { "mappings": { "logs": { "properties": { "start": { "type": "date" }, "duration": { "type": "long" } } } } } ' curl -X PUT "my.es.host:1234/concurrency/logs/1?pretty" -H 'Content-Type: application/json' -d' { "start": "2015-07-12T09:14:01Z", "duration": 10000 } ' curl -X PUT "my.es.host:1234/concurrency/logs/2?pretty" -H 'Content-Type: application/json' -d' { "start": "2015-07-12T10:20:50Z", "duration": 20000 } ' curl -X PUT "my.es.host:1234/concurrency/logs/3?pretty" -H 'Content-Type: application/json' -d' { "start": "2015-07-12T12:00:42Z", "duration": 5000 } ' ``` I then I tried to copy & paste your painless snippet into the Kibana Visualization (after removing the .millis, which I guess was introduced in a later version). ![kibana_visual](https://us1.discourse-cdn.com/elastic/original/3X/0/0/002c27d01bb5ad408d1abd496b3966bd65c8ceca.png) It fails, I get this explanation in the logs: ``` Caused by: org.elasticsearch.common.io.stream.NotSerializableExceptionWrapper: class_cast_exception: java.util.ArrayList cannot be cast to java.lang.Number ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 17, 2019, 10:05pm UTC](https://discuss.elastic.co/t/date-histogram-based-on-two-date-type-variables/194323/9 "2019-09-17T22:05:23Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.