Kibana essql query date diff

Hi,
I have a series of such data in Kibana:

"deviceID" "date"
35001 27-04-21 13:20:00
35002 27-04-21 15:21:00
35001 27-04-21 18:22:00
35002 27-04-21 19:30:00

I would like to obtain as a result a bar graph in Canavas which, divided by days and deviceId,
show the difference between the various times.
Then:
35001 27-04-21 5 hours
35002 27-04-21 4 hours

It's possible with Kibana and ESSQL?
Thanks

Any hints?

I think so, could you try something like this:

SELECT deviceID,  DATE_TRUNC('day', date) AS day, DATEDIFF('hours', MIN(date), MAX(date)) AS duration
FROM "devices"
GROUP BY deviceID, day

I found the query:

SELECT DAY(date) as dd,  DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM "application_data_bridgenuzoo" WHERE tagId=XXXX GROUP BY DAY(date)

but the graph component doesn't show the day in the X axes. Why?

You need to post the full Expression to Tell you that

filters
| essql 
  query="SELECT DAY(date) as dd,  DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" WHERE tagId=35101 GROUP BY DAY(date)"
| mapColumn "dd" fn={date | formatdate "DD/MM"}
| pointseries x="date" y="diff"
| plot defaultStyle={seriesStyle bars=0.75 lines="1"} legend=false
| render

x needs to be dd then and not date or am I wrong?

yes, dd needs to be on x axes

I've written this:

filters
| essql 
  query="SELECT DAY(date) as dd,  DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" WHERE tagId=35101 GROUP BY DAY(date)"
| mapColumn "dd" fn={date | formatdate "DD/MM"}
| pointseries x="dd" y="diff"
| plot defaultStyle={seriesStyle bars=0.75 lines="1"} legend=false
| render

same result...

I think you need to do getCell in the function of mapColumn. Otherwise mapcolumn does not know which information it should parse as date...

 filters
    | essql 
      query="SELECT DAY(date) as dd,  DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" WHERE tagId=35101 GROUP BY DAY(date)"
    | mapColumn "dd" fn={getcell "dd" | date | formatdate "DD/MM"}
    | pointseries x="dd" y="diff"
    | plot defaultStyle={seriesStyle bars=0.75 lines="1"} legend=false
    | render

I'm almost there...

filters group="group3" ungrouped=true
| essql 
  query="SELECT DAY(date) as dd, MIN(date) as realDate, DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" GROUP BY DAY(date)"
| mapColumn name="formatted_date" expression={getCell "realDate" | formatdate format="DD/MM/YY"}
| pointseries x="formatted_date" y="diff"
| plot defaultStyle={seriesStyle bars=0.75 lines="1"}
| render

This is the result:

Three questions:

  • how can I reverse the order on X axes?
  • now I'm using GROUP BY DAY(date). If I would to group by day / month? How can I do?
  • how can I show more than 5 days?

Thanks

Any hint??

There is a sort function available: Canvas function reference | Kibana Guide [7.12] | Elastic

To add group by month use group by month instead of day

The graph will visualize all available data. So if there are only 5 and you like more:
Check your filters
Check your query ( SQL limits the max results if you not override)
Check your data (more than 5 days available?)

Hi Felix,
I already tried the sort function....nothing change.

Current situation:


with expression:

filters group="group3" ungrouped=true
| essql 
  query="SELECT DAY(date) as dd, MIN(date) as realDate, DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" GROUP BY DAY(date)"
| mapColumn name="formatted_date" expression={getCell "realDate" | formatdate format="DD/MM/YY"}
| pointseries x="formatted_date" y="diff"
| plot defaultStyle={seriesStyle bars=0.75 lines="1"}
| render

I tried also:

filters group="group3" ungrouped=true
| essql 
  query="SELECT DAY(date) as dd, MIN(date) as realDate, DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" GROUP BY DAY(date)"
| mapColumn name="formatted_date" expression={getCell "realDate" | formatdate format="DD/MM/YY"}
| pointseries x="formatted_date" y="diff"
| sort by="x" reverse=false
| plot defaultStyle={seriesStyle bars=0.75 lines="1"}
| render

with and without reverse...nothing.
I tried:

filters group="group3" ungrouped=true
| essql 
  query="SELECT DAY(date) as dd, MIN(date) as realDate, DATE_DIFF('hours',MIN(date),MAX(date)) as diff FROM \"application_data_bridgenuzoo\" GROUP BY MONTH(date)"
| mapColumn name="formatted_date" expression={getCell "realDate" | formatdate format="DD/MM/YY"}
| pointseries x="formatted_date" y="diff"
| plot defaultStyle={seriesStyle bars=0.75 lines="1"}
| render

but I have a error:

Found 1 problem line 1:8: Cannot use non-grouped column [date], expected [MONTH(date)]

I tried all...