InfluxDB 2.x integral() test

influxdb2-integral.md

Test 1

Test Data:

• Sat Mar 04 2023 09:40:28 - 0W
• Sat Mar 04 2023 09:44:28 - 100W (+4 min / +240 sec)
• Sat Mar 04 2023 09:50:28 - 200W (+6 min / +360 sec)
• Sat Mar 04 2023 09:55:28 - 0W (+5 min / +300 sec)

test watts=0 1677922828000000000
test watts=100 1677923068000000000
test watts=200 1677923428000000000
test watts=0 1677923728000000000

• 100 W for 6 min = 36000 Ws
• 200 W for 5 min = 60000 Ws

= 96000 Ws = 26,66666667 Wh

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> integral(unit: 1h, column: "_value")

= 26.67 Wh

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> map(fn: (r) => ({r with duration: uint(v: r._time) / uint(v: 1000 * 1000 * 1000)})) // Sekunden
  |> difference(columns: ["duration"])
  |> map(fn: (r) => ({r with _value: r._value * float(v: r.duration) / 3600.0})) // Wh
  |> sum()

= 26.67 Wh

---

Problem:

• 100 W * 4 min + 200 W * 6 min = 1600 Wm (correct calculation)
• 100 W * 6 min + 200 W * 5 min = 1600 Wm (wrong calculation - but same result. Changing test data...)

Test 2

Test Data:

• Sat Mar 04 2023 09:40:28 - 0W
• Sat Mar 04 2023 09:44:28 - 500W (+4 min / +240 sec)
• Sat Mar 04 2023 09:50:28 - 50W (+6 min / +360 sec)
• Sat Mar 04 2023 09:55:28 - 0W (+5min / +300 sec)

test watts=0 1677922828000000000
test watts=500 1677923068000000000
test watts=50 1677923428000000000
test watts=0 1677923728000000000

• 500 W for 6 min = 180000 Ws
• 50 W for 5 min = 15000 Ws

= 195000 Ws = 54,16666667 Wh

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> integral(unit: 1h, column: "_value", interpolate: "")

= 46.25 Wh (wrong)

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> integral(unit: 1h, column: "_value", interpolate: "linear")

= -62.30 Wh (wrong)

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> map(fn: (r) => ({r with duration: uint(v: r._time) / uint(v: 1000 * 1000 * 1000)})) // Sekunden
  |> difference(columns: ["duration"])
  |> map(fn: (r) => ({r with _value: r._value * float(v: r.duration) / 3600.0})) // Wh
  |> sum()

= 38.33 Wh (wrong)

Workaround ?

Since we need the difference to the next value (instead of the previous), I've had the idea to reverse the result:

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:30:19Z, stop: 2023-03-04T09:59:19Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> sort(columns: ["_time"], desc: true)
  |> map(fn: (r) => ({r with duration: uint(v: r._time) / uint(v: 1000 * 1000 * 1000) * uint(v: -1)})) // Sekunden
  |> difference(columns: ["duration"], keepFirst: true)
  |> map(fn: (r) => ({r with _value: r._value * float(v: r.duration) / 3600.0})) // Wh
  |> sum()

= 54.17 Wh

https://forum.iobroker.net/topic/65138/influxdb2-watt-zu-wh-summieren/27

Test 2.5

Setting the time rang exactly to the first (1677922828000000000) and last (1677923728000000000) entry:

from(bucket: "smarthome")
  |> range(start: 2023-03-04T09:40:28Z, stop: 2023-03-04T09:55:29Z)
  |> filter(fn: (r) => r["_measurement"] == "test")
  |> filter(fn: (r) => r["_field"] == "watts")
  |> integral(unit: 1h, column: "_value", interpolate: "linear")

= 46.25 Wh (wrong)