# Simple range query not working, why?

**URL:** <https://discuss.elastic.co/t/simple-range-query-not-working-why/150682>\
**Category:** Elasticsearch\
**Created:** [October 2, 2018, 10:53am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682 "2018-10-02T10:53:06Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 10:53am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/1 "2018-10-02T10:53:06Z")

</div>

i execute this query below, the datas are always formatted in such a way that a simple string comparison str1 \> str2 will return true if the actual date of str1 is larger then str2

```
> {
> "query": {
> "bool": {
> "must": [{
> "range": {
> "createdAt": {
> "gte": "2019-09-28 12:23:15",
> "lte": "9999-99-99 99:99:99"
> }
> }
> }]
> }
> }
> }

```

But when i execute it is still returning for fields of "createdAt" like (only a snippet is shown as it is quite large)

```
> "_source": {
> "createdAt": "2018-09-28 12:23:15",
> "senderId": 24818700,
```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:05am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/2 "2018-10-02T11:05:38Z")

</div>

According to [the documentation](https://www.elastic.co/guide/en/elasticsearch/reference/6.4/query-dsl-range-query.html) `gte` is greater or equal than while `gt` is strictly greater than. That result therefore looks OK to me.

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:18am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/3 "2018-10-02T11:18:11Z")

</div>

the result is not ok, because 2018 is not in the range 2019-xxx and 9999-xx

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:21am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/4 "2018-10-02T11:21:27Z")

</div>

Ah, now I see the difference in year....

What happens if you change the end date to something that is actually a valid date/timestamp?

What is the mapping for the field?

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:26am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/5 "2018-10-02T11:26:04Z")

</div>

these are just strings not date, but it seeems "elastic" is trying to interpret dates from strings automaticly?

the query seem to work but not for all end dates (only the relevant snippet is shown from original)

> ```
> "range": {
> "createdAt": {
> "gte": "2019-09-28 12:23:15",
> "lt": "2099-09-19 13:00:00"
>       		  
> }
> 
> ```

-- works correctly gives back nothing

```
   	"range": {
      		"createdAt": {
      		    "gte": "2019-09-28 12:23:15",
      		    "lt": "2100-09-19 13:00:00"
      		  
      	}

```

-- doesnt care returns all dates, doesnt even do string comparison

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:26am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/6 "2018-10-02T11:26:42Z")

</div>

the field has no mapping the index defintion looks like this, really simple

```
> {
> "mappings": {
> "_doc": {
> "properties": {
> "body-headers": {
> "type": "join",
> "relations": {
> "body": ["normal", "relay/stat"]
> },
> "eager_global_ordinals": false
> }
> }
> }
> }
> }
```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:28am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/7 "2018-10-02T11:28:15Z")

</div>

Every field has a mapping. Use the [get mapping API](https://www.elastic.co/guide/en/elasticsearch/reference/current/indices-get-mapping.html) to retrieve it.

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:30am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/8 "2018-10-02T11:30:48Z")

</div>

thanks ok, it looks like this for this field (snippet)

> {  
> "createdAt": {  
> "type": "text",  
> "fields": {  
> "keyword": {  
> "type": "keyword",  
> "ignore\_above": 256  
> }  
> }  
> }  
> }

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:36am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/9 "2018-10-02T11:36:00Z")

</div>

OK, so the field you are querying on is an analysed text field. Can you instead try the keyword version `createdAt.keyword`?

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:38am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/10 "2018-10-02T11:38:07Z")

</div>

yes that works as advertised if i use  
"createdAt.keyword"

nice,  
can i change mapping of an index

> ```
> {
> "mappings": {
> "_doc": {
> "properties": {
> "createdAt": {
> "type": "date",
> "format": "yyyy-MM-dd HH:mm:ss"
> }
> }
> }
> }
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:40am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/11 "2018-10-02T11:40:02Z")

</div>

You can not change the mapping of a field in an index, so will have to reindex.

---

<div class="post-metadata">

**Author:** ![Jacob\_Bogers](https://avatars.discourse-cdn.com/v4/letter/j/ee7513/32.png) [@Jacob\_Bogers](https://discuss.elastic.co/u/Jacob_Bogers)\
**Post date:** [October 2, 2018, 11:40am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/12 "2018-10-02T11:40:52Z")

</div>

i am looking for an analog to SQL create table ... as select ...

does it exist?

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [October 2, 2018, 11:41am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/13 "2018-10-02T11:41:39Z")

</div>

[https://www.elastic.co/guide/en/elasticsearch/reference/6.4/docs-reindex.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.4/docs-reindex.html)

Should work once you have added the correct mapping to your index template.

---

<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:** [October 30, 2018, 11:41am UTC](https://discuss.elastic.co/t/simple-range-query-not-working-why/150682/14 "2018-10-30T11:41:59Z")

</div>

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