Kibana essql query date diff

Elastic Stack Kibana
1

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

2

Any hints?

3

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
4

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?

5

You need to post the full Expression to Tell you that

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

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

8

yes, dd needs to be on x axes

9

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

10

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
12

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:

Cattura
Cattura1044×317 17.2 KB

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

13

Any hint??

14

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

15

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

Current situation:

Cattura1
Cattura1733×235 10.5 KB

Cattura2
Cattura2957×386 24.6 KB

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

16

Help please...

17

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)

18

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:

Cattura
Cattura1183×412 17.2 KB

My data:

Cattura2
Cattura21212×560 53.8 KB

Something wrong...

19

Can you explain what you want to see?

20

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

21

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