# SQL Plugin: Date filter causes error **URL:** \ **Category:** SQL\ **Tags:** troubleshoot\ **Created:** [February 5, 2021, 7:11pm UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883 "2021-02-05T19:11:06Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![vijay1](https://avatars.discourse-cdn.com/v4/letter/v/9f8e36/32.png) [@vijay1](https://forum.opensearch.org/u/vijay1)\ **Post date:** [February 5, 2021, 7:11pm UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/1 "2021-02-05T19:11:06Z")
The is the query I am trying to execute: ``` POST _opendistro/_sql/_explain { "query": "SELECT `test-data1`.`event_type` AS `event_type` FROM `test-data1` WHERE ((`test-data1`.`start_time_UTC` >= DATE('2020-10-17'))) GROUP BY `test-data1`.`event_type`" } ``` I get the folloiwng error: ``` { "error": { "reason": "There was internal problem at backend", "details": ">= function expected {[BYTE,BYTE],[SHORT,SHORT],[INTEGER,INTEGER],[LONG,LONG],[FLOAT,FLOAT],[DOUBLE,DOUBLE],[BOOLEAN,BOOLEAN],[STRING,STRING],[TIMESTAMP,TIMESTAMP],[DATE,DATE],[TIME,TIME],[DATETIME,DATETIME],[INTERVAL,INTERVAL],[STRUCT,STRUCT],[ARRAY,ARRAY]}, but get [TIMESTAMP,DATE]", "type": "ExpressionEvaluationException" }, "status": 503 } ``` start\_time\_UTC is a date type… ``` "start_time_UTC": { - "type": "date" }, ``` This query is being launched thru _ **Tableau** _. **Experimental engine is enabled.**. Elastic version 7.8. Help will be appriciated. ---
**Author:** ![chloe-zh](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/chloe-zh/32/1142_2.png) [@chloe-zh](https://forum.opensearch.org/u/chloe-zh)\ **Post date:** [February 17, 2021, 9:03pm UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/2 "2021-02-17T21:03:52Z")
Hi @vijay1 , I reproduced this error in my local machine. The error point is “`test-data1`.`start_time_UTC` \>= DATE(‘2020-10-17’)”. The thing is the elasticsearch date and time types are not matching very well with the JDBC standard data types. So to make for the JDBC driver working well with the new engine, we currently converted elasticsearch date type to `timestamp` type in the query engine, and also matches to jdbc timestamp type. That explains why you got `ExpressionEvaluationExpression` saying there is an error to make comparison between timestamp value and date value. I would suggest you to keep both sides the same type using the functions `timestamp()`, `date()` etc. for example: WHERE DATE(start\_time\_UTC) \>= DATE(‘2020-10-17’) or WHERE start\_time\_UTC \>= TIMESTAMP(‘2020-10-17 00:00:00’) Either should be working fine. Thanks! ---
**Author:** ![vijay1](https://avatars.discourse-cdn.com/v4/letter/v/9f8e36/32.png) [@vijay1](https://forum.opensearch.org/u/vijay1)\ **Post date:** [February 18, 2021, 5:38pm UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/3 "2021-02-18T17:38:40Z")
Unfortunately, the issue is that with Tablaeu I have very little control over the date functions it uses… ---
**Author:** ![chloe-zh](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.opensearch.org/chloe-zh/32/1142_2.png) [@chloe-zh](https://forum.opensearch.org/u/chloe-zh)\ **Post date:** [February 20, 2021, 5:34am UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/4 "2021-02-20T05:34:48Z")
In this case, could you transfer this issue to our SQL repository by creating a new issue here: [https://github.com/opendistro-for-elasticsearch/sql/issues/new](https://github.com/opendistro-for-elasticsearch/sql/issues/new) and we will work around it once we have solution and resources ---
**Author:** ![penghuo](https://avatars.discourse-cdn.com/v4/letter/p/c89c15/32.png) [@penghuo](https://forum.opensearch.org/u/penghuo)\ **Post date:** [February 23, 2021, 4:43am UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/5 "2021-02-23T04:43:05Z")
Similar issue. > > > Version 1.13 (might be a regression from previous versions) > > The following que…ry: > \`\`\` > POST my\_test\_index\_date/\_doc/ > { > "field1": { > "subFieldA": "2020-02-02" > } > } > \`\`\` > > generates the following mapping specifying the field as \*\*DATE\*\*: > \`\`\` > { > "mappings": { > "\_doc": { > "properties": { > "field1": { > "properties": { > "subFieldA": { > "type": "date" > } > } > } > } > } > } > } > \`\`\` > > > When I try to query it in different combinations that I feel should probably be acceptable, it always fail: > > \*\*Case 1\*\* > \`\`\` > POST \_opendistro/\_sql > { > "query": """SELECT \* FROM > my\_test\_index\_date as i > WHERE > i.field1.subFieldA \> '2020-02-01' > LIMIT 50;""" > } > > Response --\> > { > "error": { > "reason": "There was internal problem at backend", > "details": "\> function expected {\[BYTE,BYTE\],\[SHORT,SHORT\],\[INTEGER,INTEGER\],\[LONG,LONG\],\[FLOAT,FLOAT\],\[DOUBLE,DOUBLE\],\[BOOLEAN,BOOLEAN\],\[STRING,STRING\],\[TIMESTAMP,TIMESTAMP\],\[DATE,DATE\],\[TIME,TIME\],\[DATETIME,DATETIME\],\[INTERVAL,INTERVAL\],\[STRUCT,STRUCT\],\[ARRAY,ARRAY\]}, but get \[TIMESTAMP,STRING\]", > "type": "ExpressionEvaluationException" > }, > "status": 503 > } > \`\`\` > > Why the field is considered TIMESTAMP here instead of DATE (like in the mapping)? > > It'd be very nice to have the two types (TIMESTAMP and STRING) comparable since we don't want the user to always know what kind of Date it is in the mapping > > \*\*Case 2\*\* > \`\`\` > > POST \_opendistro/\_sql > { > "query": """SELECT \* FROM > my\_test\_index\_date as i > WHERE > i.field1.subFieldA \> DATE('2020-02-01') > LIMIT 50;""" > } > > Response --\> > { > "error": { > "reason": "There was internal problem at backend", > "details": "\> function expected {\[BYTE,BYTE\],\[SHORT,SHORT\],\[INTEGER,INTEGER\],\[LONG,LONG\],\[FLOAT,FLOAT\],\[DOUBLE,DOUBLE\],\[BOOLEAN,BOOLEAN\],\[STRING,STRING\],\[TIMESTAMP,TIMESTAMP\],\[DATE,DATE\],\[TIME,TIME\],\[DATETIME,DATETIME\],\[INTERVAL,INTERVAL\],\[STRUCT,STRUCT\],\[ARRAY,ARRAY\]}, but get \[TIMESTAMP,DATE\]", > "type": "ExpressionEvaluationException" > }, > "status": 503 > } > \`\`\` > Shouldn't TIMESTAMP and DATE be comparable? > > > > \*\*Case 3\*\* > \`\`\` > POST \_opendistro/\_sql > { > "query": """SELECT \* FROM > my\_test\_index\_date as i > WHERE > (i.field1.subFieldA) \> TIMESTAMP('2020-02-01') > LIMIT 50;""" > } > > Response --\> timestamp parse error > { > "error": { > "type": "SearchPhaseExecutionException", > "reason": "Error occurred in Elasticsearch engine: all shards failed", > "details": "Shard\[0\]: NotSerializableExceptionWrapper\[semantic\_check\_exception: timestamp:2020-02-01 in unsupported format, please use yyyy-MM-dd HH:mm:ss\[.SSSSSS\]\]\\n\\nFor more details, please send request for Json format to see the raw response from elasticsearch engine." > }, > "status": 503 > } > \`\`\` > > > > > \*\*Case 4\*\*, working, but unexpected output > \`\`\` > > POST \_opendistro/\_sql > { > "query": """SELECT \* FROM > my\_test\_index\_date as i > WHERE > DATE(i.field1.subFieldA) \> DATE('2020-02-01') > LIMIT 50;""" > } > > Response --\> DATE in Timestamp format instead of DATE format like in mapping > { > "schema": \[ > { > "name": "field1", > "type": "object" > } > \], > "datarows": \[ > \[ > { > "subFieldA": "2020-02-02 00:00:00" > } > \] > \], > "total": 1, > "size": 1, > "status": 200 > } > > \`\`\` ---
**Author:** ![penghuo](https://avatars.discourse-cdn.com/v4/letter/p/c89c15/32.png) [@penghuo](https://forum.opensearch.org/u/penghuo)\ **Post date:** [February 24, 2021, 7:17pm UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/6 "2021-02-24T19:17:06Z")
Hi @vijay1 Are you try SQL on AWS Elasticsearch 7.8? ---
**Author:** ![daichen](https://avatars.discourse-cdn.com/v4/letter/d/c4cdca/32.png) [@daichen](https://forum.opensearch.org/u/daichen)\ **Post date:** [April 6, 2022, 12:08am UTC](https://forum.opensearch.org/t/sql-plugin-date-filter-causes-error/4883/7 "2022-04-06T00:08:37Z")
Similar request here: [https://github.com/opensearch-project/sql/issues/294](https://github.com/opensearch-project/sql/issues/294). Implicit conversion between string and date type is already supported. However more support is needed here for conversion between different date type. The workaround would be convert both sides to same type explicitly as Chloe suggested.