Difference between two date by field

[tahasefiani]

Hello,This is my query

| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
|eval show=if(STEP="show",strftime(_time, "%Y-%m-%d %H:%M:%S"),NULL),click=if(STEP="Click",strftime(_time, "%Y-%m-%d %H:%M:%S"),NULL)
|stats max(show) as show ,min(click) as click by client

I have two date : show and click
i want to calculate the difference between the two date by client

Exemple of the result :

client : RYU5890
Show : 2020-02-24 10:15:00
click: 2020-02-24 10:20:00
Diff: 5

client : FH5Y411
Show : 2020-02-24 09:20:00
click: 2020-02-24 09:21:00
Diff: 1

[somesoni2 · Accepted solution]

Give these a try

| loadjob savedsearch="myquery"
 |where strftime(_time, "%Y-%m-%d") = "2020-02-24"
 |eval show=if(STEP="show",_time, null()) ,click=if(STEP="click",_time, null()) 
 |stats max(show) as show ,min(click) as click by client
 | eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show)  timeformat="%Y-%m-%d %H:%M:%S"

OR

| loadjob savedsearch="myquery"
|where strftime(_time, "%Y-%m-%d") = "2020-02-24"
| eval {STEP}=_time
|stats max(show) as show ,min(click) as click by client
| eval Diff=tostring(click-show,"duration")
| convert ctime(click) ctime(show)  timeformat="%Y-%m-%d %H:%M:%S"

[tahasefiani]

Thank you 😉