Calculate time difference in scripted field

Elastic Stack Elasticsearch
1

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; }

2

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

3

@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; 
        
    }
4

millis might work. What is your cluster version?

5

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; 
        
    }
6

From the previous link I shared (Using Datetime in Painless | Painless Scripting Language [master] | Elastic), I think that you should do something like:

return ChronoUnit.SECONDS.between(doc['QuoteRequestTime'].value, doc['QuoteResponseTime'].value);
7

tried this but still it is provided same wrong output.

8

@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;

}

9

Can you please show the sample document as well as the mapping for the fields used so we can recreate this?

10

@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.

11

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.

12

Yes. But i already combined two fields so please consider this as one document

13

Please show the exact document together with the mappings for the fields in question in Elasticsearch.

14

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

15

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.

16

@Christian_Dahlqvist ,
PFA for this

as1
as11904×721 84.7 KB

17

One date is in June and the other in April so the difference is a bit over 61 days, or 5,270,565 seconds.

1 Like
20

How is that related to this topic?

21

@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; }

22

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