Average aggregation on date datatype in Elasticsearch?

Question

I have stored the 3 date datatype variable (date1, date2, date3) in my index using a stored script. I have calculated duration between two date variables(date2 - date1) and stored in the third date variable (date3). Here is my complete mapping, scripts and update requests that I have used.

PUT /myindex

POST /myindex/_mappings
{
  "properties":{
    "date1":{
      "type":"date"
    },
    "date2":{
      "type":"date"
    },
    "date3":{
      "type":"date"
    }
  }
}

POST _scripts/myindexscript/
{
  "script":{
    "source" :"""

          if (ctx._source.date1==null) {
            ctx._source.date1 = new Date().getTime();
          }
          ctx._source.status.add(params.status);
          if (!(params.status).equalsIgnoreCase('Info')) {
              ctx._source.date2 = new Date().getTime();
              ctx._source.date3=ctx._source.date2 - ctx._source.date1;
          }

    """,
    "lang": "painless"
  }
}

POST /myindex/_update/1
{
  "script":{
    "id":"myindexscript",
    "params":{
      "status": "Infoa"
    }
  }
  , "upsert": {
    "date1":null,
    "date2":null,
    "date3":null,
    "status": []
  },
  "scripted_upsert": true
}

I have inserted 1 document as you can see here. For insertion in the document I first run the _update request with field status='Info' and then field status!='Infoa'. Here you can see output of GET /myindex/_search

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "myindex",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "date3" : 5589,
          "date2" : 1590062272146,
          "date1" : 1590062266557,
          "status" : [
            "Info",
            "Infoa"
          ]
        }
      }
    ]
  }
}

Now I run this particular aggregation request

GET /myindex/_search
{
  "query": {
    "term": {
      "status.keyword": {
        "value": "Infoc"
      }
    }
  }, 
  "aggs": {
    "NAME": {
      "avg": {
        "field": "date3"
      }
    }
  },
  "size": 20
}

Which give this weird output :

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 0.8025915,
    "hits" : [
      {
        "_index" : "myindex",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.8025915,
        "_source" : {
          "date3" : 5589,
          "date2" : 1590062272146,
          "date1" : 1590062266557,
          "status" : [
            "Info",
            "Infoa"
          ]
        }
      }
    ]
  },
  "aggregations" : {
    "NAME" : {
      "value" : 1.142046432E14,
      "value_as_string" : "5589-01-01T00:00:00.000Z"
    }
  }
}

why in aggregation result the value is not 5589 but "value" : 1.142046432E14 . I am not able to understand how date3 field is processed for average calculation. Kindly help ?

score 1 · Answer 1 · answered May 21 '20 at 17:07

1

The difference of two millisecond-timestamp dates is an integer. In your case

1590062272146 - 1590062266557 = 5589

Since your mapping stipulates date3 to be of type date, ES attempts to parse it. It guesses it's a year and so the resulting date is Jan 1st, year 5589, which in milliseconds is indeed 1.142046432E14, i.e. 114204643200000.

So the math is right.

Now,

date2 in UTC is Thu May 21 2020 11:57:52

date1 in UTC is Thu May 21 2020 11:57:46

so the difference of the two, rounded up, is 6 seconds.

You now have basically two options:

call date3 something else like milli_diff and make it an integer instead of date. Then your avg agg result will be in milliseconds too
make date3 default to the format of epoch_millis and perform your date magic on that (such as pretty-printing the avg elapsed difference in a scripted_metric aggregation like in this example)

answered May 21 '20 at 17:07

Joe - GMapsBook.com

15,787
4
23
68

Hey! thank you for the reply. I have tried both of your suggested options and they are working good enough for me ...... But can you clear my one doubt .... I thought of that it is taking it as a year instead of epoch_millis but why ? In mapping of date datatypes when no format is specified it takes it as a default which is strict_date_optional_time||epoch_milis. So why it is not taking it as epoch_millis. One more thing when I insert just 1 element named 'Info' into status array of a doc and run the aggregation script with status.keyword value='Info' it gives the correct expected output. – hsraps May 21 '20 at 19:23
Now that I've tried it again, I cannot replicate it -.- It is as you say it should be -- `strict_millis`... – Joe - GMapsBook.com May 21 '20 at 21:19
Yes .... That was what I was not able to get ...why this dual behaviour .... :/ – hsraps May 22 '20 at 01:07

Average aggregation on date datatype in Elasticsearch?

1 Answers1