Canvas Chart Area using count/sum and Timestamp

Elastic Stack Kibana
1

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 :
    image
    with this SQL query:
SELECT round(sum(nonTaxedPrice),3) as caht, startDate as date FROM "vendors" group by date
  • The count
    image
    with this SQL query :
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) :

SELECT DATETIME_FORMAT(CAST(startDate AS DATE), 'dd/MM/YYYY') as date, count(*) as c FROM "vendors" WHERE nonTaxedPrice=0 GROUP BY date

image

Does anyone has an idea about how to solve this ?

2

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.

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

image
image1043×731 110 KB

This feels a bit hacky, I think doing a regular Elasticsearch DSL query with normal time aggregation should work much better.

1 Like
3

Unfortunatly i can't seem to make this work in the SQL editor of Canvas I tried this :

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

4

I found a solution !
I used DATE_TRUNC which in fact does exactly what the combination DATE_PARSE and DATE_FORMAT does :

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

1 Like
5

I should have found that one :man_facepalming:

Glad you find a better way!

1 Like