Kibana essql query date diff

accateo · April 28, 2021, 8:28am

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

accateo · May 4, 2021, 8:46am

Any hints?

lukas · May 4, 2021, 6:31pm

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

accateo · May 5, 2021, 8:13am

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?

Felix_Roessel · May 5, 2021, 2:23pm

You need to post the full Expression to Tell you that

accateo · May 5, 2021, 2:40pm

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

Felix_Roessel · May 5, 2021, 3:50pm

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

accateo · May 5, 2021, 4:53pm

yes, dd needs to be on x axes

accateo · May 6, 2021, 10:47am

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...

Felix_Roessel · May 6, 2021, 11:16am

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

accateo · May 6, 2021, 12:47pm

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

accateo · May 7, 2021, 3:14pm

Any hint??

Felix_Roessel · May 15, 2021, 4:38am

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

accateo · May 15, 2021, 9:37am

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...

accateo · June 1, 2021, 7:21am

Help please...

flash1293 · June 16, 2021, 12:03pm

You need to do the sort before pointseries and specify the column name instead of x. Also, what Felix wanted to say is to use GROUP BY month(date) instead of day(date)

accateo · June 16, 2021, 12:21pm

My code:

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) LIMIT 10"
| mapColumn name="formatted_date" expression={getCell "realDate" | formatdate format="DD/MM"}
| sort by="x" reverse=false
| pointseries x="formatted_date" y="diff"
| plot defaultStyle={seriesStyle bars=0.75}
| render

The result:

My data:

Something wrong...

flash1293 · June 16, 2021, 12:29pm

Can you explain what you want to see?

accateo · June 16, 2021, 12:44pm

I have a series of such data :

"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

flash1293 · June 16, 2021, 12:49pm

I meant what's wrong with the output you screenshotted in the post above