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

Elastic Stack Elasticsearch
1

Here is a simple query im trying out ...


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

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

Works fine.
Any thoughts?

2

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

3

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

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

And can you share the mapping as well?

5

Here you go ...

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

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

7

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

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.

8

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

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

Did not work!

9

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

10

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

11

"to your mapping" is the key operating phrase there :slight_smile:

12

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

13

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,

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. Just set up the new mappings win a new index, then use _reindex with the source and destination indices

14

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.

15

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

"publish-date": {
            "type": "date",
            "format": "yyyyMMddHHmmss"
        },
16

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