I have - in my humble opinion - the following nasty problem which I have tried to solve in many different ways, but always without success. Maybe an expert can provide a solution or hint where I can get further information to solve the problem:
I use Grafana combined with InfluxDB to monitor the power consumption of specific devices in Home Assistant like my coffee machine, which will be the device of choice for the following illustration. The individual figures “left” (coffee01) and “middle” (coffee02) reflect the correct values, i.e., I had a coffee around 08:46 (costs 0.005€) and another one at 11:08 (costs 0.0125€). By selecting the time range to cover both events, Grafana should display the total costs 0.005€ + 0.0125€ = 0.0175€. However, this is not the case as figure “right” illustrates, where the value is 0.2623€ which is totally wrong. After each coffee, a smart plug disconnects the coffee machine. The problem seems that the logged data for the disconnected plug is not treated as zero. So, starting from the last value before the disconnect at coffee01, Grafana integrates the data until the beginning of coffee02 which is not really how it should be. Any ideas how to fix this problem?
When you choose ‘time series’ for graphs, there is option in visualitzation which tells you to consider null values as zero. Try that option so that the graph will not consider any trend in between and will not accumulate the values which are originally zero.
I think the dashboard it right, because this is time series, you can not add time 08:46 with 11:08, the time bucket should be in the same time range, remember this is timeseries.
The figure “right” using different time with figure “left”. If you see the time between figure “middle” and “right” is slightly same, and the value of the figure “right”, when you change the “left” into the same time range, it will be correct.
Sorry, but I don’t know what you mean exactly. Maybe the dashboard is right and I am just understanding it wrong or interpreting the data wrong. However, I think my kind of understanding of how the calculation of total costs should be is correct, but maybe my implementation into Grafana is wrong and that’s why I am very grateful for every help/hint.
How it should be from my perspective in real life. Imagine you go for at coffee between 08:46 and 08:49:30. The total energy in Joule follows from the integral of the power (Watt) over the time span of 210 seconds. The result reflects the area below the power curve within that time span measured in Joule = Watt*Seconds. Let’s say the result is x*Watt*Seconds. This corresponds to (x/(3600*1000))*kiloWatt*Hours. The costs are 0.17€ per kiloWatt*Hour. So the total costs must be costs1=x*0.17/(3600*1000), right? The same holds for the second coffee, let’s say costs2. Now, if you have two coffees, the total costs must be simply costs1+costs2, right? That’s how it should be in real life, but maybe my implementation into Grafana is wrong.
In other words, i.e., mathematically speaking. If you integrate the power over the time span covering both coffees, the total energy consumption must be simply the superposition of both individual energy values, assumed that the energy consumption between the end of the first coffee and the beginning of the second coffee is zero.
Again, maybe my implementation is wrong and I am very grateful if someone can point me out where I have made the mistake.
As one can see, between coffee01 and coffee02 no power/energy consumption is reported. However, according to the right plot in the first posting of this thread, the costs are still increasing linearly within this period. This cannot be correct.
I have done it and studied every single plot carefully. I have also checked the original data that the smart plug reports, i.e., the plug really reports 0W when the plug is turned off. My conclusion is that it seems like the zero data points (0 Watt) are filtered away either in InfluxDB or Grafana. So there are no data entries between about 09:00 and about 11:00. Since the zero data points are missing, Grafana then connects the last data point when the plug was turned off (at about 09’something o’clock) and the first data point right after the plug is turned on (around 11’something o’clock) -> integration yields a linear increase …
I guess your power meter doesn’t report 0 values. So just use some aggregations and “normalize” timeseries e.g. “SELECT MEAN(value) … GROUP BY time(1m) fill(0)” so that adds zero values for missing data. Then use subqueries where you do a rest of math operations, e.g.:
SELECT cumulative_sum(integral(“value”)) / 3600 … FROM (
SELECT MEAN(value) … GROUP BY time(1m) fill(0)
) GROUP …
In query editor panel you can write a query which has sub-query in it. Simply as we do -
Select foo.a from (
select a from actual_table) as foo @jangaraj has already given the example
Consider to use SingleStat panel and you can do sum, then the result in the timerange will sum all the values. Also use graph mode: Area to indicate the data occurred in the timeseries in the panel.
thanks for your replies. I have tried to implement jangaraj suggestion, but I failed miserably. I am still stuck at the subqueries part. Based on other examples found by Google, I have tried, but still failed to find the correct syntax for the subquery. The syntax for the first part works and looks like
SELECT mean(“value”) FROM “W” WHERE (“entity_id” = ‘gosund09_power’) AND $timeFilter GROUP BY time(1m) fill(0)
How must the second part look like?
Sorry for bothering again, but without an additional hint I will probably never find the correct syntax.
SELECT cumulative_sum(integral(“m_value”)) / 3600 FROM (
select $__timeGroupAlias(<time_column_name>,‘1m’, 0), mean(“value”) as m_value from <table_name> where $timeFilter and “entity_id” = ‘gosund09_power’)
__timegroupAlias is a in-built function for time bucket in grafana, just like __timefilter.
Or you can also try -
SELECT cumulative_sum(integral(“m_value”)) / 3600 FROM (
select mean(“value”) as m_value from <table_name> where $timeFilter and “entity_id” = ‘gosund09_power’ GROUP BY time(1m) fill(0))
I have tried both of your suggestions. The first one yielded the error “InfluxDB Error: error parsing query: missing parameter: __timegroupAlias”. The second one results in a different error message “InfluxDB Error: error parsing query: found <, expected identifier at line 1, char 95”.
SELECT cumulative_sum(integral(“m_value”)) / 3600 FROM ( select $__timeGroupAlias("time",‘1m’, 0), mean(“value”) as m_value from "W" where $timeFilter and “entity_id” = ‘gosund09_power’)
SELECT cumulative_sum(integral(“m_value”)) / 3600 FROM ( select mean(“value”) as m_value from "W" where $timeFilter and “entity_id” = ‘gosund09_power’ GROUP BY time(1m) fill(0))
Ok, this is really strange. Exact copy&paste of this
SELECT cumulative_sum(integral("m_value")) / 3600 FROM ( select $__timeGroupAlias("time",'1m', 0), mean("value") as m_value from "W" where $timeFilter and "entity_id" = 'gosund09_power')
SELECT cumulative_sum(integral("m_value")) / 3600 FROM ( select mean("value") as m_value from "W" where $timeFilter and "entity_id" = 'gosund09_power' GROUP BY time(1m) fill(0))
into my Grafana 5.1.1 installation yields InfluxDB Error: error parsing query: missing parameter: __timeGroupAlias
I think __timeGroupAlias works with postgres data source. For InfluxDb, you have to try different - ‘derivative’.
Here more information on writing queries in grafana for InfluxDb - InfluxDB data source | Grafana documentation
I am still stuck at the subqueries part. Based on other examples found by Google, I have tried, but still failed to find the correct syntax for the subquery. The syntax for the first part works and looks like