# Calculate time difference in scripted field

**URL:** <https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893>\
**Category:** Elasticsearch\
**Created:** [January 28, 2020, 5:34pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893 "2020-01-28T17:34:25Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [January 28, 2020, 5:34pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/1 "2020-01-28T17:34:25Z")

</div>

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:

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

```

but it doesnt show the **"calendar"** difference.

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [January 29, 2020, 3:15pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/2 "2020-01-29T15:15:44Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [January 30, 2020, 4:58pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/3 "2020-01-30T16:58:23Z")

</div>

The following code is the solution:

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

```

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [February 3, 2020, 11:34am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/4 "2020-02-03T11:34:28Z")

</div>

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

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

 ![](https://us1.discourse-cdn.com/elastic/original/3X/2/6/26e9629de61ae9f834297933a6d5324acec60299.png)  
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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 3, 2020, 3:12pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/5 "2020-02-03T15:12:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [February 4, 2020, 8:38am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/6 "2020-02-04T08:38:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 4, 2020, 9:02am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/7 "2020-02-04T09:02:22Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [February 4, 2020, 2:27pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/8 "2020-02-04T14:27:20Z")

</div>

It shows that the method is not found:

```auto
{
 "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?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 5, 2020, 9:26am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/9 "2020-02-05T09:26:59Z")

</div>

Try this using `.toDays()`

```auto

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

```

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [February 6, 2020, 11:35am UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/10 "2020-02-06T11:35:16Z")

</div>

What do you think about this? (it works)

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

```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [February 7, 2020, 2:11pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/11 "2020-02-07T14:11:38Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![Anne\_Kim](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/anne_kim/32/47196_2.png) [@Anne\_Kim](https://discuss.elastic.co/u/Anne_Kim)\
**Post date:** [February 7, 2020, 3:21pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/12 "2020-02-07T15:21:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 6, 2020, 3:21pm UTC](https://discuss.elastic.co/t/calculate-time-difference-in-scripted-field/216893/13 "2020-03-06T15:21:19Z")

</div>

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