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


(Jaspreet Singh) #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?


(Shane Connelly) #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


(Jaspreet Singh) #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"
        }
}

(Shane Connelly) #4

And can you share the mapping as well?


(Jaspreet Singh) #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"
          },
        }
      }
    }
  }

(Jaspreet Singh) #6

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


(Shane Connelly) #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.


(Jaspreet Singh) #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!


(Shane Connelly) #9

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


(Jaspreet Singh) #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."


(Shane Connelly) #11

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


(Jaspreet Singh) #12

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


(Shane Connelly) #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


(Jaspreet Singh) #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.


(Jaspreet Singh) #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"
        },

(system) #16

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