# Kibana essql query date diff
**URL:** https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483
**Category:** Kibana
**Tags:** canvas
**Created:** [April 28, 2021, 8:28am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483 "2021-04-28T08:28:53Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [April 28, 2021, 8:28am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/1 "2021-04-28T08:28:53Z")
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
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 4, 2021, 8:46am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/2 "2021-05-04T08:46:12Z")
Any hints?
---
### Author:  [@lukas](https://discuss.elastic.co/u/lukas)
#### Post date: [May 4, 2021, 6:31pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/3 "2021-05-04T18:31:27Z")
I think so, could you try something like this:
```sql
SELECT deviceID, DATE_TRUNC('day', date) AS day, DATEDIFF('hours', MIN(date), MAX(date)) AS duration
FROM "devices"
GROUP BY deviceID, day
```
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 5, 2021, 8:13am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/4 "2021-05-05T08:13:47Z")
I found the query:
```auto
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?
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [May 5, 2021, 2:23pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/5 "2021-05-05T14:23:56Z")
You need to post the full Expression to Tell you that
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 5, 2021, 2:40pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/6 "2021-05-05T14:40:14Z")
```auto
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
```
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [May 5, 2021, 3:50pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/7 "2021-05-05T15:50:36Z")
x needs to be dd then and not date or am I wrong?
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 5, 2021, 4:53pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/8 "2021-05-05T16:53:26Z")
yes, dd needs to be on x axes
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 6, 2021, 10:47am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/9 "2021-05-06T10:47:05Z")
I've written this:
```auto
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...
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [May 6, 2021, 11:16am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/10 "2021-05-06T11:16:22Z")
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
```
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 6, 2021, 12:47pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/12 "2021-05-06T12:47:00Z")
I'm almost there...
```auto
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
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 7, 2021, 3:14pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/13 "2021-05-07T15:14:18Z")
Any hint??
---
### Author:  [@Felix\_Roessel](https://discuss.elastic.co/u/Felix_Roessel)
#### Post date: [May 15, 2021, 4:38am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/14 "2021-05-15T04:38:46Z")
There is a sort function available: [Canvas function reference | Kibana Guide [7.12] | Elastic](https://www.elastic.co/guide/en/kibana/current/canvas-function-reference.html#sort_fn)
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?)
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [May 15, 2021, 9:37am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/15 "2021-05-15T09:37:52Z")
Hi Felix,
I already tried the sort function....nothing change.
Current situation:
with expression:
```auto
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:
```auto
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:
```auto
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...
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [June 1, 2021, 7:21am UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/16 "2021-06-01T07:21:39Z")
Help please...
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [June 16, 2021, 12:03pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/17 "2021-06-16T12:03:47Z")
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)`
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [June 16, 2021, 12:21pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/18 "2021-06-16T12:21:57Z")
My code:
```auto
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...
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [June 16, 2021, 12:29pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/19 "2021-06-16T12:29:48Z")
Can you explain what you want to see?
---
### Author:  [@accateo](https://discuss.elastic.co/u/accateo)
#### Post date: [June 16, 2021, 12:44pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/20 "2021-06-16T12:44:47Z")
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
---
### Author:  [@flash1293](https://discuss.elastic.co/u/flash1293)
#### Post date: [June 16, 2021, 12:49pm UTC](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483/21 "2021-06-16T12:49:41Z")
I meant what's wrong with the output you screenshotted in the post above
[Next page](https://discuss.elastic.co/t/kibana-essql-query-date-diff/271483.md?page=2)