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

**URL:** <https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278>\
**Category:** Kibana\
**Created:** [February 23, 2017, 5:34pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278 "2017-02-23T17:34:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Castronova](https://avatars.discourse-cdn.com/v4/letter/c/97f17d/32.png) [@Castronova](https://discuss.elastic.co/u/Castronova)\
**Post date:** [February 23, 2017, 5:34pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278/1 "2017-02-23T17:34:20Z")

</div>

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).

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

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

```

This fails with the following exception:

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

---

<div class="post-metadata">

**Author:** ![Bargs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bargs/32/5429_2.png) [@Bargs](https://discuss.elastic.co/u/Bargs)\
**Post date:** [February 23, 2017, 9:49pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278/2 "2017-02-23T21:49:51Z")

</div>

Not with a range query, but you could use a [script query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-query.html):

```auto
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](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html)

---

<div class="post-metadata">

**Author:** ![Castronova](https://avatars.discourse-cdn.com/v4/letter/c/97f17d/32.png) [@Castronova](https://discuss.elastic.co/u/Castronova)\
**Post date:** [February 24, 2017, 6:26pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278/3 "2017-02-24T18:26:49Z")

</div>

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:

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

---

<div class="post-metadata">

**Author:** ![Castronova](https://avatars.discourse-cdn.com/v4/letter/c/97f17d/32.png) [@Castronova](https://discuss.elastic.co/u/Castronova)\
**Post date:** [February 24, 2017, 7:25pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278/4 "2017-02-24T19:25:19Z")

</div>

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

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

```

---

<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 24, 2017, 7:25pm UTC](https://discuss.elastic.co/t/is-it-possible-to-make-date-range-queries-with-multiple-date-fields/76278/5 "2017-03-24T19:25:36Z")

</div>

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