Calculating average of elapsed time

Question

I want to calculate the average time duration of my events. After an event starts and ends, it sends a request to my InfluxDB in the Line Protocol Syntax:

mes1 id=1,event="start" 1655885442
mes1 id=1,event="end" 1655885519
mes1 id=2,event="start" 1655885643
mes1 id=2,event="end" 1655885914
mes1 id=3,event="start" 1655886288
mes1 id=3,event="end" 1655886372
mes1 id=4,event="start" 1655889323
mes1 id=4,event="end" 1655889490

I can query the results like this:

from(bucket: "buck1") 
  |> range(start: -1w)
  |> filter(fn: (r) => r["_measurement"] == "mes1")
  |> filter(fn: (r) => r["_field"] == "event")
  |> elapsed()

Result:

As you can see, I also get the durations between those events, not only of the events themselves.

Consequently, when I add the mean() function, I get the mean of ALL elapsed seconds:

from(bucket: "buck1") 
  |> range(start: -1w)
  |> filter(fn: (r) => r["_measurement"] == "mes1")
  |> filter(fn: (r) => r["_field"] == "event")
  |> elapsed()
  |> mean(column: "elapsed")

Result:

How Can I get the average of only the events, not the time between them?

The durations of those events are:

77 sec
271 sec
84 sec
167 sec

So the expected result is 599/4 = 149.75 seconds.

Update:

from(bucket: "buck1") 
  |> range(start: -1w)
  |> filter(fn: (r) => r["_measurement"] == "mes1")
  |> filter(fn: (r) => r["_field"] == "event"  or r["_field"] == "id")
  |> group(columns: ["id"])
  |> elapsed()
  |> group(columns: ["_measurement"])
  |> mean(column: "elapsed")

Result:

runtime error @6:8-6:17: elapsed: schema collision: cannot group string and float types together

Your example line protocol seems faulty: Here is what I had to use to successfully upload the data: `mes1,id=1 event="start" 1655885442` `mes1,id=1 event="end" 1655885519` ... Changes are between commas and whitespaces. This could also explain why grouping by id does not work for you, as your screenshots do not show a column id. — Simon, Jun 28 '22 at 11:17
After re-reading the Line Protocol docs I understand that your example line protocol makes id=1 a second field, while my version in the above comment makes it a tag. In my opinion a tag makes more sense, but I also do not know how two fields are treated in InfluxDB. In any case using a field key ("id") as a column is not possible in group(). — Simon, Jun 28 '22 at 11:29

Amerousful · Accepted Answer · 2022-06-26T11:50:27.230

2

You need to group by id and then ungroup via _measurement

|> group(columns: ["id"])
|> elapsed()
|> group(columns: ["_measurement"])
|> mean(column: "elapsed")

Update I found another solution. Need to use difference instead of elapsed

|> filter(fn: (r) => r._field == "id")
|> group(columns: ["_value"])
|> difference(columns: ["_time"])
|> group()
|> mean(column: "_time")

edited Jun 26 '22 at 11:50

answered Jun 24 '22 at 15:23

Amerousful

2,292
1
12
26

Do I also need to add the field `id`? Please see my update. I'm getting an error now. – gaout5 Jun 24 '22 at 16:24
@gaout5 Try to filter only by `id` – Amerousful Jun 24 '22 at 17:06
In this case I get the same result like before: `578.285714285714` – gaout5 Jun 24 '22 at 17:08
@gaout5 Hey! I updated my answer with another solution. I think it's a dirty way, but it works. – Amerousful Jun 26 '22 at 11:53
This time I get: `difference: difference does not support time columns. Try the elapsed function` – gaout5 Jun 27 '22 at 13:20
@gaout5 I used the same data as yours and that works https://pasteboard.co/wdZc9kyVrse9.png – Amerousful Jun 27 '22 at 13:34

score 0 · Answer 2 · answered Jun 28 '22 at 10:27

Did you try to filter after the calculation of elapsed?

from(bucket: "buck1") 
  |> range(start: -1w)
  |> filter(fn: (r) => r["_measurement"] == "mes1")
  |> filter(fn: (r) => r["_field"] == "event")
  |> elapsed()
  |> filter(fn: (r) => r["_value"] == "end")
  |> mean(column: "elapsed")

This seems to be the simplest way to get your result. Of course this assumes that you always have a sequence of start, end, start, ... As soon as this is not guaranteed, using id seems to be the more stable approach.

Calculating average of elapsed time

2 Answers2