How to find the time difference between values in the same field

[raby1996]

Hi all,
I have a field that i am calling "code_load_date" and I am running a stats command that groups them by associated serial number and code level, so essentially the events look like this-

Serial            Code                 code_load_date
8k12              1.1                     1/01/15
--------------------------------------------------------------------         
8k13              1.2                     01/01/15
                  1.3                     01/11/15
                  1.4                     01/22/15
--------------------------------------------------------------------
8k14              1.2                     01/01/15
                  1.3                     01/15/15

and essentially I want to find the difference in the days between each date (code_load_date) that code was loaded onto a machine (serial) between each new code level so my desired results would look something like this-

    Serial            Code                 code_load_date          time_difference
    8k12              1.1                     1/01/15                       0
    ---------------------------------         
    8k13              1.2                     01/01/15                      10
                      1.3                     01/11/15                      11
                      1.4                     01/22/15
    --------------------------------
    8k14              1.2                     01/01/15                      14
                      1.3                     01/15/15

Is there any way that this can be achieved? Thank you in advance.

[somesoni2 · Accepted solution]

I'm guessing your query looks like this

your search before stats | stats values(Code) as Code values(code_load_date) as code_load_date by Serial

Try something like this

your search before stats | sort Serial code_load_date | streamstats current=f window=1 values(code_load_date) as prev by Serial | eval time_diff=round(strptime(code_load_date,"%m/%d/%y")-strptime(prev,"%m/%d/%y"))/86400)  | stats values(Code) as Code values(code_load_date) as code_load_date values(time_difference) as time_difference by Serial | fillnull value=0 time_difference

[raby1996]

It worked! Thank you