Hello!

I am trying to calculate the mean of a variable taking into account the time. Imagine that I have this situation with a non regular frequency:

2024-01-03 07:00:00 — 0.000 km/h

2024-01-03 08:59:59 — 0.000 km/h

2024-01-03 09:00:00 — 25.000 km/h

2024-01-03 09:00:00 — 25.000 km/h

2024-01-03 09:00:00 — 25.000 km/h

2024-01-03 09:00:00 — 25.000 km/h

2024-01-03 09:00:01 — 25.000 km/h

2024-01-03 09:00:02 — 25.000 km/h

2024-01-03 11:00:00 — 25.000 km/h

2024-01-03 11:00:01 — 250.000 km/h

2024-01-03 23:00:00 — 250.000 km/h

2024-01-03 23:00:01 — 0.000 km/h

If you see I have more points around 09:00.

I want to do a query that gives me the result of 127,1km/h in the time range of all the day (from 00:00 to 00:00).

I have tried with this sample of query, but I don’t know how to continue:

from(bucket: “MY_DATA”)

|> range(start: 2024-01-03T00:00:00Z, stop: 2024-01-04T00:00:00Z)

|> filter(fn: (r) => r[“_measurement”] == “SPEED_DATA”)

|> filter(fn: (r) => r[“_field”] == “actual_speed”)

|> difference(nonNegative: false, columns: [“_value”])

|> elapsed(unit: 1ms)

Can someone help me?

Thanks!