Canvas from minutes to hours

Elastic Stack Kibana
1

Hi,
in Canvas I have a SQL query that returns me a time value in minutes.
How do I turn it into "hours, days, months, etc"?

filters
| essql 
  query="SELECT uptime FROM \"device_data_bridgenuzoo\" ORDER BY date DESC LIMIT 1"
| markdown "### Uptime: ###
{{#each rows}}
 {{uptime}}
{{/each}}"
| render

Thank you

2

Hey there!

You might consider using a few mapColumn function calls in your expression to get the minutes turned into other units. You'll have to do a bit of tweaking but maybe try something like this:

filters
| essql 
  query="SELECT uptime FROM \"device_data_bridgenuzoo\" ORDER BY date DESC LIMIT 1"
| mapColumn name="days" fn={math "floor(uptime/60/24)"} 
| mapColumn name="hours" fn={math (uptime - days*60*24)/60"}
| mapColumn name="minutes" fn={math "uptime - days*60*24 - hours*60"}
| markdown "### Uptime: ###
{{#each rows}}
{{ days }} {{ hours }} {{ minutes }}
{{/each}}"
| render

Give that a shot and let me know how it works!

1 Like
3

It works! Thanks! If I would check:

if days is greater than zero, then show "days", if hours is greater than zero, then, etc....?

4

You might have to experiment with it but you could prolly add something like:

| mapColumn "display_value" fn={if {getCell "days" | gt 0} then={string {getCell "days"} " days"} else={string {getCell "hours"} " hours}}

and then you just can use that display_value in your markdown.

1 Like
5

Cattura
Cattura1482×612 34.5 KB

Mmm...I've to figure out why it doesn't work...

6

any hint?
Thanks

7

Hmm not sure what's going on but a helpful way to debug might be to temporarily remove the | markdown line which will cause the render function to render a datatable by default. You could use that to check the output and see what's being set in each row and column

1 Like
8

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