Calculate the mean of a variable taking into account time

InfluxDB 2
, , , ,
1

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!

2

Hello,
You can’t have multiple points with the same value at the exact same timestamp in InfluxDB.
If you try to write
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

You’ll just end up with one point in the db as the additional points will overwrite the old. Are you using different tags for each similar point?

(7x25+250+250) = 675/12 = 56.2 so taking the mean accounting for all the points gives a different value.

Can you explain how you’re getting 127?

Are you looking to get the derivative?

3

Hello!
First of all thank you for your fast reply.
Teorically the timestamp of 2024-01-03 09:00:00 it was a bit different on all points:
image

image
image923×263 10.7 KB

So the mean I calculated was → ( (25km/h * 2 hours) + (250km/h * 12 hours) ) / 24 hours = 127,08 km/h

Maybe it can serve me also if I calculate in the period that I have points (16 hours) → ( (25km/h * 2 hours) + (250km/h * 12 hours) ) / 16 hours = 190,62 km/h

Thank you!