# Canvas Chart Area using count/sum and Timestamp
**URL:** https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608
**Category:** Kibana
**Tags:** canvas
**Created:** [July 5, 2024, 10:11am UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608 "2024-07-05T10:11:11Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Mang0](https://discuss.elastic.co/u/Mang0)
#### Post date: [July 5, 2024, 10:11am UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608/1 "2024-07-05T10:11:11Z")
Hi !
I'm trying to use an area chart in canvas, with timestamp data on x and a count or a sum on y.
The two cases I'm trying to set up:
- The sum :
with this SQL query:
```auto
SELECT round(sum(nonTaxedPrice),3) as caht, startDate as date FROM "vendors" group by date
```
- The count
with this SQL query :
```auto
SELECT startDate as date, count(*) as c FROM "vendors" WHERE
nonTaxedPrice=0 GROUP BY date
```
In both cases, the area that is created does not correspond to the data...
My theory is that **grouping by date is unuseful** (timestamps are all different), but when I tried to take only the day/month value, all the dates were in disorder, and time filter did not work anymore on it because the value returned by DATETIME\_FORMAT is a string (and cast does not work on it) :
```auto
SELECT DATETIME_FORMAT(CAST(startDate AS DATE), 'dd/MM/YYYY') as date, count(*) as c FROM "vendors" WHERE nonTaxedPrice=0 GROUP BY date
```
Does anyone has an idea about how to solve this ?
---
### Author:  [@jsanz](https://discuss.elastic.co/u/jsanz)
#### Post date: [July 5, 2024, 10:47am UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608/2 "2024-07-05T10:47:51Z")
So the issue is how to do a `GROUP BY` by dates, right?
With the Kibana Sample Data Flights I got working this example SQL to do a count and average of a metric group by day. The trick is to create a string representation of my time field (`timestamp`) and then parsing it again as a date so all data points are moved to exactly the same time of the day (`00:00:00`) and the all points get in the same bucket.
```auto
GET _sql?format=txt
{
"query": """
SELECT
DATE_PARSE(
DATETIME_FORMAT("timestamp", 'yyyy-MM-dd'),
'yyyy-MM-dd'
) as date,
COUNT(1) as "count",
ROUND(AVG(AvgTicketPrice),2) as "avg_ticket"
FROM "kibana_sample_data_flights"
GROUP BY "date"
ORDER BY "date"
"""
}
```
This feels a bit hacky, I think doing a regular Elasticsearch DSL query with normal time aggregation should work much better.
---
### Author:  [@Mang0](https://discuss.elastic.co/u/Mang0)
#### Post date: [July 5, 2024, 1:10pm UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608/3 "2024-07-05T13:10:29Z")
Unfortunatly i can't seem to make this work in the SQL editor of Canvas I tried this :
```auto
SELECT
DATE_PARSE(
DATETIME_FORMAT(tx.startDate, 'yyyy-MM-dd'),'yyyy-MM-dd') as date,
FROM "vendors_cockpit-tx_active" WHERE tx.valorization.nonTaxedPrice=0
```
I checked, the DATETIME\_FORMAT is returning date string no problem here, but when i add the DATE\_PARSE, i get the generic error "[essql] \> Unexpected error from Elasticsearch: undefined - undefined" which is not helpful.
Thank you for the help! I will try to find another solution
---
### Author:  [@Mang0](https://discuss.elastic.co/u/Mang0)
#### Post date: [July 5, 2024, 1:15pm UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608/4 "2024-07-05T13:15:21Z")
I found a solution !
I used DATE\_TRUNC which in fact does exactly what the combination DATE\_PARSE and DATE\_FORMAT does :
```auto
SELECT DATE_TRUNC('days', startDate::datetime) as date,
count(*) as c FROM "vendors"
WHERE nonTaxedPrice=0 group by date
```
With this all my timestamps are formated : [Date trunc Documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-functions-datetime.html#sql-functions-datetime-trunc)
---
### Author:  [@jsanz](https://discuss.elastic.co/u/jsanz)
#### Post date: [July 5, 2024, 1:38pm UTC](https://discuss.elastic.co/t/canvas-chart-area-using-count-sum-and-timestamp/362608/5 "2024-07-05T13:38:18Z")
I should have found that one 🤦♂️
Glad you find a better way!