Coping with ES' Use of org.joda.time.MutableDateTime

Hello...

I'm still struggling with date arithmetic in the ES stack. I tried something that should have been trivial, coding along with an example presented at this url:

https://www.codeproject.com/Articles/1179670/Using-Painless-Scripting-to-calculate-dates-and-pr

I decided to try this out in Kibana's DevTools tab. Following through the example, I was able to create an index and fill it, just as in the code blocks in the blog post show. I can execute the first, trivial GET request successfully.

But I have encountered a couple of issues:

  1. I cannot see this index on the Kibana 'Discover' tab when I look for it (but that's a separate issue); and
  2. Significantly, I encounter an error when I try to execute the second GET request, encountering an error "Cannot apply [-] to types [org.joda.time.MutableDateTime] and [org.joda.MutableDateTime]."

Here is the second GET query, pasted in from the url cited above is:

GET logs/userlog/_search
{
  "size": 0,
  "aggs": {
    "groupby": {
      "range": {
        "script": {
          "inline": "((doc['CLOSED DATE'].value - doc['START DATE'].value) / (3600000.0/60))"
        },
        "ranges": [
          {
            "from": 0.0,
            "to": 30.0,
            "key": "From 0 to 30 minutes"
          },
          {
            "from": 30.0,
            "to": 60.0,
            "key": "From 30 to 60 minutes"
          },
          {
            "from": 60.0,
            "key": "Greater than 1 hour"
          }
        ]
      }
    }
  }
}

I'm using Kibana 6.4.1 and Elasticsearch 6.4.1. Clearly something has changed since Elastic 5.x that now sets dates to this rather unfriendly org.joda.time.MutableDateTime format. How do I change my bulk ingest to get something that will allow me to date arithmetic?

Thanks!

OK. I have now solved the time problem by invoking getMillis() on the date objects. And I also fixed a "deprecated" warning regarding using "inline" rather than "source". Corrected query pasted below:

GET logs/userlog/_search
{
  "size": 0,
  "aggs": {
    "groupby": {
      "range": {
        "script": {
          "source": "((doc['CLOSED DATE'].value.getMillis() - doc['START DATE'].value.getMillis()) / 3600000.0)"
        },
        "ranges": [
          {
            "from": 0.0,
            "to": 2.0,
            "key": "From 0 to 2 hours"
          },
          {
            "from": 2.0,
            "to": 24.0,
            "key": "From 2 to 24 hours"
          },
          {
            "from": 24.0,
            "key": "Greater than 1 day"
          }
        ]
      }
    }
  }
}

So, other than the fact that I can't see my index in Kibana's "Discover" tab, I'm good...I'd appreciate any advice on this second matter. Or, on how to avoid this default representation using org.joda.time.MutableDateTime.

Thanks again!

Glad you figured it out!

Just a side note: This script needs to be executed for every hit in your query, which in the above examples means all documents in your index.

This would be a use-case for using an ingest processor, that uses a script to calculate the duration when indexing and store it in an additional field. This will speed up your queries tremendously!

@spinscale--Thanks for the tip. I am aware that what I currently have is search-intensive. Another question I have is: is there some equivalent of a cron job that I could run periodically--say daily and presumably outside of peak hours--to update my time_to_closure statistic for an existing index?

Also, while we're on the topic of time processing, do you have any advice regarding another of my recent posts?

[Scanning Window Search through Index?](Scanning Window Search through Index?

I'm still struggling with figuring out how to sweep through an index in time with a time coordinate--let's just call it t--whose value I control (say by updating in a loop) and comparing various time field values with t to determine things like what tickets are open at a given moment, and how long have they been open.

Thanks and all the best...

Hey,

right now there is no equivalent of a cronjob. I think what you need to do is to run a update by query job, that searches for recently closed issues and use a script to calculate the difference. Again, you could potentially do this, when you update the closing time to save a roundtrip and have the data added immediately instead of a cron job (given your setup around Elasticsearch allows for this).

Hope this helps!

--Alex

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