Time subtraction from different jobs

[BenjamT]

Hi everyone,

I am currently facing an issue so I'm coming here to ask for your help. My issue is basic :

I get the data from differents JOBS. Here is an example of the data i'm getting :

Name="JOB1"  StartTime="2021-09-16 05:10:45+02" EndTime="2021-09-16 06:10:45+02"

Name="JOB2"  StartTime="2021-09-16 06:08:45+02" EndTime="2021-09-16 09:10:45+02"

As you can see, JOB1 start sooner than JOB2 but, JOB2 finishes later. I would like to have in a table in THE SAME ROAD the difference bewteen StartTime of JOB1 and EndTime of JOB2, in this case, the result should be then around 4H or 280 minutes (format Hour or minutes doesn't matter)

I can't figure out how to it, so thank you all in advance 😉

[BenjamT]

Thank you for your answer, I should specify :

I get the data from the last 8 week and the jobs are running daily, which means that I get a table of data like this : 

Jobs are always running the same day, so you always get job1 start and end, job 2 start and end,

So what I would like to get is a table showing something like :

JOB1	JOB2	JOB1 START TIME	JOB2 END TIME	JOB2ENDTIME - JOB1 STARTTIME	DATE OF RUN (ex 09-22-2021)
etc	etc	etc	etc	etc

I also get the date of run from the data, I just didn't show it in the screenshot

[ITWhisperer · Accepted solution]

| eval starttime=if(jobname="job1",strptime(StartTime,"%Y-%m-%d %H:%M:%S"),null())
| eval endtime=if(jobname="job2",strptime(EndTime,"%Y-%m-%d %H:%M:%S"),null())
| stats min(starttime) as starttime max(endtime) as endtime by date job
| eval difference=tostring(endtime-starttime,"duration")

[BenjamT]

It looks like it is doing the job, thank you so much 🙂

[ITWhisperer]

| eval starttime=strptime(StartTime,"%Y-%m-%d %H:%M:%S")
| eval endtime=strptime(EndTime,"%Y-%m-%d %H:%M:%S")
| stats min(starttime) as starttime max(endtime) as endtime
| eval difference=tostring(endtime-starttime,"duration")

[PickleRick]

I'm not sure if I understand correctly, but I tnink that OP would like to calculate "aggregate start time" and stop time for multiple series of overlapping jobs. Not just global start/end.