get duration time between 2 events that have nothing in common
I have a very particular need in which I want to measure the duration time between 2 events that have nothing in common. I tried with transaction and transactionize operators and I have a close result of what I am looking for, but still not what I want. This is a sample of the events I have:
row |
_messagetime |
eventName |
1 |
8/30/22 16:41 |
MergePullRequestByFastForward |
2 |
8/30/22 16:24 |
StartBuild |
3 |
8/30/22 15:47 |
MergePullRequestByFastForward |
4 |
8/30/22 15:30 |
StartBuild |
5 |
8/30/22 8:07 |
StartBuild |
6 |
8/30/22 7:51 |
MergePullRequestByFastForward |
7 |
8/29/22 16:32 |
MergePullRequestByFastForward |
8 |
8/28/22 9:48 |
StartBuild |
9 |
8/28/22 9:39 |
MergePullRequestByFastForward |
10 |
8/28/22 9:04 |
MergePullRequestByFastForward |
11 |
8/27/22 17:00 |
StartBuild |
12 |
8/27/22 16:29 |
StartBuild |
13 |
8/27/22 16:20 |
MergePullRequestByFastForward |
14 |
8/27/22 14:19 |
StartBuild |
15 |
8/27/22 14:10 |
MergePullRequestByFastForward |
16 |
8/27/22 12:14 |
MergePullRequestByFastForward |
17 |
8/26/22 13:42 |
MergePullRequestByFastForward |
18 |
8/26/22 11:24 |
MergePullRequestByFastForward |
19 |
8/26/22 9:18 |
MergePullRequestByFastForward |
20 |
8/26/22 8:11 |
MergePullRequestByFastForward |
21 |
8/25/22 16:16 |
MergePullRequestByFastForward |
22 |
8/24/22 17:58 |
StartBuild |
23 |
8/24/22 17:39 |
MergePullRequestByFastForward |
24 |
8/24/22 17:33 |
MergePullRequestByFastForward |
25 |
8/24/22 17:13 |
MergePullRequestByFastForward |
26 |
8/24/22 17:05 |
StartBuild |
27 |
8/24/22 14:22 |
MergePullRequestByFastForward |
28 |
8/24/22 12:21 |
MergePullRequestByFastForward |
29 |
8/24/22 11:18 |
MergePullRequestByFastForward |
30 |
8/24/22 9:50 |
StartBuild |
31 |
8/24/22 9:15 |
StartBuild |
What I need is get the time between ` MergePullRequestByFastForward` and `StartBuild` events, time difference should be between ` MergePullRequestByFastForward` and the next apparition of ` StartBuild`. For example: I will add a difference column and the formula used
row |
_messagetime |
eventName |
difference |
formula |
1 |
8/30/22 16:41 |
MergePullRequestByFastForward |
||
2 |
8/30/22 16:24 |
StartBuild |
||
3 |
8/30/22 15:47 |
MergePullRequestByFastForward |
37.11666667 |
diff(row2 - row3) |
4 |
8/30/22 15:30 |
StartBuild |
||
5 |
8/30/22 8:07 |
StartBuild |
||
6 |
8/30/22 7:51 |
MergePullRequestByFastForward |
16.06666666 |
diff(row5 - row6) |
7 |
8/29/22 16:32 |
MergePullRequestByFastForward |
934.5666667 |
diff(row5 - row7) |
8 |
8/28/22 9:48 |
StartBuild |
||
9 |
8/28/22 9:39 |
MergePullRequestByFastForward |
9.700000004 |
diff(row8 - row9) |
10 |
8/28/22 9:04 |
MergePullRequestByFastForward |
43.81666667 |
diff(row8 - row10) |
11 |
8/27/22 17:00 |
StartBuild |
||
12 |
8/27/22 16:29 |
StartBuild |
||
13 |
8/27/22 16:20 |
MergePullRequestByFastForward |
9.200000003 |
diff(row12 - row13) |
14 |
8/27/22 14:19 |
StartBuild |
||
15 |
8/27/22 14:10 |
MergePullRequestByFastForward |
9.316666665 |
diff(row14 - row15) |
16 |
8/27/22 12:14 |
MergePullRequestByFastForward |
124.8333333 |
diff(row14 - row16) |
17 |
8/26/22 13:42 |
MergePullRequestByFastForward |
1476.783333 |
diff(row14 - row17) |
18 |
8/26/22 11:24 |
MergePullRequestByFastForward |
1615.3 |
diff(row14 - row18) |
19 |
8/26/22 9:18 |
MergePullRequestByFastForward |
1741.3 |
diff(row14 - row19) |
20 |
8/26/22 8:11 |
MergePullRequestByFastForward |
1807.75 |
diff(row14 - row20) |
21 |
8/25/22 16:16 |
MergePullRequestByFastForward |
2762.75 |
diff(row14 - row21) |
22 |
8/24/22 17:58 |
StartBuild |
||
23 |
8/24/22 17:39 |
MergePullRequestByFastForward |
18.90000001 |
diff(row22 - row22) |
24 |
8/24/22 17:33 |
MergePullRequestByFastForward |
24.83333333 |
diff(row22 - row24) |
25 |
8/24/22 17:13 |
MergePullRequestByFastForward |
45.46666667 |
diff(row22 - row25) |
26 |
8/24/22 17:05 |
StartBuild |
||
27 |
8/24/22 14:22 |
MergePullRequestByFastForward |
162.8 |
diff(row26 - row27) |
28 |
8/24/22 12:21 |
MergePullRequestByFastForward |
283.9 |
diff(row26 - row28) |
29 |
8/24/22 11:18 |
MergePullRequestByFastForward |
347.15 |
diff(row26 - row29) |
30 |
8/24/22 9:50 |
StartBuild |
||
31 |
8/24/22 9:15 |
StartBuild |
Basically what I want to find is the time between a pull request is merged and the next build is performed.
Hopefully this is not so crazy, please let me know if you have any question.
Please sign in to leave a comment.
Comments
0 comments