1

Below mapping i have created for search field

PUT /sample/_mapping
{
  "properties": {
    "webDateTime1": {
      "type":   "date",
      "format": "dd-MM-yyyy HH:mm:ss||dd-MM-yyyy||hour_minute_second"
    }
  }
}

If i search based on "04-04-2019 20:17:18" getting proper data if i search based on "04-04-2019" getting proper data if i search based on "20:17:18" don't know always getting empty result. Any help would be appreciated.

Joe - GMapsBook.com
  • 15,787
  • 4
  • 23
  • 68
TeamZ
  • 343
  • 6
  • 15

1 Answers1

2

When you ingest some sample docs:

POST sample/_doc/1
{"webDateTime1":"04-04-2019 20:17:18"}

POST sample/_doc/2
{"webDateTime1":"04-04-2019"}

POST sample/_doc/3
{"webDateTime1":"20:17:18"}

and then aggregate on the date field,

GET sample/_search
{
  "size": 0, 
  "aggs": {
    "dt_values": {
      "terms": {
        "field": "webDateTime1"
      }
    }
  }
}

you'll see how the values are actually indexed:

...
"buckets" : [
        {
          "key" : 73038000,
          "key_as_string" : "01-01-1970 20:17:18",
          "doc_count" : 1
        },
        {
          "key" : 1554336000000,
          "key_as_string" : "04-04-2019 00:00:00",
          "doc_count" : 1
        },
        {
          "key" : 1554409038000,
          "key_as_string" : "04-04-2019 20:17:18",
          "doc_count" : 1
        }
      ]
...

That's the reason your query for 20:17:18 is causing you a headache.

Now, you'd typically wanna use the range query like so:

GET sample/_search
{
  "query": {
    "range": {
      "webDateTime1": {
        "gte": "20:17:18",
        "lte": "20:17:18",
        "format": "HH:mm:ss"
      }
    }
  }
}

Notice the format parameter. But again, if you don't provide a date in your datetime field, it turns out it's going to take the unix epoch as the date.

Joe - GMapsBook.com
  • 15,787
  • 4
  • 23
  • 68
  • i can see below result in my aggregations "aggregations" : { "dt_values" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 1554322638000, "key_as_string" : "03-04-2019 20:17:18", "doc_count" : 2 }, { "key" : 1554409038000, "key_as_string" : "04-04-2019 20:17:18", "doc_count" : 2 } ] } } while searching search query it is giving me empty result. – TeamZ Apr 03 '20 at 15:17
  • That's because all 4 of your documents include datetimes, not dates. Use this instead: `{"query":{"range":{"webDateTime1":{"gte":"03-04-2019 20:17:18","lte":"04-04-2019 20:17:18","format":"dd-MM-yyyy HH:mm:ss"}}}}` – Joe - GMapsBook.com Apr 03 '20 at 15:31