i use grafana with influxdb and I’m collecting values from OpenHab.
Database in very simple, 2 columns:
time
value
Openhab save values for power consumption in €.
The graph with the lines shows correct values but the graph with the bars show a much too high value.
I would like to see the daily electricity costs.
Oh, sorry thought you had chosen series on the x-axis.
Can you show a screenshots of your axes and display tabs and also what does the raw data look like? This guide describes how to get the raw data from Chrome Dev Tools.
And it looks good.
But the value is by “group by time 24h” not exactly.
When i group by time 24h the valus from yesterday is 0,05€.
But the real consumption from yesterday is 0,07€
See screenshot without group by time.
Time jun. 12, 2017 00:00:00 to jun. 12, 2017 23:59:00
In influx i have query
SELECT difference(mean(“value”)) FROM “Strom_Summe” WHERE time > now() - 14d GROUP BY time(1d)
The result is: example yesterday
2017-06-11T00:00:00Z 0.03717413708767059
2017-06-12T00:00:00Z 0.051058803078753434
2017-06-13T00:00:00Z 0.06876347194776133
With query “SELECT * FROM Strom_Summe” i see each value for each day
The first value from yesterday is:
2017-06-12T00:00:00.175Z 0.57888
And the last is:
2017-06-12T23:00:03.181Z 0.64935
The difference is 0,07047 and not 0.051058803078753434
Oh interesting. I’ve never used the difference function before I would usually use the similar derivative or non-negative derivative functions. The derivative functions do a difference aggregation and then convert it to a rate of change (per second or per hour for example).
You are calculating the average (mean) for a day, is that what you want? So SELECT difference(mean("value")) is very different from SELECT *. I think what that does is take the average for a day and then compare to the average of the day before and show the difference. I don’t know anything about openhab so I’m not sure what it should be.
The reason is that it is adjusted to your time zone - GMT +2 (taken from your browser settings). You can adjust that per dashboard by changing timezone to UTC:
I have use the difference function because it was so described here.
Now i have try it with derivate but it shows me wrong values.
What type of aggregation I have to choose?
I do not want the average i need the exactly value from each day.
Mean is the wrong aggregation I know that now.
What is the right setting to display the daily consumption correctly?
Thanks for the clarification because of the time zone.
I also have problem with time stamp to be 02:00:00. When I changed to UTC my bars are OK but all other graphs are showing 2 hours behind.
I am trying to get power consumption to show how much power I used each day. The value I get from influx is a “growing” value, don’t know the correct name for that but it works like this:
Day 1 starts at 0 kWh, I use 10 kWh so Day 2 will start at 10 kWh and I use 5 kWh then Day 3 will start on 15 kWh and so on.
My Rain gauge value is working on the same principial.
Grafana 4.5.2 running on Debian
Influxdb as datasource. Data collectiion with Domoticz and MySensors
I tried to install 4.6.0 beta, because there was some change for timezone but it didn’t help.
I can confirm that the data is from 02:00 until 02:00, I checked my raw data.
2017-10-21 02:00:00 is still increasing but now time is 2017-10-22 00:33, this means that data is reed from 02:00 until 02:00. I also calucalted manually from raw data between 2017-10-20 02:00 -> 2017-10-21 02:00 and I get the value that Grafana shows.
I tried lines and dots but still wrong. Tried 1h, 2h and 10h as group time and then it is showing correct value.
This must be something with group time 24h
Thanks for answering.
If I change to UTC in this Dashboard, time stamp for Bars will be stamped at 00:00 but for other graphs in same Dashboard will have wrong time stamp.
I checked raw data and data for bars is using data for 02:00 and not 00:00 as I want to use. Even with UTC it is using data from 02:00.
Maybe this snapshot can help. My local time was 13:54 when I created the snapshot. Sweden, summer time right now so UTC +2 otherwise UTC +1. https://snapshot.raintank.io/dashboard/snapshot/35g2NOUN507NZlu2KZsobxQQ19mNxyKd?orgId=2
Query: SELECT non_negative_derivative(last("value"), 1d) FROM "Usage" WHERE ("name" = 'Huset_V') AND $timeFilter GROUP BY time($Interval) fill(null)
When you are saving the data, are you saving it in UTC?
Currently Grafana is not very flexible when it comes to time zones and is built on the assumption that everyone saves their data in UTC. It then uses the browser settings to adjust to your timezone. This will be wrong if you are saving it in another timezone.
If this is the case, then you can offset the timezone in your InfluxDB query (think this got added in 1.3):
I guess it must be in UTC, because when I choose UTC it will be 11.00 when my time is 13.00.
But I don’t know if it will send in UTC. I am using Domoticz to send data to InfluxDB.
Influx is running on a Debian where I have set time zone to Sweden.
I cannot answer if the data is saved in UTC.
But when I choose “Local browser time” all other graphs are OK except when a graph is set to “GROUP BY time(1d)” then it will have wrong time stamp
InfluxDB uses a host’s local time in UTC to assign timestamps to data and for coordination purposes.
So I’m not sure if it is saving in Swedish time or UTC time. Both InfluxDB and Grafana expect times to be saved in UTC. If the timestamps are not in UTC, you are going to have to offset the group by time with this advanced group by syntax.
To double check if your data is saved in UTC or not: create a table panel and write a simple select query. Are the values in the Time column UTC or local time? If the dashboard setting is UTC then it should be two hours behind, if the dashboard setting is local browser time then it should be the same as Swedish time.
(you can also use the Query Explorer on the Metrics tab to get the raw epoch values from InfluxDB and convert those to datetime using https://www.epochconverter.com/)
