# Failing to retrieve results from ES when the timezone is UTC+1, UTC+2 - Problem started in indexes opened since 1/1/2019 **URL:** \ **Category:** Elasticsearch\ **Created:** [January 2, 2019, 9:13pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716 "2019-01-02T21:13:59Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Ariel\_Assaraf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ariel_assaraf/32/22545_2.png) [@Ariel\_Assaraf](https://discuss.elastic.co/u/Ariel_Assaraf)\ **Post date:** [January 2, 2019, 9:13pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/1 "2019-01-02T21:13:59Z")
We are seeing very odd behavior when trying to query ES via Kibana or directly (using Postman). If the timezone is set to UTC or a timezone that is UTC+3 and above/UTC-3 and below, queries return fast, while queries ran with UTC+1/UTC+2/UTC-1/UTC-2 have poor performance and get timed out. We see that when running the exact same query directly to ES with different time zones. ES version 6.3.1 Thanks in advance! ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [January 2, 2019, 9:21pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/2 "2019-01-02T21:21:29Z")
Could you show an example of such query with the mapping? ---
**Author:** ![Ariel\_Assaraf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ariel_assaraf/32/22545_2.png) [@Ariel\_Assaraf](https://discuss.elastic.co/u/Ariel_Assaraf)\ **Post date:** [January 2, 2019, 9:39pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/3 "2019-01-02T21:39:10Z")
Thanks for the prompt reply. Here it is (its a copied Kibana request) : {"index":"2\_newlogs\*","ignore\_unavailable":true,"timeout":3000000,"preference":1546442297305} {"version":true,"size":500,"sort":[{"timestamp":{"order":"desc","unmapped\_type":"boolean"}}],"\_source":{"excludes":},"aggs":{"2":{"date\_histogram":{"field":"timestamp","interval":"30m","time\_zone":"Asia/Jerusalem","min\_doc\_count":1}}},"stored\_fields":["_"],"script\_fields":{},"docvalue\_fields":["timestamp"],"query":{"bool":{"must":[{"match\_all":{}},{"range":{"timestamp":{"gte":1546356433787,"lte":1546442833788,"format":"epoch\_millis"}}}],"filter":[],"should":[],"must\_not":[]}},"highlight":{"pre\_tags":["@kibana-highlighted-field@"],"post\_tags":["@/kibana-highlighted-field@"],"fields":{"_":{}},"fragment\_size":2147483647}} BTW, when we remove "time\_zone":"Asia/Jerusalem" or the histogram query (or both) we get results. @amnons @iamredlus @farin99 ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 2, 2019, 9:57pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/4 "2019-01-02T21:57:51Z")
Hey Igor, I've sent you directly the mapping of one of the indices queried in @Ariel_Assaraf's reply (we wish not to put it in the public domain 🙂 ). Let me know if you need anything else. Lior. ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [January 2, 2019, 10:00pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/5 "2019-01-02T22:00:08Z")
And you can reproduce this by querying ES directly without kibana? When you get the result after it takes the long time, does it seem correct? Could you possibly execute [hot\_threads](https://www.elastic.co/guide/en/elasticsearch/reference/6.5/cluster-nodes-hot-threads.html) request while running this slow request directly against elasticsearch? It would be interesting to see where it spends this time. ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 2, 2019, 10:03pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/6 "2019-01-02T22:03:00Z")
Yes, it is reproduced directly against elasticsearch (using Postman for querying). Removing the time\_zone from the date\_histogram aggregation (or plainly using "UTC" as the value) eliminates the problem. ---
**Author:** ![Ariel\_Assaraf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ariel_assaraf/32/22545_2.png) [@Ariel\_Assaraf](https://discuss.elastic.co/u/Ariel_Assaraf)\ **Post date:** [January 2, 2019, 10:03pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/7 "2019-01-02T22:03:53Z")
Yes, we are reproducing this with/without Kibana while changing the timezone to UTC works both in Kibana and directly. @iamredlus or @amnons can probably help with the hot\_threads req ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [January 2, 2019, 10:10pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/8 "2019-01-02T22:10:20Z")
> [@iamredlus](#): > > Removing the time\_zone from the date\_histogram aggregation (or plainly using "UTC" as the value) eliminates the problem. Can you try running it with `Etc/GMT+2`instead of `Asia/Jerusalem`? I am starting to suspect that it might be [Mitigate date histogram slowdowns with non-fixed timezones. by jpountz · Pull Request #30534 · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/pull/30534), but I am not really sure why it started to happen only after jan 1 though. ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 2, 2019, 10:17pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/9 "2019-01-02T22:17:41Z")
I've run a hot\_threads request while the query was running. Around 10 seconds have passed from when I sent the query until the hot\_threads has been sent. Query finally returned after 49.827 seconds. Sent you the hot\_threads pastebin in a DM (filtered in only the 6 nodes the index resides on). Running the query with "UTC" - query returns within 1.220 seconds. Running the query with "Etc/GMT+2" - query returns within 1.341 seconds. ---
**Author:** ![Ariel\_Assaraf](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ariel_assaraf/32/22545_2.png) [@Ariel\_Assaraf](https://discuss.elastic.co/u/Ariel_Assaraf)\ **Post date:** [January 2, 2019, 10:37pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/10 "2019-01-02T22:37:14Z")
BTW this worked (and still works) on indexes up to 31/12/2018 and isn't working on 01/01/2019, 02/01/2019. So [https://github.com/elastic/elasticsearch/pull/30534](https://github.com/elastic/elasticsearch/pull/30534) doesn't seem relevant 😑 ---
**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\ **Post date:** [January 2, 2019, 11:19pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/11 "2019-01-02T23:19:59Z")
> [@iamredlus](#): > > Running the query with "UTC" - query returns within 1.220 seconds. > Running the query with "Etc/GMT+2" - query returns within 1.341 seconds. Hmm this seems to point towards the issue that I have linked. Do you create indices daily? Did you upgrade elasticsearch recently? @jpountz, any thoughts on this? ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 2, 2019, 11:38pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/12 "2019-01-02T23:38:52Z")
We are running elasticsearch 6.3.2 and create indices daily. Does this have any effect on querying? ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 20, 2019, 7:21pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/14 "2019-01-20T19:21:20Z")
Hey @Igor_Motov and @jpountz Any updates on the issue? Thanks! ---
**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\ **Post date:** [January 24, 2019, 4:04pm UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/15 "2019-01-24T16:04:51Z")
@iamredlus Sorry for the lag, would you mind sharing hot threads again? ---
**Author:** ![iamredlus](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/iamredlus/32/15504_2.png) [@iamredlus](https://discuss.elastic.co/u/iamredlus)\ **Post date:** [January 27, 2019, 9:34am UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/16 "2019-01-27T09:34:56Z")
@jpountz we no longer have these available for the circumstances described. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [February 24, 2019, 9:44am UTC](https://discuss.elastic.co/t/failing-to-retrieve-results-from-es-when-the-timezone-is-utc-1-utc-2-problem-started-in-indexes-opened-since-1-1-2019/162716/17 "2019-02-24T09:44:13Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.