TIme difference between first and last

[darkins]

My query returns these events, i need to compute the total time A was in this state and total time B was in this state. My thought is to subtract the TImestamp of the first A from the most recent A and so on for B but cant figure out the right way to do this?

Timestamp	Job	Date	LoggedTime	Ready
1728092168.000000	A	10/4/2024	21:36:03	1
1728092163.000000	A	10/4/2024	21:35:50	1
1728092150.000000	A	10/4/2024	21:35:27	1
1728092127.000000	A	10/4/2024	21:35:16	1
1728090335.000000	B	10/4/2024	21:05:15	2
1728090315.000000	B	10/4/2024	21:05:03	2
1728090303.000000	B	10/4/2024	21:04:53	2
1728090293.000000	B	10/4/2024	21:04:31	2

[ITWhisperer]

Assuming the timestamp you want is in _time,  you could use transaction to get the duration

| transaction Job

If not, you could reassign the _time field to be the time you want.

[PickleRick]

But since transaction is one of the cursed commands 😉 you can - assuming Job is unique - do

| stats min(_time) as start max(_time) as end by Job
| eval duration=end-start

[darkins]

ok this should work but one wrinkle, i want to do this on two fields meaning:

these are my records

Job1	10/4/2024	20241004	10/4/2024	0:38:27
Job1	10/4/2024	20241004	10/4/2024	0:38:41
Job 2	10/4/2024	20241004	10/4/2024	17:39:12
Job 2	10/4/2024	20241004	10/4/2024	17:39:24
Job 2	10/4/2024	20241004	10/4/2024	17:39:34
Job1	10/5/2024	20241004	10/4/2024	0:38:27
Job1	10/5/2024	20241004	10/4/2024	0:38:35

from this i want to be able to say:

job1 took 14 seconds on 10/4/2024 and job 2 took 22 seconds on 10/4

job 1 took 8 seconds on 10/5

[yuanliu]

You mean something like this?

| eval date = strftime(_time, "%F")
| stats min(_time) as start max(_time) as end by date
| eval duration = round(end - start)
| fields - start end

date	duration
2024-10-04	61267
2024-10-05	8

Here is the emulation

| makeresults format=csv data="jobId, date, skip1, skip2, time
Job1,	10/4/2024,	20241004,	10/4/2024,	0:38:27	 
Job1,	10/4/2024,	20241004,	10/4/2024,	0:38:41	 
Job 2,	10/4/2024,	20241004,	10/4/2024,	17:39:12	 
Job 2,	10/4/2024,	20241004,	10/4/2024,	17:39:24	 
Job 2,	10/4/2024,	20241004,	10/4/2024,	17:39:34	 
Job1,	10/5/2024,	20241004,	10/4/2024,	0:38:27	 
Job1,	10/5/2024,	20241004,	10/4/2024,	0:38:35"
| eval _time = strptime(date . " " . time, "%m/%d/%Y %H:%M:%S")
``` data emulation above ```