Date Format Mismatch in Canvas (Elasticsearch SQL)

Elastic Stack Kibana
1

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?
2

@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.

3

@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.

4

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

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.

5

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