The question - in that query how could I calculate the total for that period (specified in the range). What I am trying to show is for the selected period the total usage is X through the query.
Hello @meaningoflife,
Welcome?
What do you mean by total? The sum? The raw total? or the total of the aggregatewindow?
If its a sum you want, the raw total would look like:
Thank you @Anaisdg for taking the time to reply. So wanted to check some logic with you on the above.
The IoT sensor is logging the actual watts being used in real time (W). What I am trying to do at the query level was to get the sum of watts for the selected period of time (in the time filter)
To answer your question, right now I was using this query for a Bucket that has different measurements (each measurement represents a IoT device) and an associated field (_field) with a value.
What I wanted to end up with was to actually calculate my cost for that period of time. So given that its storing real time Watts, I need to convert the Watts into Kilowatts hours which would be the following calculation:
Kilowatt-Hours: kWh = (watts Ă— hrs) Ă· 1,000.
I have the Watts (which is stored as the value in _field)
I should be able to calculate the hrs based on my search filter in time?
Then I would need to divide by 1000 to get to KwH.
Finally I would then need to multiply the kWh by the unit price from my municipality to get the total cost - we could use * 0.8 for now?.
Any help / example how I could do the above in the query?
In @Anaisdg 's first example, she sums up the value field.
You can then calculate further values from this value if you add maps to your query.
|> map(fn: (r) => ({ r with _kwh: r._value / 1000 }))
|> map(fn: (r) => ({ r with _cost: r._kwh * 0.8 }))
If you need to convert an integer to a float, you can use float(v: r._kwh).
But be aware that you may not have recorded each single watt value. E.g. there could have been very short network outages so that not every value that was measured also got stored in the database. Those missing values will also not be present in the sum and all further calculations.
The way I understand it, is to use a map for the value and convert it to the float so did the following (not working but would like to understand why so I can learn):
from(bucket: "HotWaterPowerConsumption")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "C7F8AFDevice0Room")
|> filter(fn: (r) => r["_field"] == "value")
// Convert the value to float
|> map(fn: (r) => ({r with _kwh: float(v: r._kwh)}))
|> map(fn: (r) => ({ r with _kwh: r._value / 1000 }))
|> group()
|> sum()
|> yield(name: "mean")
The error kind of confuses me:
runtime error @7:7-7:56: map: type conflict: int != float
Is this because it cant convert the current value to float (could be because MQTT is passing into the influx as string and it is not an int?)
You can filter out null values from the display in Grafana.
Your problem is the other way round: If you use the boiler but not all values are stored (due to network problems for examle). Then those measurements won’t be in your sum of energy/costs either.
So just checking logic here, what you referring to is boiler is still consuming but logging device is off - yes that would be an issue
Lucky the logger controls the power to the device as well so if the “logger” is off 99% chance the device will be off as well.
Just want to thank you once again!
A clarifying question, once you use the map function and “create a variable” like we did with _kwh you can use it anywhere later in the query, because it’s seen as being defined?
once you use the map function and “create a variable” like we did with _kwh you can use it anywhere later in the query, because it’s seen as being defined?
Well that’s what you do in the line that calculates the cost.
from(bucket: "HotWaterPowerConsumption")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "ApparentPower")
|> filter(fn: (r) => r["_field"] == "value")
// Convert the value to float
|> map(fn: (r) => ({ r with _kwh: float(v: r._value) / 1000.0 }))
|> map(fn: (r) => ({ r with _cost: r._value * 2.84 }))
|> aggregateWindow(every: v.windowPeriod, fn: sum, createEmpty: false)
|> group()
|> sum()
|> yield(name: "sum")
The result still doesn’t seem to be correct - Let me explain:
ApparentPower - is the measurement that is stored every the hot water device comes on and goes off. Example:
Red Boxes show when the boiler comes on and then the 0’s (purple boxes) are when it turns off.
What I ideally want to do is sum all the value when its on then work out the kWh (as its stored in Watts) as per the above query. When I perform the above query I am getting a number that doesn’t make sense.
