Date Range Query without result


(Michael Remme) #1

Hi,
on elastic 6.1.2 a range query by date does not give a result, hope anybody can help.

Here is the query:

      GET /test_index/_search 
      {
        "query" : {
          "range" : {
            "testDate" : {
              "gte" : "2012-01-01 22:13:22",
              "lt" : "now"
            }
          }
        }
      }

Here the mapping definition of the field testDate:

      "testDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      },

And here an example record:

  {
    "_index": "test_index",
    "_type": "doc",
    "_id": "0",
    "_score": 1,
    "_source": {
      "field1": "value 0",
      "field2": "other_value 0",
      "field3": "staticValue",
      "testInteger": 0,
      "active": true,
      "testDate": "2018-01-21 17:30:29",
      "title-suggest": "value 0 other_value 0",
      "description_de_DE|SHOP": "value 0 other_value 0",
      "title_de_DE|SHOP": "value 0"
    }
  },

Any idea?

tia


(David Pilato) #2

This works well:

DELETE test
PUT test
{
  "mappings": {
    "doc": {
      "properties": {
        "testDate": {
          "type": "date",
          "format": "yyyy-MM-dd HH:mm:ss"
        }
      }
    }
  }
}
PUT test/doc/1
{
  "testDate": "2018-01-21 17:30:29"
}
GET test/_search
{
  "query": {
    "range": {
      "testDate": {
        "gte": "2012-01-01 22:13:22",
        "lt": "now"
      }
    }
  }
}

It gives:

{
  "took": 2,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 1,
    "max_score": 1,
    "hits": [
      {
        "_index": "test",
        "_type": "doc",
        "_id": "1",
        "_score": 1,
        "_source": {
          "testDate": "2018-01-21 17:30:29"
        }
      }
    ]
  }
}

Tested on 6.1.1. Note that elasticsearch considers at index time your date as a UTC date if you don't define any timezone. Which might be your issue...


(Michael Remme) #3

this is strange. Your example works in my system either.

So the difference to my failing example is, that there i am creating the data inside a java application by using the rest client. Inside elastic they are looking quite similar, though.


(David Pilato) #4

That's often the case. Pasted code is not the exact code ran.

What gives:

GET test/_mapping

(Michael Remme) #5

mapping of test:

  {
    "test": {
      "mappings": {
        "doc": {
          "properties": {
            "testDate": {
              "type": "date",
              "format": "yyyy-MM-dd HH:mm:ss"
            }
          }
        }
      }
    }
  }

mapping of test_index, where testDate looks similar to me

{
  "test_index": {
    "mappings": {
      "doc": {
        "properties": {
          "active": {
            "type": "boolean"
          },
          "category_ids_de_DE|SHOP": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "dateField2": {
            "type": "long"
          },
          "description_de_DE|SHOP": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "field1": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "field2": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "field3": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "testDate": {
            "type": "date",
            "format": "yyyy-MM-dd HH:mm:ss"
          },
          "testInteger": {
            "type": "long"
          },
          "title-suggest": {
            "type": "completion",
            "analyzer": "simple",
            "preserve_separators": true,
            "preserve_position_increments": true,
            "max_input_length": 50
          },
          "title_de_DE|SHOP": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      }
    }
  }
}

(David Pilato) #6

Are you doing that in an integration test by any chance?


(system) #7

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