Display a date in a Markdown widget?

(Gary E. Barnett) #1

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?

#2

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}}
{{/each}}
(Gary E. Barnett) #3

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.

#4

Can you share the entire expression with the SQL query?

(Gary E. Barnett) #5

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.

#6

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:

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

or

filters
| essql query="SELECT timestamp as MyDate FROM \"kibana_sample_data_flights\" LIMIT 1"
| markdown {getCell MyDate}
| render
(Gary E. Barnett) #7

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

{{#each row}}
{{MyDate}}
{{/each}}

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.

(Gary E. Barnett) #8

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.