# Finding past due orders in Kibana by subracting one date from another **URL:** \ **Category:** Kibana\ **Created:** [December 10, 2015, 7:12pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906 "2015-12-10T19:12:49Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 10, 2015, 7:12pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/1 "2015-12-10T19:12:49Z")
Environment: LS2.0, ES2.0, Kib4.2 and Sh2.0 We are capturing order flow data in an Index complete with time stamps of the major order fulfillment events. We want to identify few key orders that are past due. I want to compare the two time stamps and if the difference is greater than 2 days (or 48hrs) then I want to show that order line. In my example we have an arrivalTimeStamp and OnBoardingTimestamp; if the difference btween these dates is more than 2 days then the order is past due and we need to see it. How can I do that in the Query field in Kibana? Please help I'm attaching an image of what I've tried. ![](https://us1.discourse-cdn.com/elastic/original/2X/b/bfe0b5729dc000ef85a39924815543d99b9f7892.jpg) ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [December 10, 2015, 7:50pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/2 "2015-12-10T19:50:15Z")
You can't compare fields in the same document in the query field, but you should be able to set up a script field in Kibana that performs the subtraction and creates a "virtual field" in each document. Then you can place a condition on that field. ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 10, 2015, 8:19pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/3 "2015-12-10T20:19:06Z")
Ok - I got the value computed using the Virtual field. Had to enable dynamic scripting in ES on the ES engine and restart. ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 10, 2015, 9:55pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/4 "2015-12-10T21:55:40Z")
Now that I have the computed the value that I need in virtual field Diff\_Arrival\_to\_OrderDesk - I still can't query/select by that value in the Query field. I tried Diff\_Arrival\_to\_OrderDesk \> 20. Nothing found. It's a numeric field, how do I query on it? ![](https://us1.discourse-cdn.com/elastic/original/2X/8/8d3f3d495b009805e8e7e69e50ac373c0f9e0e94.png) ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [December 11, 2015, 6:45am UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/5 "2015-12-11T06:45:22Z")
The query string syntax for range queries is documented here: [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html#\_ranges](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html#_ranges) ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 11, 2015, 1:34pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/6 "2015-12-11T13:34:34Z")
Documentation mentions this: count:[10 TO \*] I have been trying this with no success: Diff\_Arrival\_to\_OrderDesk:[1 TO \*] Diff\_Arrival\_to\_OrderDesk:\>1 Diff\_Arrival\_to\_OrderDesk:(\>=1 AND \<50) I'm close but I'm missing something. ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 11, 2015, 5:00pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/7 "2015-12-11T17:00:45Z")
It seems that I can apply the range syntax to other numeric fields that exist in the document, but I can't get it to work on that scripted field that I've added. Please help me get over this hump.... still reading the DSL Query String documentation but don't understand how to construct the syntax yet. ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 14, 2015, 1:38pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/8 "2015-12-14T13:38:25Z")
Should the "range" queries work on virtual fields?! ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 16, 2015, 2:00pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/9 "2015-12-16T14:00:24Z")
Anybody, please? ---
**Author:** ![BigFunger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bigfunger/32/7323_2.png) [@BigFunger](https://discuss.elastic.co/u/BigFunger)\ **Post date:** [December 16, 2015, 8:24pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/10 "2015-12-16T20:24:15Z")
jjdepaul, Once I did as you did and enabled scripting on my es instance, I was able to accomplish your goal. There were two tricky bits that I had to work through. First, I needed to add a filter to your search in order to add criteria to your scripted field. Second, I needed to make my scripted field a little more complicated in order to account for the undefined onboardingTimestamp values shown in your sample. I did the following steps: ### Create a scripted field: ((doc['orderOnboardTimestamp'].value ? doc['orderOnboardTimestamp'].value : doc['orderArrivalTimestamp'].value + (1000 \* 60 \* 60 \* 24 \* 9999)) - doc['orderArrivalTimestamp'].value) / 1000 / 60 / 60 / 24 This calcuates the difference between the onboard and arrival dates. If there is no onboard date, then the arrival date with an offset of 9999 days is used instead. [![](http://i.imgur.com/UXxzLNw.png) ](http://i.imgur.com/UXxzLNw.png) ### In discover, add a filter Find any value in the newly created calculatedShipTime field and add a filter for it. [![](http://i.imgur.com/murcfD2.png) ](http://i.imgur.com/murcfD2.png) ### Edit the filter - Hover over the filter, and click on the edit icon (furthest on the right) [![](http://i.imgur.com/0ZmvBzs.png) ](http://i.imgur.com/0ZmvBzs.png) ### Give it a meaningful Filter Alias, and modify the script of the filter - Change the Filter Alias to 'Older than two days' - Change the value param to 2 - Modify the comparison operator at the end of the 'script' value from '==' to '\>=' - Click 'Done' [![](http://i.imgur.com/B8HpU0L.png) ](http://i.imgur.com/B8HpU0L.png) ### Save the search ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 16, 2015, 8:28pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/11 "2015-12-16T20:28:58Z")
Wonderful writeup - thank you so much - will try asap. ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [December 17, 2015, 8:48pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/12 "2015-12-17T20:48:44Z")
This worked Brilliantly, thank you! ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [January 19, 2016, 12:06am UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/13 "2016-01-19T00:06:08Z")
One other question on this old topic: is there a way to get the current timestamp into this equation to replace the 9999? ---
**Author:** ![jjdepaul](https://avatars.discourse-cdn.com/v4/letter/j/e0b2c6/32.png) [@jjdepaul](https://discuss.elastic.co/u/jjdepaul)\ **Post date:** [January 19, 2016, 6:41pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/14 "2016-01-19T18:41:58Z")
Another question in this space - is there a way to make this filter select a RANGE of values?! I tried several combinations of (1 TO 20) and [1 TO 20], but no luck ![](https://us1.discourse-cdn.com/elastic/original/2X/b/bfd1c3d153b5803ce27e0226a7eb8858a94c4ac3.jpg) ---
**Author:** ![Eduard\_Camaj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eduard_camaj/32/8448_2.png) [@Eduard\_Camaj](https://discuss.elastic.co/u/Eduard_Camaj)\ **Post date:** [July 24, 2016, 7:49am UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/15 "2016-07-24T07:49:10Z")
This is something i need now too. It is very strange to have to do these sort of hacks. Any other explanation why is range filter not working out-of-the-box? I have scripted field: `(doc['date1']) ? ((doc['date2']) ? doc['date2'].value - doc['date1'].value : 0): 0` And i get integer numbers (differenfce in ms) in discover/visualizations. But I cannot use this scripted fields in range filter in kibana query (doesn't return nothing): scripted\_field:[1 TO 100] or any other format. Is this bug in kibana/ES? Thanks, Eddie ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:44pm UTC](https://discuss.elastic.co/t/finding-past-due-orders-in-kibana-by-subracting-one-date-from-another/36906/16 "2017-07-06T13:44:30Z")