# Date histogram on objects with a start and end time **URL:** \ **Category:** Kibana\ **Tags:** vega\ **Created:** [April 26, 2022, 1:08pm UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290 "2022-04-26T13:08:00Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Griffures](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/griffures/32/104838_2.png) [@Griffures](https://discuss.elastic.co/u/Griffures)\ **Post date:** [April 26, 2022, 1:08pm UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/1 "2022-04-26T13:08:00Z")
Hello everyone, first time poster here. Please make me aware of any mishaps I might have done. I'll use a specific usecase to illustrate my question, but I would love a more general answer as I just got into Kibana. Elastic's version is **7.17.7** (_rip Timelions_). ## Context I work on a Spark monitoring library. During a job's life time, this library sends message to Elasticsearch containing some useful variables, and are then displayed in a Kibana Dashboard. Below is an example of a message concerning a spark executors sent when it dies : ```json { "timestamp" : 1650966972460, // mapped as date "sourceAppName" : "RandomTestApp", // keyword "sourceAppId" : "local-1650966959680", // keyword "sourceAppLabel" : "RandomTestApp#2022-04-26_11:55:34", "dataClass" : "ExecutorEntry", // keyword "executorEntry" : { "executorId" : "local-1650966959680:driver", "executorHost" : "localhost", // keyword "totalCores" : 8, // int "executorStartTime" : 1650966959724, // mapped as date "executorEndTime" : 1650966972460, // mapped as date "removedReason" : "Application ended.", // str "totalExecutorRunTime" : 14394, // ms "totalExecutorCpuTime" : 4888, // ms "totalExecutorBytesRead" : 37608, // B "totalExecutorBytesWritten" : 0, // B "executorPeakMemory" : 1310720, // B "executorAverageMemory" : 818152, // B "executorNetAllocatedMemory" : 2000000000, // B "executorGrossAllocatedMemory" : 3000000000, // B "executorStatus" : -1 // 1 for ACTIVE, -1 for INACTIVE } } ``` ## Questions Now for the Kibana question : Let's say I want to see the number of equivalent cores per application on a date histogram. In my mind, the best result would answer to the following procedure : For every timestamp _t\_n_, most likely one per _15min_ : - Query every _executorEntry_ where _executorEntry.executorStartTime \<= t\_n_ and _executorEntry.executorEndTime \>= t\_n_ - Get _executorEntry/totalCores_ from them - Break down (aggregate) per _sourceAppLabel_ - Show the date histogram as _bars stacked_. Could this be done via the Lens function ? How so ? Is there something like the Expression Editor in Canvas that could be included in a Dashboard environment ? I though of timelions that could provide a language to do just that, but I heard they have been deprecated. What would replace them ? I think Vega might be overkill, but perhaps I am just scared of the barrier of entry. Would love some pointers. I know the question is quite open ended as I am pretty new with the technology, so I'll be happy to answer anything that might be of use. Feel free to recommend good practices too ! ---
**Author:** ![ghudgins](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ghudgins/32/138532_2.png) [@ghudgins](https://discuss.elastic.co/u/ghudgins)\ **Post date:** [April 26, 2022, 2:45pm UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/2 "2022-04-26T14:45:57Z")
Given how your data is structured I would try this in Lens from a Dashboard (you can also import visualizations to Canvas with the "Add from Kibana" button in 7.17--the editors are much more similar in later versions). Set the filter you specify `executorEntry.executorStartTime <= t_n and executorEntry.executorEndTime >= t_n` in the query bar > Get _executorEntry/totalCores_ from them sounds like a good use of the **Sum** quick function > Break down (aggregate) per _sourceAppLabel_ Set this as the break down by. Adjust number of terms and rank order. ---
**Author:** ![Griffures](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/griffures/32/104838_2.png) [@Griffures](https://discuss.elastic.co/u/Griffures)\ **Post date:** [April 26, 2022, 3:39pm UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/3 "2022-04-26T15:39:34Z")
Hello Graham ! Thanks for the response ! > [@ghudgins](#): > > Set the filter you specify `executorEntry.executorStartTime <= t_n and executorEntry.executorEndTime >= t_n` in the query bar How would I indicate to the date histogram that the _t\_n_ is the current timestamp ?( if I indicate to the date histogram that I want the data to be bucketed with a 30min interval, it should do that every 30 min to populate the bar stacked graph). For example, if : - _A.executorStartTime_ = 10:25 and _A.executorEndTime_ = 10h55 - _B.executorStartTime_ = 10:35 and _B.executorEndTime_ = 11h15 Then on the graph : - Bucket 09h30 -\> 10h00 : 0 - Bucket 10h00 -\> 10h30 : A - Bucket 10h30 -\> 11h00 : A + B - Bucket 11h00 -\> 11h30 : B - Bucket 11h30 -\> 12h00 : 0 ---
**Author:** ![ghudgins](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ghudgins/32/138532_2.png) [@ghudgins](https://discuss.elastic.co/u/ghudgins)\ **Post date:** [April 26, 2022, 8:26pm UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/4 "2022-04-26T20:26:17Z")
ah I get it now I don't think you have access to the date histogram's current interval here so Vega might be a good option. if you don't need the exact interval of the visualization you could write a runtime field to check within a sort of sliding window i.e. executorEntry.executorStartTime \<= (last 15 minutes from timestamp) and executorEntry.executorEndTime \>= (15 minutes after timestamp) but it's not a perfect solution for what you've asked here. ---
**Author:** ![Griffures](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/griffures/32/104838_2.png) [@Griffures](https://discuss.elastic.co/u/Griffures)\ **Post date:** [April 27, 2022, 8:55am UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/5 "2022-04-27T08:55:38Z")
> [@ghudgins](#): > > you could write a runtime field to check within a sort of sliding window [...] but it's not a perfect solution for what you've asked here. I researched about runtime fields, but I failed to see how they could be used to populate a bar stacked graph. So I guess my only solution is Vega then. Would you happen to have some pointers on what a Vega solution would look like ? (Maybe some examples of code with a similar workflow). It is quite noob unfriendly ! Haha. My team is not hostile to a transition on Canvas. Could this be done inside the expression editor ? (again if you have some pointers I would appreciate it gladly). ---
**Author:** ![Griffures](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/griffures/32/104838_2.png) [@Griffures](https://discuss.elastic.co/u/Griffures)\ **Post date:** [May 9, 2022, 8:05am UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/6 "2022-05-09T08:05:44Z")
May I rekindle the subject ? Fitting objects with a start and end time into a time histogram seems like a pretty general problematic. Surely there is a straightforward way to do this, right ? Looking for guidance ! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 6, 2022, 8:06am UTC](https://discuss.elastic.co/t/date-histogram-on-objects-with-a-start-and-end-time/303290/7 "2022-06-06T08:06:08Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.