Wrong Results flux query funciton difference()

Fluxlang
, , ,
1

i have a problem with the results of my flux query using the flux-funciton difference. When i selcect data from the whole year it returns wrong results at first of march (minus values) and doubles the values at the first of november. I have checked my data in influx db, the data are correct and there are no wrong entries in the db. The result are only wrong when both first of march and first of november are in the time range. Whe i use a time range where only first of march or first of november ist in the time range, the result is correct

verbraucher
verbraucher1038×351 23.6 KB

Query:
import “timezone” option location = timezone.location(name: “America/Los_Angeles”)
from(bucket: “${BucketInflux}”)

|> range(start: ${__from:date}, stop: ${__to:date})
|> drop(columns: [“host”, “year”, “month”])
|> filter(fn: (r) => r._measurement == “power_boiler” )
|> difference()
|> aggregateWindow(every: ${Interval}, fn: sum)

2

Could this have anything to do with the daylight savings time change that occurs in March & Nov?

3

yes maybe, normaly i am using import “timezone” option location = timezone.location(name: "Europe/Berlin”) not as I posted America/Los_Angeles, but the behavior is the same. I tried it only with different time zones.

4

does anybody have an idea what can i try?

5

Not a proper solution - more of a work around, but how about adding a filter() statement where you filter out the values which are out of bound?

6

there are no wrong values in the influx data, all values are correct, nevertheless the function difference calculates wrong on first of march and first of november. first of march all data from the whole year are minus and on first november all data from the whole year are plus.

Screenshot 2023-12-26 211853
Screenshot 2023-12-26 2118532040×1242 64.4 KB

7

ah, should have been clearer. I’m was thinking about a filter after the calculation. I’m assuming the MegaWatts are out of bounds, so you could filter them out.

8

to bring more clarity in this topic i have made a sample data set, 12 records one for each month with a difference counter from 1 per record, starting by 10:

power_consumption,host=fhem,month=0,year=123 value=10.0 1672610400150183134
power_consumption,host=fhem,month=1,year=123 value=11.0 1675413900169791501
power_consumption,host=fhem,month=2,year=123 value=12.0 1677903300133465637
power_consumption,host=fhem,month=3,year=123 value=13.0 1681324200156906309
power_consumption,host=fhem,month=4,year=123 value=14.0 1683888300166902729
power_consumption,host=fhem,month=5,year=123 value=15.0 1686492900168486777
power_consumption,host=fhem,month=6,year=123 value=16.0 1689223500162810796
power_consumption,host=fhem,month=7,year=123 value=17.0 1690945200177528171
power_consumption,host=fhem,month=8,year=123 value=18.0 1694354400173095563
power_consumption,host=fhem,month=9,year=123 value=19.0 1697125500163008242
power_consumption,host=fhem,month=10,year=123 value=20.0 1700164800157812443
power_consumption,host=fhem,month=11,year=123 value=21.0 1703358000164680983

first step:
after loading the data in influxdbcloud i made an query with flux, the query looks like as follows (without difference() function) , the range is over the whole year 2023:

second step:
(grafanacloud flux statement)
import “timezone” option location = timezone.location(name: “Europe/Berlin”)
from(bucket: “${BucketInflux}”)
|> range(start: ${__from:date}, stop: ${__to:date})
|> drop(columns: [“host”, “year”, “month”])

the result is correct and looks like as follows
grafik

third step:
as last step im using the query with difference(), the range is over the whole year 2023:
import “timezone” option location = timezone.location(name: “Europe/Berlin”)
from(bucket: “${BucketInflux}”)
|> range(start: ${__from:date}, stop: ${__to:date})
|> drop(columns: [“host”, “year”, “month”])
|> difference()
the result is not correct and looks as follows:
grafik

have anybody an idea wy flux ist calculation for march -9 and for november 9 ?

9

Can you remove this line:

import "timezone" option location = timezone.location(name: "Europe/Berlin")

and see if that makes any difference? If you store your data in UTC, then the browser can specify the Berlin timezone and handle any of the daylight savings time stuff.

10

it doesn’t make a difference