# Calculate time difference in scripted field **URL:** \ **Category:** Elasticsearch\ **Created:** [April 26, 2021, 5:04am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213 "2021-04-26T05:04:10Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 5:04am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/1 "2021-04-26T05:04:10Z")
The question is how to get the difference from following date format in a second? For example, if **date1** = 4/21/2021 7:53:50 and **date2** = 6/21/2021 7:54:10, the answer should **20**. I am trying below code but it is providing wrong result that is: -40 `if (!doc['QuoteRequestTime'].empty && !doc['QuoteResponseTime'].empty) { return (doc['QuoteRequestTime'].value.second - doc['QuoteResponseTime'].value.second) } else { return null; }` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 26, 2021, 6:52am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/2 "2021-04-26T06:52:49Z")
`value.second` gives the second part of a date. I'd try with `getTime()`. And divide by 1000 (as the result would be in ms). Or I'd look at [Using Datetime in Painless | Painless Scripting Language [master] | Elastic](https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-datetime.html#_datetime_difference_elapsed_time) ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 7:06am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/3 "2021-04-26T07:06:50Z")
@dadoonet , Getting below error: ``` "caused_by": { "type": "illegal_argument_exception", "reason": "dynamic method [org.elasticsearch.index.fielddata.ScriptDocValues.Dates, getTime/0] not found" ``` Script: ``` if (!doc['QuoteRequestTime'].empty && !doc['QuoteResponseTime'].empty) { return (doc['QuoteRequestTime'].getTime() / 1000 - doc['QuoteResponseTime'].getTime() / 1000) } else { return null; } ``` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 26, 2021, 7:28am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/4 "2021-04-26T07:28:36Z")
`millis` might work. What is your cluster version? ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 8:35am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/5 "2021-04-26T08:35:58Z")
Hi @dadoonet , Cluster version is 7.9 still it is providing wrong output: -5270465 ``` { "_id": "FFtZBXkBl4eN-eKzM3Wo", "as": [ -5270465 ] }, ``` Script: ``` if (!doc['QuoteRequestTime'].empty && !doc['QuoteResponseTime'].empty) { return (doc['QuoteRequestTime'].value.millis / 1000 - doc['QuoteResponseTime'].value.millis / 1000) } else { return null; } ``` ---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [April 26, 2021, 8:54am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/6 "2021-04-26T08:54:00Z")
From the previous link I shared ([Using Datetime in Painless | Painless Scripting Language [master] | Elastic](https://www.elastic.co/guide/en/elasticsearch/painless/master/painless-datetime.html#_datetime_difference_elapsed_time)), I think that you should do something like: ```auto return ChronoUnit.SECONDS.between(doc['QuoteRequestTime'].value, doc['QuoteResponseTime'].value); ``` ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 9:04am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/7 "2021-04-26T09:04:01Z")
tried this but still it is provided same wrong output. ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 9:28am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/8 "2021-04-26T09:28:24Z")
@dadoonet , I didn't get why following output adding some extra number like -52704 in final output : -5270434 It should be 34 only ``` if (!doc['QuoteRequestTime'].empty && !doc['QuoteResponseTime'].empty) { return doc['QuoteRequestTime'].value.getMillis()/1000 - doc['QuoteResponseTime'].value.getMillis()/1000; ``` } ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 9:35am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/9 "2021-04-26T09:35:24Z")
Can you please show the sample document as well as the mapping for the fields used so we can recreate this? ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 9:42am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/10 "2021-04-26T09:42:21Z")
@Christian_Dahlqvist , PFB sample document. I parse out last fields i.e received at and sent at through grok and converted into date in logstash. ``` [0915adf2-ff62-421e-97a8-0a00cbb7df7f] [07:53:41 Information] SpotQuote Request : {"RequestId":"5679uo678","LineItems":[{"QuoteRequestId":"87635204","BuyCurrency":"CAD","SellCurrency":"XOF","DealCurrency":"XOF","SalesCohort":"WUBSCTEST","TreasuryCohort":"WUBSCTESTCORE","ValueDate":"SPOT","Amount":500.0,"Direction":"Sell","MarketConvention":"CAD/XOF"}]} received at 4/21/2021 7:56:15 AM `[0915adf2-ff62-421e-97a8-0a00cbb7df7f] [07:53:43 Information] SpotQuote Response : {"ResponseId":"5679uo678","LineItems":[{"QuoteResponseId":"87635204","QuoteId":"G-9a221d-178f4476610-WUSales-2f23fc6-WUBSCTEST-WUSales-1619006023185","SpotRate":437.3374,"CostRate":437.3374,"ForwardPoints":0,"ValidUntilTime":"00:02:00.000","SpotMargin":4.3655,"ForwardPointMargin":0,"Margin":0,"ClientRate":443.8857,"ForwardMargin":0,"IsQuoteTradable":true}]} sent at 6/21/2021 7:57:20 AM` ``` So result should be 65 sec. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 10:21am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/11 "2021-04-26T10:21:51Z")
Is that two separate documents? Scripted fields work in the context of a single document so that would as far as I know not work. ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 11:32am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/12 "2021-04-26T11:32:33Z")
Yes. But i already combined two fields so please consider this as one document ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 11:55am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/13 "2021-04-26T11:55:19Z")
Please show the exact document together with the mappings for the fields in question in Elasticsearch. ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 12:02pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/14 "2021-04-26T12:02:03Z")
Please consider following log line `[0915adf2-ff62-421e-97a8-0a00cbb7df7f] [07:53:43 Information] SpotQuote Response : {"ResponseId":"5679uo678","LineItems":[{"QuoteResponseId":"87635204","QuoteId":"G-9a221d-178f4476610-WUSales-2f23fc6-WUBSCTEST-WUSales-1619006023185","SpotRate":437.3374,"CostRate":437.3374,"ForwardPoints":0,"ValidUntilTime":"00:02:00.000","SpotMargin":4.3655,"ForwardPointMargin":0,"Margin":0,"ClientRate":443.8857,"ForwardMargin":0,"IsQuoteTradable":true}]} sent at 6/21/2021 7:57:20 AM received at received at 4/21/2021 7:56:15 AM` ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 12:05pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/15 "2021-04-26T12:05:55Z")
Please provide the document exactly as indexed in Elasticsearch with the mappings for that index. Can not help or ntry to recreate the issue without that. ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 12:11pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/16 "2021-04-26T12:11:55Z")
@Christian_Dahlqvist , PFA for this ![as1](https://us1.discourse-cdn.com/elastic/original/3X/c/a/ca21d14d8e7228588f44c2911ae48b61afc2751c.png) ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 12:36pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/17 "2021-04-26T12:36:24Z")
One date is in June and the other in April so the difference is a bit over 61 days, or 5,270,565 seconds. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [April 26, 2021, 2:07pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/20 "2021-04-26T14:07:30Z")
How is that related to this topic? ---
**Author:** ![mangeshmj1992](https://avatars.discourse-cdn.com/v4/letter/m/d9b06d/32.png) [@mangeshmj1992](https://discuss.elastic.co/u/mangeshmj1992)\ **Post date:** [April 26, 2021, 2:22pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/21 "2021-04-26T14:22:57Z")
@Christian_Dahlqvist , sorry, my bad. i didn't notice April & Jun. Thank you so much @dadoonet & @Christian_Dahlqvist Following script work for me. `if (!doc['QuoteRequestTime'].empty && !doc['QuoteResponseTime'].empty) { return (doc['QuoteResponseTime'].value.millisOfDay/1000 - doc['QuoteRequestTime'].value.millisOfDay/1000) } else { return null; }` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 24, 2021, 2:23pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/271213/22 "2021-05-24T14:23:21Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.