Is it possible to make date range queries with multiple date fields

[Castronova (Tony Castronova)]

Is it possible to make date range queries with multiple date fields? My index has multiple date fields that I would like to use to isolate specific records. I am able to make a query using a hardcoded date (i.e. 2017-01-01).

GET _search
{
  "query": {
    "range": {
      "usr_last_login_date": {
        "gt": "2017-01-01||-1M/d"
      }
     }
  }
}

Is it possible to modify this to find records that are relative to another datefield? For example:

GET _search
{
  "query": {
    "range": {
      "usr_last_login_date": {
        "gt": "report-date||-1M/d"
      }
     }
  }
}

This fails with the following exception:

...
...
"reason": {
   "type": "parse_exception",
   "reason": "failed to parse date field [report-date] with format [strict_date_optional_time||epoch_millis]",
   "caused_by": {
   "type": "illegal_argument_exception",
    "reason": "Parse failure at index [0] of [report-date]"
}
...
...

Any help is greatly appreciated.

[Bargs (Matt Bargar)]

Not with a range query, but you could use a script query:

GET /_search
{
    "query": {
        "bool" : {
            "must" : {
                "script" : {
                    "script" : {
                        "inline": "doc['usr_last_login_date'].value > doc['report-date'].value",
                        "lang": "painless"
                     }
                }
            }
        }
    }
}

We also support this sort of functionality in Kibana via Scripted Fields

[Castronova (Tony Castronova)]

Thanks @Bargs. Is it possible to subtract from the datetime field too? I really want to compare the usr_last_login_date to (report-date - 6 Months). I'm having trouble finding any documentation online for subtracting a fixed date (e.g. 6M) from a datetime field.

for example:

"doc['usr_last_login_date'].value > (doc['report-date'].value -6M)"

I've also tried to create a report-date-minus-6m scripted field using painless, but haven't had any luck.

[Castronova (Tony Castronova)]

I figured out a way to create this as a painless scripted field:

LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['usr_last_login_date'].value), ZoneId.of('Z')).isAfter(LocalDateTime.ofInstant(Instant.ofEpochMilli(doc['report_date'].value), ZoneId.of('Z')).minusMonths(6))