Simple range query not working, why?

Elastic Stack Elasticsearch
1

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,
2

According to the documentation gte is greater or equal than while gt is strictly greater than. That result therefore looks OK to me.

3

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

4

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?

5

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

6

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
>         }
>       }
>     }
>   }
> }
7

Every field has a mapping. Use the get mapping API to retrieve it.

1 Like
8

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

{
"createdAt": {
"type": "text",
"fields": {
"keyword": {
"type": "keyword",
"ignore_above": 256
}
}
}
}

9

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

1 Like
10

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"
        }
      }
    }
  }
}
11

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

1 Like
12

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

does it exist?

13

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.

1 Like
14

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