# Parser exception when use "include\_upper":true in date range query **URL:** \ **Category:** Elasticsearch\ **Created:** [September 3, 2020, 5:34pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428 "2020-09-03T17:34:13Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![silvina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/silvina/32/44096_2.png) [@silvina](https://discuss.elastic.co/u/silvina)\ **Post date:** [September 3, 2020, 5:34pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/1 "2020-09-03T17:34:13Z")
Hi, I get parse\_exception when I try to do this query: ```auto { "from":0, "size":999, "query":{ "bool":{ "filter":[ { "range":{ "date_created":{ "from":"2020-09-01T23:59:59.000-03:00", "to":"2020-09-01T23:59:59.000-03:00", "include_lower":true, "include_upper":true, "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX", "boost":1.0 } } } ], "adjust_pure_negative":true, "boost":1.0 } }, "sort":[ { "date_created":{ "order":"desc" } } ] } ``` Exception details: {"error":{"root\_cause":[{"type":"parse\_exception","reason":"failed to parse date field [2020-09-01T23:59:59.000-03:00] with format [8uuuu-MM-dd'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXXXX]: [Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26]"}],"type":"search\_phase\_execution\_exception","reason":"all shards failed","phase":"query","grouped":true,"failed\_shards":[{"shard":0,"index":"users","node":"2IUwcGlGQ0mEzsQZ7NgGAg","reason":{"type":"parse\_exception","reason":"failed to parse date field [2020-09-01T23:59:59.000-03:00] with format [8uuuu-MM-dd'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXXXX]: [Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26]","caused\_by":{"type":"date\_time\_parse\_exception","reason":"Text '2020-09-01T23:59:59.000-03:00' could not be parsed, unparsed text found at index 26"}}}]},"status":400} But if I change "include\_upper":true to "include\_upper":false, it's works. ```auto { "from":0, "size":999, "query":{ "bool":{ "filter":[ { "range":{ "date_created":{ "from":"2020-09-01T23:59:59.000-03:00", "to":"2020-09-01T23:59:59.000-03:00", "include_lower":true, "include_upper":false, "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX", "boost":1.0 } } } ], "adjust_pure_negative":true, "boost":1.0 } }, "sort":[ { "date_created":{ "order":"desc" } } ] } ``` ES version: 6.8.4. Could you please help me to understand this issue? Thanks! ---
**Author:** ![Emanuil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuil/32/36783_2.png) [@Emanuil](https://discuss.elastic.co/u/Emanuil)\ **Post date:** [September 3, 2020, 6:48pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/2 "2020-09-03T18:48:45Z")
It does indeed fail with that error on 6.8.4 exactly as you describe, and works when `include_upper` is set to `false`. I found this [old deprecation notice (end of page)](https://www.elastic.co/guide/en/elasticsearch/reference/0.90/query-dsl-range-query.html) in the 0.90 docs stating that `from`, `to`, `include_upper` and `include_lower` were deprecated in favour of `gt`, `gte`, `lt` and `lte`. I then read your query more closely and I'm curious, what are you trying to query exactly? Your `from` and `to` are the same moment in time. That's not really a range query :). The error is saying that your own desired time period can't be parsed with any of the formats you're providing. I can't find any reference to the `X` date format marker you're using either in the [Joda docs](https://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html) or [our own](https://www.elastic.co/guide/en/elasticsearch/reference/6.8/mapping-date-format.html#custom-date-formats). It seems to be a valid marker so chalk that down to my lack of experience with Joda. I rewrote your query to what I think retains your intent but uses less complex formatting: ```auto { "from":0, "size":999, "query":{ "bool":{ "filter":[ { "range":{ "date_created":{ "lte":"2020-09-01T23:59:59.000", "gte":"2020-09-01T23:59:59.000", "format":"date_hour_minute_second_millis", "time_zone": "-03:00", "boost":1.0 } } } ], "adjust_pure_negative":true, "boost":1.0 } }, "sort":[ { "date_created":{ "order":"desc" } } ] } ``` Let me know if that works, and if it is equivalent to your original query. ---
**Author:** ![silvina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/silvina/32/44096_2.png) [@silvina](https://discuss.elastic.co/u/silvina)\ **Post date:** [September 3, 2020, 8:02pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/3 "2020-09-03T20:02:48Z")
Thanks for getting back to me 🙂 The range before was just a test, the real range is: ```auto "range":{ "date_created":{ "gte":"2020-06-01T00:00:00.000-03:00", "lte":"2020-09-01T23:59:59.000-03:00", "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX", "boost":1.0 } } } ``` This is not a joda time format, it's a date time format ( we are using this doc to change joda formatter to date time formatter: [https://www.elastic.co/guide/en/elasticsearch/reference/7.x/migrate-to-java-time.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/migrate-to-java-time.html)) I think, it's not a format problem because if you change "lte" to "lt" it's works. This is Another test that I made and it's worked: ```auto { "range":{ "date_created":{ "gte":"2020-09-01T23:59:59.000-03:00", "format":"8uuuu-MM-dd'\''T'\''HH:mm:ss.SSSX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-dd'\''T'\''HH:mm:ss.SSSXXXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXX||uuuu-MM-d'\''T'\''HH:mm:ss.SSSXXXXX", "boost":1.0 } } } ``` I think there is something wrong with "LTE" field in date range query. What do you think? Thanks! ---
**Author:** ![Emanuil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuil/32/36783_2.png) [@Emanuil](https://discuss.elastic.co/u/Emanuil)\ **Post date:** [September 4, 2020, 12:02am UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/4 "2020-09-04T00:02:10Z")
> This is not a joda time format, it's a date time format ( we are using this doc to change joda formatter to date time formatter: [Java time migration guide | Elasticsearch Guide [7.16] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/7.x/migrate-to-java-time.html)) Thanks a lot, I forgot we did indeed switch time formats in 7.x! > it's not a format problem Agreed. I can reproduce it - changing `lte` to `lt` in the first query here makes it work. Changing `gte` to `gt` has no effect. For me, using this rewrite of your `format` string works: > "8uuuu-MM-d'T'HH:mm:ss.SSSXXXXX||uuuu-MM-d'T'HH:mm:ss.SSSXXX||uuuu-MM-d'T'HH:mm:ss.SSSX||uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX||uuuu-MM-dd'T'HH:mm:ss.SSSXXX||uuuu-MM-dd'T'HH:mm:ss.SSSX" I haven't touched your format strings, but I have re-ordered them. You have 6 defined in this order in your `format` field: > uuuu-MM-dd'T'HH:mm:ss.SSSX > uuuu-MM-dd'T'HH:mm:ss.SSSXXX > uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX > uuuu-MM-d'T'HH:mm:ss.SSSX > uuuu-MM-d'T'HH:mm:ss.SSSXXX > uuuu-MM-d'T'HH:mm:ss.SSSXXXXX Mine are reordered so that the more specific format strings come before the less detailed format strings: > uuuu-MM-d'T'HH:mm:ss.SSSXXXXX > uuuu-MM-d'T'HH:mm:ss.SSSXXX > uuuu-MM-d'T'HH:mm:ss.SSSX > uuuu-MM-dd'T'HH:mm:ss.SSSXXXXX > uuuu-MM-dd'T'HH:mm:ss.SSSXXX > uuuu-MM-dd'T'HH:mm:ss.SSSX It seems you ran into a version of [this bug (+ the reorder workaround)](https://github.com/elastic/elasticsearch/issues/47423#issuecomment-537572846). The bug should be fixed in 7.5.0. I tested myself in 7.9.1 (latest) and both format definitions work now. Let me know if using my format definition fixes `lte` on your system too. ---
**Author:** ![silvina](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/silvina/32/44096_2.png) [@silvina](https://discuss.elastic.co/u/silvina)\ **Post date:** [September 4, 2020, 3:39pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/5 "2020-09-04T15:39:14Z")
It's works 🙂 thanks! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 2, 2020, 3:39pm UTC](https://discuss.elastic.co/t/parser-exception-when-use-include-upper-true-in-date-range-query/247428/6 "2020-10-02T15:39:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.