Painless Scripted Field Based on Condition to Subtract Dates to get days

[Ramya_Tanikanti (Twinkle)]

I want to find the difference between dates in days in a new field. I tried the following script and it's throwing error.
What am I doing wrong?

def diff = 0;
if (doc['delivery_date'].value != null) {  
diff = (doc['delivery_date'].value - doc['start_date'].value) / (3600000.0*24);
} 
return diff;

[ebalmeida (Erick Almeida)]

Hi,
From what I've gathered:
On the current version of Kibana you should be checking for field validity using

doc['field'].size() > 0

So, first things first:

if(doc['delivery_date'].size() > 0) {
  if(doc['start_date'].size() > 0) {
    "code"
  }
}
return -1;

You shouldn't return null on the scripted field, filters will hate it. If -1 is a valid result than you should add another filter to exclude results without a delivery date (and another to exclude results without a start date) on the vis. If it's not then add a filter excluding diff = -1.

Second, I used to do date diff this way and had some headaches with it, mostly to do with the fact that this counts 24 hour intervals and not actual civil days (from midnight to 11:59) which led me to do it by converting the values to a localdate. It's a chore, but pays off in the end (for instance, you can use it to exclude weekends from the duration).

Your code block above would therefore look like this

ZoneId timeZone = ZoneId.of('Z') // Or your relevant time zone
LocalDate deliveryDate = LocalTimeDate.ofInstant(Instant.ofEpochMilli(doc['delivery_date'].value),timeZone).toLocalDate(); // This gets rid of time information in the date
LocalDate startDate = LocalTimeDate.ofInstant(Instant.ofEpochMilli(doc['start_date'].value),timeZone).toLocalDate(); // This gets rid of time information in the date
return DAYS.between(deliveryDate,startDate);