Hi all,
I have a lookup like this.
caseid date
a 19-01-01 15:54:43.934000000
b 19-01-01 16:54:43.934000000
c 19-01-01 17:54:43.934000000
d 19-01-01 18:54:43.934000000
e
f
g
.
.
.
.
.
I did this command
| inputlookup test1
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N")
| stats min(date) as starttime max(date) as endtime by caseid
| eval diff =endtime-starttime
| stats avg(diff) as average
my result is like this.(this is dummy)
average
999999.9999999
I want to get results like this.
average
3600
(this is 1 hour)
I understand I have to convert, but I don't know how to convert date differences to seconds.
Could you help me?
Like this:
| inputlookup test1
| eval date=strptime(date,"%y-%m-%d %H:%M:%S.%9N")
| stats range(date) AS diff BY caseid
| stats avg(diff) AS average
| fieldformat average = tostring(average, "duration")