# Calculate time difference in scripted field

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.

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

but it doesnt show the "calendar" difference.

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)

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
``````

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?

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

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.

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

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

``````{
"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",
}
}
}
]
}
``````

How can the ` toEpochDay()` be used?

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"
}
}
}
}
``````

``````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)
}
``````

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 So may be you have an idea how to optimize it? This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.