Wrong value in graph with bars


#1

Hi Community

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.

The following query is executed

for lines:

SELECT mean(“value”) FROM “Strom_Summe” WHERE $timeFilter GROUP BY time($__interval) fill(null)

for bares:

SELECT count(“value”) FROM “Strom_Summe” WHERE $timeFilter GROUP BY time(1d)

Can someone tell me what I do wrong?


#2

What aggregation did you choose - did you choose total instead of current by accident:


#3

Hi daniellee

Thanks for reply.

Aggregation i have select “count”. This is the only option which shows me the bars correctly so far.

I have post details a few days ago but no answer.


#4

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.


#5

Hi daniellee

I have now configured it to this manual.

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

And when I read the individual values of yesterday directly in influx is the value 0,0704
Why is group by time always showing the time 02:00:00?


#6

I don’t know what query and aggregation you are using now. Are you trying to get the sum or average? And what does your influx query look like now?


#7

This is my query in grafana:
SELECT difference(mean(“value”)) FROM “Strom_Summe” WHERE $timeFilter GROUP BY time(24h)

I need the sum of every day power usage.

In influx i have only query select * from Strom_Summe
And added the values of yesterday


#8

I try to make it clearer

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

I hope it is to understand where my problem is


#9

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:


#10

Hi

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.


#11

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


#12

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


#13

Just to be sure I understand:

  • Your dashboard has the Timezone field set to UTC:
  • but it only works for one graph on the dashboard?

It’s called a counter and it sounds like you are trying to show the daily rate (counter and rate are the two technical terms that are commonly used):

Can you show your query, please. You should be using this function: https://docs.influxdata.com/influxdb/v1.3/query_language/functions/#non-negative-derivative

Here are some example queries using the derivative function (which is the same as non-negative derivative except that it shows decreases in value): https://docs.influxdata.com/influxdb/v1.3/query_language/functions/#examples-of-advanced-syntax-1


#14

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)


#15

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):

If this not the problem, can you explain more please.


#16

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.


#17

So in InfluxDB, it is saved in UTC? (That’s the important part - is InfluxDB running on a server with time set to UTC?)


#18

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


#19

The docs say this:

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/)


Changing the point in time a GROUP BY takes effect InfluxDB
#20

Thanks.

Then it is saved in UTC.
Table view with settings to UTC is two hours behind.