tnsmanyu opened a new issue, #2601: URL: https://github.com/apache/drill/issues/2601
**Describe the bug** We are using apache drill with elasticsearch, when the date fields are queried we are getting them as varchar type. When cast(datefield as timestamp) is applied queries are not working. ISO 8601 format The [ISO 8601](https://www.iso.org/iso-8601-date-and-time-format.html) standard is an International Standard for the representation of dates and times. This format contains date, time, and the offset from UTC, as well as the T character that designates the start of the time, for example, **2007-04-05T12:30:22+0200**. The pattern for this date and time format is **yyyy-MM-ddThh:mm:ss.Z**. It is recommended to use the ISO-8601 format for representing the date and time in your RESTful web APIs. **To Reproduce** Steps to reproduce the behavior: 1. store date-field in elastic in ISO 8601 format 2. execute SQL query in apache drill - select date-field from elastic.index1. 3. look for error in logs **Expected behavior** It is expected that date-field acts as a date field without applying any functions. `[Error Id: 3cf336ca-3185-498d-ab7c-09bb7c144d48 on drill:31010] org.apache.drill.common.exceptions.UserException: SYSTEM ERROR: DateTimeParseException: Text '2022-07-13T10:12:14+0000' could not be parsed, unparsed text found at index 10 Fragment: 0:0 Please, refer to logs for more information. [Error Id: 3cf336ca-3185-498d-ab7c-09bb7c144d48 on drill:31010] at org.apache.drill.common.exceptions.UserException$Builder.build(UserException.java:657) at org.apache.drill.exec.work.fragment.FragmentExecutor.sendFinalState(FragmentExecutor.java:392) at org.apache.drill.exec.work.fragment.FragmentExecutor.cleanup(FragmentExecutor.java:244) at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:359) at org.apache.drill.common.SelfCleaningRunnable.run(SelfCleaningRunnable.java:38) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:750) Caused by: java.time.format.DateTimeParseException: Text '2022-07-13T10:12:14+0000' could not be parsed, unparsed text found at index 10 at java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1952) at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1777) at org.apache.drill.exec.expr.fn.impl.DateUtility.parseBest(DateUtility.java:696) at org.apache.drill.exec.test.generated.ProjectorGen582.doEval(ProjectorGen582.java:57) at org.apache.drill.exec.physical.impl.project.ProjectorTemplate.projectRecords(ProjectorTemplate.java:67) at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.doWork(ProjectRecordBatch.java:158) at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:103) at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:85) at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:170) at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:119) at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:111) at org.apache.drill.exec.record.AbstractUnaryRecordBatch.innerNext(AbstractUnaryRecordBatch.java:59) at org.apache.drill.exec.physical.impl.project.ProjectRecordBatch.innerNext(ProjectRecordBatch.java:85) at org.apache.drill.exec.record.AbstractRecordBatch.next(AbstractRecordBatch.java:170) at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:103) at org.apache.drill.exec.physical.impl.ScreenCreator$ScreenRoot.innerNext(ScreenCreator.java:81) at org.apache.drill.exec.physical.impl.BaseRootExec.next(BaseRootExec.java:93) at org.apache.drill.exec.work.fragment.FragmentExecutor.lambda$run$0(FragmentExecutor.java:321) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1762) at org.apache.drill.exec.work.fragment.FragmentExecutor.run(FragmentExecutor.java:310) ... 4 common frames omitted 2022-07-18 09:13:07,958 [1d2adbdb-c1f8-1726-647f-5f32502b7daa:frag:0:0] WARN o.a.d.exec.rpc.control.WorkEventBus - Fragment 1d2adbdb-c1f8-1726-647f-5f32502b7daa:0:0 manager is not found in the work bus. 2022-07-18 09:13:07,963 [qtp2023037789-24] ERROR o.a.d.e.server.rest.QueryResources - Query from Web UI Failed: {} org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: DateTimeParseException: Text '2022-07-13T10:12:14+0000' could not be parsed, unparsed text found at index 10 ` **Desktop (please complete the following information):** - OS: Ubuntu 20.04.3 LTS - Browser Chrome -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@drill.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org
