Different graph between Flux and InfluxQL and Homeassistant History

Grafana Time Series Panel
1

Hi folks,

I hope I could explain my situation well and you can enlighten me where I’ve made the mistake.

  • What Grafana version and what operating system are you using?
    I use Grafana 9.5.1 on Ubuntu 22.10 - 5.15

  • What are you trying to achieve?
    I would expect to have with “flux” the same chart as I have with “InfluxQL” and the history from Home Assistant. See picture: Imgur: The magic of the Internet

  • How are you trying to achieve it?
    Tried to rebuild the InfluxQL with Flux CLI and another approach was to use the “Query Explorer” under the “Data Explorer”

  • What happened?
    “nothing” as expected

  • What did you expect to happen?
    I would expect to have with “flux” the same chart as I have with “InfluxQL” and the history from Home Assistant. See picture from the second point

  • Can you copy/paste the configuration(s) that you are having problems with?

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?
    Flux 0.x Documentation

2

Welcome

Please share both the influxql and flux queries

3

Hi,

Not sure how to properly export the influxQL so I made two screenshots. https://imgur.com/a/9FBI7vp

4

Is the data stored in UTC? It appears your two graphs differ by 5 hours, but maybe there are other differences? Flux offers timezone support options.

5

I dont see a difference by 5h, at maximum 1h and I dont know why. Maybe as you said because of the timezone option by flux. Not sure how home assistant store the value, but I will try to check the behavior with flux and timezone

I saw right now, that the filename is missing on the pictures.
The second picture is from Home Assistant and as far I could observ it is correct. This means that also picture3 with InfluxQL is correct. The small peak at the start is fully gone on picture1 with Flux.

It is the always the same influxDB with the same bucket configured. On the InfluxQL way there was a mapping done with a guide.

6

Can you remove the aggregateWindow statement in your Flux query and then see how the two graphs compare?

7

Hi Grant,

Of course. I’ve removed the whole aggregate link and only one line moved very slightly to the left.

2023-05-11 05_53_47-Window
2023-05-11 05_53_47-Window1351×844 33.8 KB

8

image
image1363×854 76 KB

Above is the graph generated by InfluxQL (taken from your original link).

Is the Flux query data (InfluxDBv2) and the InfluxQL data (InfluxDB) collected / populated at the same time? Are you 100% sure the data is the same? Perhaps easiest is to download the CSV data from Influx and compare each point side-by-side.

9

Hi Grant,

The data source is from Home Assistant
fahrrad power
As far as I can see the entity_id is on “InfluxQL” and “Flux” the same, this should be the same data source. Issnt’t it?

The query, I use on the “Flux” is the one I use on this InfluxDB query (directly from the Data Explorer)

influx
influx2151×474 54.7 KB

The query from Influx data explorer looks completly different from all queries, so I assume that the Home Assistant and InfluxQL query should be correct, since the “data writer” is originally Home Assistant.

10

In the Flux query from Data Explorer (i.e. your screenshot), can you show the actual query / Flux code? And instead of the graph, can you change to “View Raw Data” toggle, then post everything back here?

11

This is the query (Thats also how I got the code that I used in Grafana):

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "W")
  |> filter(fn: (r) => r["entity_id"] == "hmip_psm_2_0034df298e7a87_power")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
0 W value 0.45 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T21:52:24.982Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 183.9 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T22:52:58.271Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 181.9 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T22:53:30.424Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 171.6 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:13:52.238Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 161.28 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:15:28.697Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 157.34 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:16:00.850Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 146.99 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:17:37.309Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 136.63 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:19:13.768Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 126.34 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:21:22.380Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 123.61 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:21:54.533Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 113.42 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:23:30.992Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 102.98 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:26:11.757Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 101.39 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:26:43.910Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 91.49 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:29:24.675Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 81.41 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:32:05.440Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 71.5 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:36:22.664Z sensor hmip_psm_2_0034df298e7a87_power
— — — — — — — — —
0 W value 64.68 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:39:03.429Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 61.66 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:40:39.888Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 51.88 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:46:33.571Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 41.89 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-09T23:54:35.866Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 2.22 2023-05-09T21:45:29.000Z 2023-05-10T00:58:24.000Z 2023-05-10T00:02:38.161Z sensor hmip_psm_2_0034df298e7a87_power

From my point of view it looks good

12

Hi,

anyone an idea? Today it was the same behavior in the difference between the query with InfluxQL, Flux and the raw data directly from Influx.

2023_05_16_11_15_00_v_TeamViewer
2023_05_16_11_15_00_v_TeamViewer1733×555 135 KB

tablemean _measurementgroupstring _fieldgroupstring _valueno groupdouble _startgroupdateTime:RFC3339 _stopgroupdateTime:RFC3339 _timeno groupdateTime:RFC3339 domaingroupstring entity_idgroupstring
0 W value 0.36 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T19:45:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 173.7 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T20:15:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 161.53 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:28:20.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 149.67 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:30:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 139.53 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:31:40.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 137.695 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:32:30.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 126.44 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:34:10.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 116.19 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:36:40.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 106.26 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:39:10.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 103.66 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:40:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 92.94 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:42:30.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 83.33 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:45:50.000Z sensor hmip_psm_2_0034df298e7a87_power
— — — — — — — — —
0 W value 73.58 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:50:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 63.49 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:54:10.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 62.81 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T21:55:00.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 53.28 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T22:00:50.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 2.72 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T22:07:30.000Z sensor hmip_psm_2_0034df298e7a87_power
0 W value 0.34 2023-05-15T19:00:00.000Z 2023-05-16T00:00:00.000Z 2023-05-15T23:17:30.000Z sensor hmip_psm_2_0034df298e7a87_power

Please consider (as it seems not correctly shown) timezone difference of +2h UTC

13

The top-over-bottom comparison screenshot helps a lot.

Could it be these settings? Are they the same for both of your graphs?

image

image
image800×431 21.7 KB

14

Awesome, It seems that I have not noticed this configuration.

1 Like