Difference in two timestamp fields

Elastic Stack Kibana
1

Hello

I have two fields closed_date and open_date which are in date format and are aggregatable. i want to create a new scripted field using scripting to calculate the difference between them.

We are using ELK 7.3.0 version
My fields are as below...

closed_date = Feb 16, 2020 @ 15:00:06.000,
open_date = Feb 13, 2020 @ 14:40:06.000

Need to calculate (closed_date - open_date).

Thank you.

1 Like
2

This will be helpful

I tried a simple script query with the sample ecommerce dataset and works as expected. I just changed the .millis property by the recommended method .toInstant().toEpochMilli().

GET kibana_sample_data_ecommerce/_search
{
    "_source": ["products.created_on", "order_date"],
    "size": 2,
    "script_fields": {
      "difference": {
        "script": {
          "source": "(doc['order_date'].value.toInstant().toEpochMilli() - doc['products.created_on'].value.toInstant().toEpochMilli()) / 1000 / 60 / 60 / 24"
        }
      }
    }
}

With the result:

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4675,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "kibana_sample_data_ecommerce",
        "_type" : "_doc",
        "_id" : "XAGPn3AB9W_0WIVNAOX7",
        "_score" : 1.0,
        "_source" : {
          "order_date" : "2020-03-16T09:28:48+00:00",
          "products" : [
            {
              "created_on" : "2016-12-26T09:28:48+00:00"
            },
            {
              "created_on" : "2016-12-26T09:28:48+00:00"
            }
          ]
        },
        "fields" : {
          "difference" : [
            1176
          ]
        }
      },
      {
        "_index" : "kibana_sample_data_ecommerce",
        "_type" : "_doc",
        "_id" : "XQGPn3AB9W_0WIVNAOX7",
        "_score" : 1.0,
        "_source" : {
          "order_date" : "2020-03-15T21:59:02+00:00",
          "products" : [
            {
              "created_on" : "2016-12-25T21:59:02+00:00"
            },
            {
              "created_on" : "2016-12-25T21:59:02+00:00"
            }
          ]
        },
        "fields" : {
          "difference" : [
            1176
          ]
        }
      }
    ]
  }
}
3

Thank you @jsanz

Script is working fine

1 Like
4

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