# Canvas from minutes to hours

**URL:** <https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [April 20, 2021, 10:04am UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686 "2021-04-20T10:04:39Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![accateo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/accateo/32/85332_2.png) [@accateo](https://discuss.elastic.co/u/accateo)\
**Post date:** [April 20, 2021, 10:04am UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/1 "2021-04-20T10:04:39Z")

</div>

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

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

```

Thank you

---

<div class="post-metadata">

**Author:** ![poff](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/poff/32/81795_2.png) [@poff](https://discuss.elastic.co/u/poff)\
**Post date:** [April 20, 2021, 3:17pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/2 "2021-04-20T15:17:17Z")

</div>

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:

```auto
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!

---

<div class="post-metadata">

**Author:** ![accateo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/accateo/32/85332_2.png) [@accateo](https://discuss.elastic.co/u/accateo)\
**Post date:** [April 20, 2021, 3:33pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/3 "2021-04-20T15:33:22Z")

</div>

> [@poff](#):
>
> ```auto
> 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
> 
> ```

It works! Thanks! If I would check:

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

---

<div class="post-metadata">

**Author:** ![poff](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/poff/32/81795_2.png) [@poff](https://discuss.elastic.co/u/poff)\
**Post date:** [April 20, 2021, 3:47pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/4 "2021-04-20T15:47:27Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![accateo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/accateo/32/85332_2.png) [@accateo](https://discuss.elastic.co/u/accateo)\
**Post date:** [April 20, 2021, 4:06pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/5 "2021-04-20T16:06:25Z")

</div>

![Cattura](https://us1.discourse-cdn.com/elastic/original/3X/9/e/9ee3f834ab40932267452bd72b519be79887f79d.png)

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

---

<div class="post-metadata">

**Author:** ![accateo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/accateo/32/85332_2.png) [@accateo](https://discuss.elastic.co/u/accateo)\
**Post date:** [April 21, 2021, 2:43pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/6 "2021-04-21T14:43:30Z")

</div>

any hint?  
Thanks

---

<div class="post-metadata">

**Author:** ![poff](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/poff/32/81795_2.png) [@poff](https://discuss.elastic.co/u/poff)\
**Post date:** [April 21, 2021, 3:16pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/7 "2021-04-21T15:16:25Z")

</div>

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

---

<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:** [May 19, 2021, 3:16pm UTC](https://discuss.elastic.co/t/canvas-from-minutes-to-hours/270686/8 "2021-05-19T15:16:26Z")

</div>

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