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

Answer 1

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")

Answer 2

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.