Hello, first i would like to thanks all the great people working on this amazing platform.
I’m using Grafana with ‘home assistant’ and i really love it.
I would like to know if it’s possible how to calculate the daily power/coast consumption of my ‘tp-link HS110’ switch.
This is what i have done so far:
when i had the 1 day interval it give me wrong values.
I don’t understand how to make it work, i’m french math dumb lost in translation.
Any kind of help would be awsome.
Thanks
Hi,
Could you please specify what measurement and unit are written to influxdb and how often, what you expect to be the result in Grafana and exactly why that is wrong. Also, if you do a manual select against influxdb do you get out what you expect? And is at least one point written correct, otherwise there may be a problem on the way in.
Marcus
Hello, thank you for your response.
The measurement and unit written to influxdb are watts in current use, the value refresh by itself regularly.
The value i’m getting from my current watts usage in influxdb are true and they are the same in the offical app from my power switch.
I would like to group this values to display the power usage per day with ‘bars’.
Unfortunatly i don’t understand how to group the watts power usage per days, has shown on the above pictures when i had the ‘1 day group by interval’ the values i’m getting are wrong.
Thanks.
How about group by time(24h) and select field(value) and use sum aggregation instead of mean().
Marcus
Thanks ‘mefraimsson’, the Bars seems to be more legit now.
I will try to edit the unit axe to display the proper value.
I think i miss some math in the process, the average should be around 3 kw per days, not 600 kw 
. Do you know how to apply this change.
Thanks.
If the average should be around 3kw per day I guess you’re displaying the wrong values in the graph. The average is basically calculated in browser based on total / number of points, i.e. 6035 / 10 = 603.5.
Marcus
Hi all,
I have to do the opposite. I have a counter that represents the total consumption therefore always counts up. I want to get the consumption by time period.
When the time interval of the data is constant and there is no missing data then it’s simple with the difference() function.
unfortunately that’s not the case.
I set-up a demo on my home server with the problem.
http://81.83.6.53:3000
Problem discription as follow:
interval of logging total consumption counter is not fixed! Demo data is once a day in the morning, with 2 ‘error’. Missing data on 2018-05-04 Multiple data on 2018-05-10, even once with same consumption counter (no consumption)
with difference() function you see the spike at 2018-05-05 (which is consumption from 2 days, because of the missing data on 2018-05-04) and you see a very low (and even zero) consumption on 2018-05-10 because of the multiple data on that day.
I already tried different plugins like histogram, different functions like difference() and integral(). None with the desired result, consumption by time period.
Time period must be flexible. Here the data is daily, but it can also come in every 15 min.
To view daily from the 15 min data, I can use sum() To view on 15min base, from daily data, I want 96 times the same consumption (spreaded out). To view the demo data (daily) on 15min base, I want for every day around 96 values who are the same. except for 2018-05-10, because there the interval of data is more frequent. The term I found latest was BUCKETIZING or PRE-BUCKETED DATA
RAW data:
select * from consumption
name: consumption
time meter_id value
2018-05-01T09:37:56Z meter1 66
2018-05-02T10:18:44Z meter1 112
2018-05-03T08:07:13Z meter1 142
2018-05-05T09:17:25Z meter1 258
2018-05-06T08:03:01Z meter1 336
2018-05-07T08:01:13Z meter1 378
2018-05-08T08:34:12Z meter1 442
2018-05-09T08:44:08Z meter1 498
2018-05-10T07:25:08Z meter1 541
2018-05-10T07:35:28Z meter1 542
2018-05-10T07:55:28Z meter1 542
2018-05-10T07:59:55Z meter1 543
2018-05-10T08:01:55Z meter1 544
2018-05-11T08:23:21Z meter1 593
2018-05-12T09:01:21Z meter1 659
Thanks in advance! Regards, Maarten
Try DERIVATE. In theory (because we don’t have access to your demo Grafana):
SELECT DERIVATIVE(value, 1d) FROM consumption
http://81.83.6.53:3000/
Forgot to mention:
user: viewer
password: viewer
normally, my grafana server is up and running…
Viewer permissions are not very comfortable. Did you read DERIVATE documentation and did you develop query or are you waiting for me to do all work for you?
certainly not! I looked at it and it seems a right step to hit the solution. When I have some spare time, I will investigate the function thoroughly. Allready massive thanks for the tip. Regards, Maarten
OK, my blind guess:
SELECT derivative(mean("value"), 1d) FROM "consumption" GROUP BY time(1d)
I hope you have skills to use it - it may still need your tweak.
Hello,
i’ve read this topic and also some similare topics because i have the identical problem.
But in all of this topics i couldn’t found a solution so I want to get in on this to solve the problem once and for all.
My setup is a influxDB and datasource is a innr sp 120 plug with power consumption. The consumption value increases in Watt. So i can’t reset this value day by day or in any other way. It always increases… actually i’m dealing with values arround 40.000W. Maybe next week it is about 42.000W.
So my data-source looks like this (mean_1 is what i try to visualize):
I would like to have a graph with one column-bar for every day, the last 30days for example showing the consumption of every single day.
If i would do this in excel it would be easy: [last value of day] - [first value of day] = [consumption of day]
But how to do this in grafana now?
I already tried the last tip from jangaraj: no data result
My actual query-string:
SELECT derivative(“gesamt”, 1d) FROM “Verbrauch” WHERE (“location” = ‘Beamer’) AND $timeFilter GROUP BY time(1d) fill(null)
Thanks for your support,
Bond
Solution was to use spread():
SELECT spread("gesamt") FROM "Verbrauch" WHERE ("location" = 'Beamer') AND $timeFilter GROUP BY time(1d) fill(null)Hey i’m having a similar use-case where the energy-meter is read out periodicaly each hour.
Only changed values are stored in the database.
To have the sum of the used energy per day spread() is the function to use.
BUT: When i’m grouping values, it will obviously ignore at least 1kWh per day as the counter changes between the last value of the previous group and the first value of the actual group.
Is there somehow the possibility to calculate the spread from the last value of the previous group and the last value of the actual group?