Calculate time difference in scripted field

Elastic Stack Elasticsearch
1

Hello,

The question is how to get the difference rounded to a day between the dates in a document?
For example, if date1 = 2019-12-27T23:00:00.999 and date2 = 2019-12-28T01:00:00.999, the answer is 1.
I'm already using this:

doc['room_created_at'].value.millis - doc['player_created_at'].value.millis) / 1000 / 60 / 60 / 24

but it doesnt show the "calendar" difference.

2

try something like doc['room_created_at'].value.toInstant().minus(doc['player_created_at'].value.toInstant()).toEpochMilli() (on top of my head and untested, also depends on the ES version being used)

3

The following code is the solution:

doc['room_created_at'].value.millis / 1000 / 60 / 60 / 24 - doc['player_created_at'].value.millis / 1000 / 60 / 60 / 24
4

What do you think about the following? To solve the problem, I've written the script :

if (doc['player_created_at'].size() != 0) {
    return (doc['start_at'].value.millis / 1000 / 60 / 60 / 24 - doc['player_created_at'].value.millis / 1000 / 60 / 60 / 24)
} else {
    return null
}

but the script gives different results. For example, if the difference between the dates is 3d 3h 1m 56s , it calculates as 3 days. But if the difference is 3d 9h 17m 6s , the difference calculated is 4 days (see the screenshot). What's the reason?

1125×274 18.7 KB

P.S. I've tried to use the ceil function, but it works only in X-Pack, so it's not supported in my version of Kibana

5

keep in mind that this uses the same division logic than java. All of those values are integers, so this is doing integer division with rounding.

6

Can it be a solution that calculates the difference as float number and than returns an "integer + 1"? I've written a script, but it doesn work cause of error in casting float-->integer

7

Hm, I think instead of doing calculations yourself, how about

Duration.between(doc['room_created_at'].value.toInstant(), doc['player_created_at'].value.toInstant()).ofDays();

This way you don't need to worry about calculations yourself and their order or their casting...

8

It shows that the method is not found:

{
 "root_cause": [
  {
   "type": "script_exception",
   "reason": "compile error",
   "script_stack": [
    "... d_at'].value.toInstant()).ofDays())\r\n    } else {\r ...",
    "                             ^---- HERE"
   ],
   "script": "if (doc['room_created_at'].size() != 0) {\r\n    if (doc['player_created_at'].size() != 0) {\r\n        return (Duration.between(doc['room_created_at'].value.toInstant(), doc['player_created_at'].value.toInstant()).ofDays())\r\n    } else {\r\n        return null\r\n    }\r\n} else {\r\n    return null\r\n}",
   "lang": "painless"
  }
 ],
 "type": "search_phase_execution_exception",
 "reason": "all shards failed",
 "phase": "query",
 "grouped": true,
 "failed_shards": [
  {
   "shard": 0,
   "index": "gameplay_stat",
   "node": "pZQAw2krTHuIGJxi8862ug",
   "reason": {
    "type": "script_exception",
    "reason": "compile error",
    "script_stack": [
     "... d_at'].value.toInstant()).ofDays())\r\n    } else {\r ...",
     "                             ^---- HERE"
    ],
    "script": "if (doc['room_created_at'].size() != 0) {\r\n    if (doc['player_created_at'].size() != 0) {\r\n        return (Duration.between(doc['room_created_at'].value.toInstant(), doc['player_created_at'].value.toInstant()).ofDays())\r\n    } else {\r\n        return null\r\n    }\r\n} else {\r\n    return null\r\n}",
    "lang": "painless",
    "caused_by": {
     "type": "illegal_argument_exception",
     "reason": "method [java.time.Duration, ofDays/0] not found"
    }
   }
  }
 ]
}

How can the toEpochDay() be used?

9

Try this using .toDays()


DELETE test

PUT test/_doc/1?refresh
{
  "room_created_at" : "2020-02-01T12:34:56.789Z",
  "player_created_at" : "2020-01-01T12:34:56.789Z"
}

GET test/_search
{
  "script_fields": {
    "scripted_filter": {
      "script": {
        "source": "Math.abs(Duration.between(doc['room_created_at'].value.toInstant(), doc['player_created_at'].value.toInstant()).toDays());",
        "lang": "painless"
      }
    }
  }
}
10

What do you think about this? (it works)

if (doc['room_created_at'].size() != 0) {
    if (doc['player_created_at'].size() != 0) {
        Instant p = Instant.ofEpochMilli(doc['player_created_at'].value.millis);
        Instant r = Instant.ofEpochMilli(doc['room_created_at'].value.millis);
        LocalDateTime ztp = LocalDateTime.ofInstant(p, ZoneId.of('Europe/Kiev'));
        LocalDateTime ztr = LocalDateTime.ofInstant(r, ZoneId.of('Europe/Kiev'));
        LocalDate localp = ztp.toLocalDate();
        LocalDate localr = ztr.toLocalDate();
        long dayp = localp.toEpochDay();
        long dayr = localr.toEpochDay();
        return (dayr-dayp)
    } else {
        return (-1)
    }
} else {
    return (-1)
}
11

it sounds like there are a few additional calls for dates, but if it solves your problem properly, I wouldnt too much about it and go with it for now :slight_smile:

12

So may be you have an idea how to optimize it?:wink:

13

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