# How to set max bucket in canvas?

**URL:** <https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058>\
**Category:** Kibana\
**Created:** [November 22, 2019, 11:54am UTC](https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058 "2019-11-22T11:54:50Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Yungyoung\_Ok](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yungyoung_ok/32/43465_2.png) [@Yungyoung\_Ok](https://discuss.elastic.co/u/Yungyoung_Ok)\
**Post date:** [November 22, 2019, 11:54am UTC](https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058/1 "2019-11-22T11:54:50Z")

</div>

i want to show max tps(count per seconds).  
In present i show tps in Metric(visualize).  
like below...

 ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/9/b92257bff21fe4ef73c24e594964447f6a8a84e8.png)

how to set max bucket in canvas?

---

<div class="post-metadata">

**Author:** ![Brandon\_Kobel](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/brandon_kobel/32/14829_2.png) [@Brandon\_Kobel](https://discuss.elastic.co/u/Brandon_Kobel)\
**Post date:** [November 22, 2019, 5:57pm UTC](https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058/2 "2019-11-22T17:57:45Z")

</div>

Hey @Yungyoung_Ok, you can use the "Elasticsearch SQL" data-source with Canvas and then use the Max aggregate function: [https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html#sql-functions-aggs-max](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-aggs.html#sql-functions-aggs-max)

 ![33%20AM](https://us1.discourse-cdn.com/elastic/original/3X/3/d/3d861ad082877aad41b0c9e1fdd809f9e77bd364.png)

---

<div class="post-metadata">

**Author:** ![Yungyoung\_Ok](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yungyoung_ok/32/43465_2.png) [@Yungyoung\_Ok](https://discuss.elastic.co/u/Yungyoung_Ok)\
**Post date:** [November 26, 2019, 1:57am UTC](https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058/3 "2019-11-26T01:57:55Z")

</div>

I want to count the number every second to get the maximum count.  
So i'm trying to group by my\_date\_field.

SELECT SUBSTR(CONCAT(TO\_CHAR(my\_date\_field)),4), COUNT(\*) FROM "my\_index\_name" GROUP BY SUBSTR(CONCAT(TO\_CHAR(my\_date\_field)),4)

but TO\_CHAR is unknown function...  
and i show like this..

> [@Date format parsing exception](https://discuss.elastic.co/t/date-format-parsing-exception/100892):
>
> I have create\_date column as timestamp in database and need create\_date value in elastic as "MM/DD/YYYY" or "yyyy-MM-dd" . I am getting below error exception while loading data from oracle database using logstash 2.4. SQL: select to\_date(to\_char(cast(create\_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY') from table X. exception. "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [create\_date]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"In…

my\_date\_field format is strict\_date\_time(yyy-MM-dd'T'HH:mm:ss.SSSZZ).

how can i set to get max records?

---

<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:** [December 24, 2019, 1:58am UTC](https://discuss.elastic.co/t/how-to-set-max-bucket-in-canvas/209058/4 "2019-12-24T01:58:10Z")

</div>

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