# Date\_histogram over terms bucket

**URL:** <https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249>\
**Category:** Elasticsearch\
**Created:** [May 22, 2019, 2:17pm UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249 "2019-05-22T14:17:34Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Madala](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@Madala](https://discuss.elastic.co/u/Madala)\
**Post date:** [May 22, 2019, 2:17pm UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249/1 "2019-05-22T14:17:34Z")

</div>

Hi, I'm trying to create a date histogram on the buckets formed based on aggregations. But, some how I'm not getting any data on the date histogram. Please see below to understand the scenario.

> PUT view\_log  
> {  
> "mappings": {  
> "vcc-analytics":{  
> "properties": {  
> "session\_id": { "type": "keyword" },  
> "videoId": { "type": "text" },  
> "watchTime": { "type": "integer" },  
> "creationDate": {  
> "type": "date"  
> }  
> }  
> }  
> }  
> }
> 
> POST /view\_log/\_bulk  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s1", "creationDate":"2019-05-01T10:10:10", "watchTime":"0", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s1", "creationDate":"2019-05-01T10:10:40", "watchTime":"30", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s1", "creationDate":"2019-05-01T10:11:10", "watchTime":"60", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s1", "creationDate":"2019-05-01T10:11:40", "watchTime":"90", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s2", "creationDate":"2019-05-01T12:10:10", "watchTime":"0", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s2", "creationDate":"2019-05-01T12:10:40", "watchTime":"30", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s2", "creationDate":"2019-05-01T12:11:10", "watchTime":"60", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s2", "creationDate":"2019-05-01T12:11:40", "watchTime":"90", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s3", "creationDate":"2019-06-01T10:10:10", "watchTime":"0", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s3", "creationDate":"2019-06-01T10:10:40", "watchTime":"30", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s3", "creationDate":"2019-06-01T10:11:10", "watchTime":"60", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s3", "creationDate":"2019-06-01T10:11:40", "watchTime":"90", "videoId":"v-1"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s4", "creationDate":"2019-06-01T12:10:10", "watchTime":"0", "videoId":"v-2"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s4", "creationDate":"2019-06-01T12:10:40", "watchTime":"30", "videoId":"v-2"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s4", "creationDate":"2019-06-01T12:11:10", "watchTime":"60", "videoId":"v-2"}  
> {"index":{"\_index":"view\_log","\_type":"vcc-analytics"}}  
> {"session\_id":"s4", "creationDate":"2019-06-01T12:11:40", "watchTime":"90", "videoId":"v-2"}
> 
> GET view\_log/\_search  
> {
> 
> "size" : 0,  
> "aggs":{  
> "session" : {  
> "terms" : { "field" : "session\_id"},  
> "aggs": {  
> "max\_play\_time": {  
> "max": {"field" : "watchTime"}  
> },  
> "min\_creation\_time":{  
> "min":{"field": "creationDate"}  
> }  
> }  
> },  
> "sessions\_overr\_time":{  
> "date\_histogram": {  
> "field": "session\>min\_creation\_time",  
> "interval": "day"  
> }  
> }
> 
> ```
> }
> 
> ```
> 
> }

I'm expecting a response where for each give date I can see the number of sessions & their corresponding playtimes. Please help me with this.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 22, 2019, 2:46pm UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249/2 "2019-05-22T14:46:15Z")

</div>

Hi Madala,  
This and other forms of large-scale behavioural analysis are best performed using an [entity-centric index](https://twitter.com/elasticmark/status/1009380268409610240?s=20) rather than an event-centric index of raw logs.

---

<div class="post-metadata">

**Author:** ![Madala](https://avatars.discourse-cdn.com/v4/letter/m/ecccb3/32.png) [@Madala](https://discuss.elastic.co/u/Madala)\
**Post date:** [May 23, 2019, 7:45am UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249/3 "2019-05-23T07:45:36Z")

</div>

Thanks Mark! I'm new to elastic and I would like to clarify my understanding here. So, does that mean all the required querying or aggregations have to be made on entity-centric index which have to be built by pre-processing all the event-centric documents?

So, in my case I keep getting viewlog requests(events) that captures the video playback event data like played time until that time for every 30 secs. To create a entity centric document, I need to pre-process all the events for a session and create a single document with all the necessary attributes which can then be queried. Is my understanding correct here?

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [May 23, 2019, 9:40am UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249/4 "2019-05-23T09:40:45Z")

</div>

> [@Madala](#):
>
> Is my understanding correct here?

For certain operations, yes this is required on large-scale systems.  
Some things like "most active user" can be determined relatively easily on an event-centric index but others like your example are much more taxing because there are too many distributed joins required.  
While it may sound a pain to have to create an entity-centric index there are benefits which you may not have considered. For example, with each session holding a list of video IDs that were watched it would be possible to build a "people who watched X also watched Y" style[recommendation system](https://www.youtube.com/watch?v=azP15yvbOBA). I know of music streaming services that use elasticsearch in this way.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [June 20, 2019, 9:40am UTC](https://discuss.elastic.co/t/date-histogram-over-terms-bucket/182249/5 "2019-06-20T09:40:48Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.