# Date Format Mismatch in Canvas (Elasticsearch SQL)

**URL:** <https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [October 4, 2019, 9:29am UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317 "2019-10-04T09:29:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Zany](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zany/32/50113_2.png) [@Zany](https://discuss.elastic.co/u/Zany)\
**Post date:** [October 4, 2019, 9:29am UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317/1 "2019-10-04T09:29:51Z")

</div>

**Problem Statement**  
My charts in Canvas are using metric\_date, which has a date format of `YYYYMMDD`. However, the Time Filter element in Canvas formats the same metric\_date as `MMMM Do YYYY, HH:mm:ss.SSS`. As a result, a format mismatch error occurred.

> [essql] \> Unexpected error from Elasticsearch: [parse\_exception] failed to parse date field [2019-09-20T09:16:49.680Z] with format [basic\_date]

As a workaround, I created a Script Field, Formatted\_Metric\_Date, for my charts so that they are using the same date format as the Time Filter.

**Name:** Formatted\_Metric\_Date  
**Lang:** Painless  
**Type:** date  
**Format:** Date  
**Moment.js Format Pattern:** `MMMM Do YYYY, HH:mm:ss.SSS`  
**Script:** `doc['metric_date'].value`

However, I encountered an error in my incorrect Elasticsearch SQL query:  
`SELECT Formatted_Metric_Date, metric_date, metric_name FROM "metrics_dataset_*";`

> [essql] \> Unexpected error from Elasticsearch: [verification\_exception] Found 1 problem(s) line 1:8: Unknown column [Formatted\_Metric\_Date]

**Questions:**

- What is the syntax to query scripted field in Elasticsearch SQL?
- To address the original problem, how can I format metric\_date of format `YYYYMMDD` to `MMMM Do YYYY, HH:mm:ss.SSS` in my Elasticsearch SQL query?

---

<div class="post-metadata">

**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\
**Post date:** [October 4, 2019, 6:07pm UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317/2 "2019-10-04T18:07:05Z")

</div>

@Zany Scripted fields are not supported by Elasticsearch SQL. How are you getting data into elasticsearch? Perhaps it would be easier to solve the date formatting problem when the data is ingested.

---

<div class="post-metadata">

**Author:** ![Zany](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/zany/32/50113_2.png) [@Zany](https://discuss.elastic.co/u/Zany)\
**Post date:** [October 11, 2019, 3:35am UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317/3 "2019-10-11T03:35:51Z")

</div>

@mattkime, thanks for the clarification.

In the Logstash configuration, the data input (metric\_date) has a format of `YYYYMMDD`. This date was extracted from the source filename (a constraint that cannot be changed).

In the Index Pattern, it takes on the default date format of `MMMM Do YYYY, HH:mm:ss.SSS`.

---

<div class="post-metadata">

**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\
**Post date:** [October 14, 2019, 3:19pm UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317/4 "2019-10-14T15:19:04Z")

</div>

Perhaps we can find a way to reformat your query. There's KQL which does work with scripted fields - [https://www.elastic.co/guide/en/kibana/current/kuery-query.html](https://www.elastic.co/guide/en/kibana/current/kuery-query.html)

That said, I'm a little confused by your query since its just a select all that specifies some field names. The ES SQL query is unnecessary from what I see here.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 11, 2019, 3:19pm UTC](https://discuss.elastic.co/t/date-format-mismatch-in-canvas-elasticsearch-sql/202317/5 "2019-11-11T15:19:16Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.