# Why between tag (in range query) doesn't work in Elastic Search queries?

**URL:** <https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137>\
**Category:** Elasticsearch\
**Tags:** elastic-stack-sql\
**Created:** [February 13, 2020, 7:14am UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137 "2020-02-13T07:14:52Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![busra](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@busra](https://discuss.elastic.co/u/busra)\
**Post date:** [February 13, 2020, 7:14am UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137/1 "2020-02-13T07:14:53Z")

</div>

In Elasticsearch i have mapping such as ;

"body": {  
"properties":{  
"time\_Stamp":{  
"type" : "date",  
"format": "dd.MM.yyyy HH:mm:ss"  
},  
"value1":{  
....  
}  
....  
}  
}  
}

I want to get data between two dates. If i explain with examples ;

It works and returns values:  
POST /\_sql  
{  
"query":"select time\_Stamp, value1 from table1 where time\_Stamp \> '23.01.2020 08:30:00.000'"  
}

It works and returns values:  
POST /\_sql  
{  
"query":"select time\_Stamp, value1 from table1 where time\_Stamp \< '07.02.2020 08:30:00'"  
}

But when i put together two range with 'between' tag it doesn't work:  
POST /\_sql  
{  
"query":"select time\_Stamp, value1 from table1 where time\_Stamp between '23.01.2020 08:30:00' and '07.02.2020 09:01:00'"  
}

It couldn't give any error, just query return empty. And also when i write query with AND tag it returns empty.

POST /\_sql  
{  
"query":"select time\_Stamp, value1 from table1 where time\_Stamp \> '23.01.2020 08:30:00' and time\_Stamp \< '07.02.2020 09:01:00'"  
}

It returns like that;  
{"columns":[{"name":"time\_Stamp", "type": "datetime"}, {"name":"value1", "type": "text"}], "rows":[]}

What is the problem in between tag? Is my query wrong or not? Do you have any idea or suggestion?

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 14, 2020, 7:34am UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137/2 "2020-02-14T07:34:40Z")

</div>

1. What ES version is this?
2. Please, run the "BETWEEN" query through the `translate` API and post here the result.

---

<div class="post-metadata">

**Author:** ![busra](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@busra](https://discuss.elastic.co/u/busra)\
**Post date:** [February 14, 2020, 10:50am UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137/3 "2020-02-14T10:50:39Z")

</div>

My Elasticsearch version is 7.2.0.

when i run this query with translate;

> POST /\_sql/translate  
> {  
> "query":"select time\_Stamp from table1 where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time\_Stamp between '23.01.2020 08:30:00.000' and '24.01.2020 09:00:00.000') order by time\_Stamp asc"  
> }

it returns ;

> {  
> "size" : 1000,  
> "query" : {  
> "bool" : {  
> "must" : [  
> {  
> "term" : {  
> "code.keyword" : {  
> "value" : "99fbd3e83d8f27b7dcd086ef2d5019c4",  
> "boost" : 1.0  
> }  
> }  
> },  
> {  
> "range" : {  
> "time\_Stamp " : {  
> "from" : "23.01.2020 08:30:00.000",  
> "to" : "24.01.2020 09:00:00.000",  
> "include\_lower" : true,  
> "include\_upper" : true,  
> "boost" : 1.0  
> }  
> }  
> }  
> ],  
> "adjust\_pure\_negative" : true,  
> "boost" : 1.0  
> }  
> },  
> "\_source" : false,  
> "stored\_fields" : "_none_",  
> "docvalue\_fields" : [  
> {  
> "field" : "time\_Stamp "  
> }  
> ],  
> "sort" : [  
> {  
> "time\_Stamp" : {  
> "order" : "asc",  
> "missing" : "\_last",  
> "unmapped\_type" : "date"  
> }  
> }  
> ]  
> }

However when i changed date range it gives an error;

> POST /\_sql/translate  
> {  
> "query":"select time\_Stamp from table1where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time\_Stamp between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000') order by time\_Stamp asc"  
> }

> {  
> "error": {  
> "root\_cause": [  
> {  
> "type": "planning\_exception",  
> "reason": "Cannot generate a query DSL for an SQL query that either its WHERE clause evaluates to FALSE or doesn't operate on a table (missing a FROM clause), sql statement: [select time\_Stamp from table1where code = '99fbd3e83d8f27b7dcd086ef2d5019c4' AND ( time\_Stamp between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000') order by time\_Stamp asc]"  
> }  
> ],  
> "status": 400  
> }

I don't understand why it gives an error and doesn't work my query. How does 'between' tag work? Is it compare dates step by step? For instance;  
this date range works : '23.01.2020 08:30:00.000' and '24.01.2020 09:00:00.000'  
this date range doesn't work for me : '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000'

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 14, 2020, 2:27pm UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137/4 "2020-02-14T14:27:06Z")

</div>

The reason in that error message is self-explanatory: "WHERE clause evaluates to FALSE".  
And in your query's case `between '23.01.2020 08:30:00.000' and '11.02.2020 09:00:00.000'` doesn't make sense since the upper limit is in the past relative to the lower limit which means that the query will always evaluate to `FALSE`.

Also, the translation of the initial query is correct. And I don't see why it wouldn't match your documents. Also, to my knowledge we didn't have any bugs in that area.

If you can create a simple reproduceable case, I can try this on my end to see if I find any issues. By "simple reproduceable case" I mean a simple index mapping, test documents and query that fails to return them. Thanks.

---

<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:** [March 13, 2020, 2:27pm UTC](https://discuss.elastic.co/t/why-between-tag-in-range-query-doesnt-work-in-elastic-search-queries/219137/5 "2020-03-13T14:27:16Z")

</div>

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