Display a date in a Markdown widget?

I have a SQL query associated with my Canvas Markdown widget that returns a single value that is a date. I'm struggling to see how to display this date in a Markdown widget. {{MyDate}} doesn't display anything, even though in the SQL preview the MyDate value is correct. Apparently, Markdown is expecting to see the date as a string as opposed to a date field. Any examples of how to display the MyDate value?

Hi @gbarnett,

It's hard to tell without knowing the data shape and the Canvas expression you have. Since SQL query usually returns a number of rows, you need to iterate over them to be able to retrieve the value in Markdown, e.g. something like this:

{{#each rows}}
Time: {{timestamp}}

Firstly, I'm using a Markdown widget in my canvas. Secondly, the dataset associated with this widget is a simple query that returns a single date value, called MyDate. If I PREVIEW the query, I can see where it is indeed returning a single value (2019-05-01T01:00:00+01:00), and that value has the correct date from the database. However, if I specify Date: {{MyDate}} in the Markdown, it doesn't complain, but doesn't display anything.

Can you share the entire expression with the SQL query?

MyDate is defined in my document mapping as ‘date’. In the markdown widget, my dataset is SELECT MyDate from MyDataset LIMIT 1

My Markdown is Date: {{MyDate}}

The value of MyDate is not displayed. However, it definitely has a valid date value.

As I already said, SQL returns table with one row in your case, not scalar value. To display the cell you need to treat data as a table, e.g. using the Kibana demo data:

| essql query="SELECT timestamp as MyDate FROM \"kibana_sample_data_flights\" LIMIT 1"
| markdown "My date is {{#each rows}}{{MyDate}}{{/each}}"
| render


| essql query="SELECT timestamp as MyDate FROM \"kibana_sample_data_flights\" LIMIT 1"
| markdown {getCell MyDate}
| render

I am now able to display the number of rows returned from my query by displaying {{rows.length}}, however, using...

{{#each row}}

The date is still not being displayed. Is there an issue in that Markdown can only handle strings, and not date datatypes? In the SQL query, I can see that a single row is being returned, and the MyDate column has the correct date value.

I found the issue in my last example. It should be "rows" not "row". It was failing silently, but works now that I corrected the syntax.

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