# Trying to get simplest of "now" to work in date range queries

**URL:** <https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660>\
**Category:** Elasticsearch\
**Created:** [August 10, 2017, 6:53pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660 "2017-08-10T18:53:07Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 6:53pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/1 "2017-08-10T18:53:07Z")

</div>

Here is a simple query im trying out ...

```auto

  "query": { 
        "bool": {
            "filter": [
                { "range": { "publishdate": { "lt": "now" }}}
            ]
        }
    }
}

```

Im hoping to get back almost all reports or atleast most back. But I dont get any hits. If I give a proper anchor date in the format we have like ...

```auto
    "query": {
        "range" : {
            "publishdate" : {
              "lte" : "20160921000000",
              "gte" : "20160921000000"
            }
        }
    }
}

```

Works fine.  
Any thoughts?

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 10, 2017, 8:38pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/2 "2017-08-10T20:38:27Z")

</div>

Can you post an example document you expect to match and your mappings and what version you're on? I was able to get this to work with a simple reproduction: [https://gist.github.com/eskibars/ec4afb8bcc6f2917b89f729fcaca67d6](https://gist.github.com/eskibars/ec4afb8bcc6f2917b89f729fcaca67d6)

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 8:42pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/3 "2017-08-10T20:42:01Z")

</div>

I should have shared this earlier ... version we are on is 5.1.2  
And here is a sample doc

```auto
{
        "_source": {
          "language": "",
          "tags": {
          },
          "title": "Services",
          "publish-date": "20160921000000",
          "summary": " Virtual Network Services",
          "content": "Virtual Network Services ",
          "authors": [],
          "access-control": {
          },
          "last-update-date": "20160921000000"
        }
}

```

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 10, 2017, 8:44pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/4 "2017-08-10T20:44:58Z")

</div>

And can you share the mapping as well?

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 9:15pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/5 "2017-08-10T21:15:28Z")

</div>

Here you go ...

```auto
   "mappings": {
      "reports": {
        "properties": {
          "authors": {
            "type": "text"
          },
          "content": {
            "type": "text"
          },
          "language": {
            "type": "text"
          },
          "publish-date": {
            "type": "date"
          },
          "summary": {
            "type": "text"
          },
          "title": {
            "type": "text"
          },
        }
      }
    }
  }

```

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 9:16pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/6 "2017-08-10T21:16:42Z")

</div>

By the way I'm aware publishdate is actually publish-date.

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 10, 2017, 9:46pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/7 "2017-08-10T21:46:49Z")

</div>

So what's happening is you haven't told Elasticsearch what the date format of that `publish-date` is. By default, [Elasticsearch uses `strict_date_optional_time||epoch_millis`](https://www.elastic.co/guide/en/elasticsearch/reference/5.5/date.html). That 20160921000000 value could be epoch\_millis, and if it were epoch milliseconds, the date would be Tuesday, November 15, 2608 11:50:00 PM GMT.

You can verify that this is what it thinks, as the following does produce a hit on that document:

```auto
GET /t/reports/_search
{
  "query": {
    "bool": {
      "filter": {
        "range": {
          "publish-date": {
            "gt": "2608-11-14",
            "lt": "2608-11-16"
          }
        }
      }
    }
  }
}

```

You can reformat your date value to be one of the default date formats (`strict_date_optional_time` or `epoch_millis`) or you can [supply the `format` parameter to your mapping for the date fields to tell Elasticsearch what you're sending](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#strict-date-time).

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 11:31pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/8 "2017-08-10T23:31:29Z")

</div>

I see. Well, makes sense. Here is what I tried ...

```auto
GET bluemine20170727/reports/_search
{
    "query": {
        "range" : {
            "publish-date" : {
              "lt": "now",
              "format":"yyyyMMddHHmmss"
            }
        }
    }
}

```

Did not work!

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 10, 2017, 11:35pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/9 "2017-08-10T23:35:05Z")

</div>

You need to update the `format` of your mapping (and reindex your data), not the format of the query.

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 10, 2017, 11:42pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/10 "2017-08-10T23:42:56Z")

</div>

> [@shanec](#):
>
> you can supply the format parameter to your mapping for the date fields to tell Elasticsearch what you’re sending.

I see ... I was trying the second option you mentioned ...  
_"you can supply the format parameter to your mapping for the date fields to tell Elasticsearch what you’re sending1."_

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 10, 2017, 11:43pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/11 "2017-08-10T23:43:32Z")

</div>

> [@Jaspreet\_Singh](#):
>
> to your mapping

"to your mapping" is the key operating phrase there 🙂

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 11, 2017, 12:19am UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/12 "2017-08-11T00:19:15Z")

</div>

Aha - so essentially if I have to use "now" there is no way around re-indexing with proper format.

---

<div class="post-metadata">

**Author:** ![shanec](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shanec/32/4004_2.png) [@shanec](https://discuss.elastic.co/u/shanec)\
**Post date:** [August 11, 2017, 12:28am UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/13 "2017-08-11T00:28:50Z")

</div>

Correct. And in general, you probably should reindex even if you didn't want to use `now` since the date values that Elasticsearch thinks it has are incorrect and I suspect you'll have more troubles down the line. For example, if you don't reindex,

```auto
GET /t/reports/_search
{
  "query": { "match_all": {} },
  "aggs": {
    "t": {
      "date_histogram": {
        "field": "publish-date",
        "interval": "month"
      }
    }
  },
  "size": 0
}

```

will produce a date histogram with `"key_as_string": "2608-11-01T00:00:00.000Z"` even though the `key` may be `20159625600000`, and even if you use your numeric form of query. Reindexing now will likely save you a lot of trouble later as you have more complicated queries.

There is a [`_reindex` API in Elasticsearch that may make your life much easier though](https://www.elastic.co/guide/en/elasticsearch/reference/5.5/docs-reindex.html). Just set up the new mappings win a new index, then use `_reindex` with the source and destination indices

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 11, 2017, 2:57am UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/14 "2017-08-11T02:57:47Z")

</div>

Got it!  
Well, thanks a tonne.  
More than solution to my problem, I learnt a great deal more about elastic search. Appreciate all your help and patience.

---

<div class="post-metadata">

**Author:** ![Jaspreet\_Singh](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jaspreet_singh/32/58296_2.png) [@Jaspreet\_Singh](https://discuss.elastic.co/u/Jaspreet_Singh)\
**Post date:** [August 11, 2017, 1:48pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/15 "2017-08-11T13:48:22Z")

</div>

@shanec Just to make sure I'm doing this right ... is this good for the date format we have?

```auto
"publish-date": {
            "type": "date",
            "format": "yyyyMMddHHmmss"
        },

```

---

<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:** [September 8, 2017, 1:48pm UTC](https://discuss.elastic.co/t/trying-to-get-simplest-of-now-to-work-in-date-range-queries/96660/16 "2017-09-08T13:48:29Z")

</div>

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