# Unable to load data from elasticsearch that contain date with timezone **URL:** \ **Category:** Uncategorized\ **Created:** [January 15, 2019, 3:16am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554 "2019-01-15T03:16:25Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 15, 2019, 3:16am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/1 "2019-01-15T03:16:25Z")
Hi Guru’s, Im running my own research on utilization of dremio into our production data analytics. During my research, I found an issue that not sure how to solve it. I notice that dremio unable to load data from elasticsearch that contain date with timezone. Below are the error : ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/d/d7c572c877435b090a7c9145cb06cde214ec5a46.png) Below are the format for documentSet data.iso\_8601 from ElasticSearch side : ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/2/2f60581a97e974e3eb85131b2608c4c905c1849d.png) FYI, the input data are like this : ![](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/a/a3ac5eb2da5b542691c6e335fbd955762d8d8c5e.png) I’ve found a link that seems to be related to this issue [Elasticsearch datetime problems](https://community.dremio.com/t/elasticsearch-datetime-problems/1088) but didn’t seem to get response on that. So far now I’m keen to use dremio as middle man between ElasticSearch as main data lake and client BI tools. Therefore I would highly appreciate if your end have any suggestion or recommendation on above issue if it’s possible to be fix. ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 15, 2019, 3:25am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/2 "2019-01-15T03:25:29Z")
Hi @Carillpower Can you please send us the server.log and the profile of the failed query [Dremio Logs](https://docs.dremio.com/advanced-administration/log-files.html) [Share a Query Profile](https://www.dremio.com/tutorials/share-query-profile-dremio/) Thanks @balaji.ramaswamy ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 15, 2019, 3:49am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/3 "2019-01-15T03:49:16Z")
[5543ac43-905d-4db6-a23e-262dad638c83.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/9/9916d0599b1b01b1120fee6c73a898d74b3dbafe.zip) (4.5 KB) Please find the attachment. Thanks in advance for your help ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 15, 2019, 4:19am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/4 "2019-01-15T04:19:28Z")
[server.zip](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/6/60be91cd606581a00682c3fec5ff13b58d2b1457.zip) (12.3 KB) Please find the attachment. Thanks an advance for the help ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 15, 2019, 7:18am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/5 "2019-01-15T07:18:49Z")
Ho @Carillpower We have couple of enhancements we are working on to address this issue. We are actively working on getting our next MAJOR release 3.1 out and then will probably prioritize this into one of the future releases. Thanks @balaji.ramaswamy ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 16, 2019, 3:01am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/6 "2019-01-16T03:01:49Z")
Hi Balaji, Would like to check with you, if i need to manipulate the datetime format from the source before storing into elasticsearch, could you share some example what kind of format that can be accepted from dremio? Thanks and appreciate the effort ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 17, 2019, 3:58pm UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/7 "2019-01-17T15:58:40Z")
Hi @Carillpower Do you have multiple formats of dates in that Elastic field even though you have defined a certain format in the mapping? Thanks @balaji.ramaswamy ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 23, 2019, 5:02am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/8 "2019-01-23T05:02:16Z")
Hi @balaji.ramaswamy , Sorry for the late reply, so far out date format been stream as yyyy-MM-dd HH:mm:ss z . If dremio have it’s default format that are accepted , I can try to ask my developer to try change to that format before streaming into ES. Thanks ---
**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\ **Post date:** [January 23, 2019, 4:10pm UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/9 "2019-01-23T16:10:30Z")
Hi @Carillpower What’s the ES version? Is this the only date format in the actual data? Thanks @balaji.ramaswamy ---
**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\ **Post date:** [January 23, 2019, 5:15pm UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/10 "2019-01-23T17:15:00Z")
Hi @Carillpower, Depending on which Elasticsearch version you’re using, you may have documents under the same mapping with different date formats. In early versions, you can update date format on an existing date field (see [https://github.com/elastic/elasticsearch/issues/25271](https://github.com/elastic/elasticsearch/issues/25271)) You may try updating the mapping in Elastic with specific date format string that Dremio complains about in the error. Elastic’s documentation on how to do this: [https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html#multiple-date-formats](https://www.elastic.co/guide/en/elasticsearch/reference/current/date.html#multiple-date-formats) ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 24, 2019, 2:05am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/11 "2019-01-24T02:05:39Z")
Yes you are correct, im using latest version of ES 6.5 ---
**Author:** ![Carillpower](https://avatars.discourse-cdn.com/v4/letter/c/278dde/32.png) [@Carillpower](https://community.dremio.com/u/Carillpower)\ **Post date:** [January 24, 2019, 2:07am UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/12 "2019-01-24T02:07:14Z")
Hi Ben, You are correct, check my earlier post. I’ve already set multiple format for the date. Thanks though ---
**Author:** ![ben](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ben/32/1367_2.png) [@ben](https://community.dremio.com/u/ben)\ **Post date:** [January 30, 2019, 5:07pm UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/13 "2019-01-30T17:07:12Z")
Hi @Carillpower, My apologies for not looking closely enough at the top of the thread. I notice that the value in the example Elastic document you provided includes “UTC” in the iso\_86901 field. It looks like Elastic parses this fine, but let me verify that Dremio does as well, and I’ll get back to you. ---
**Author:** ![cholden](https://avatars.discourse-cdn.com/v4/letter/c/e47774/32.png) [@cholden](https://community.dremio.com/u/cholden)\ **Post date:** [June 25, 2020, 7:19pm UTC](https://community.dremio.com/t/unable-to-load-data-from-elasticsearch-that-contain-date-with-timezone/2554/14 "2020-06-25T19:19:35Z")
Was this ever resolved?