Migrate query to calculate energy (kWh)

Fluxlang
,
23

Hello @Cavekeeper,
It might be timezones in which case you’ll want to look into using:

Or

Otherwise, I think the issue here is that your queries are slightly different. But it’s hard for me to say without seeing your data. Once you group by time in your InfluxQL query do you only have two values in that group?

I think the difference is from:
NON_NEGATIVE_DIFFERENCE()
Returns the non-negative result of subtraction between subsequent field values. Non-negative results of subtraction include positive differences and differences that equal zero.

While
The spread() function outputs the difference between the minimum and maximum values in a specified column. spread() is an aggregate function.

I think what you want is:

import "timezone"
from(bucket: “stromzaehler”)
|> range(start: -1d)
|> filter(fn: (r) => r["_measurement"] == “stromzaehler”)
|> difference(nonNegative: true) 
|> timezone.location(name: "France/Paris")

Or whatever. your timezone is

Let me know if that helps!

24

hallo @Anaisdg , please in what version you use: import “timezone”

in influxdb 2.0.9 explorer OR cli OR flux_repl v0.139.0

i’m getting Error: invalid import path timezone

thank you, pavel

25

Dear @Anaisdg @srbp
Yes, depending to V2.0.9 I can confirm the error:

error @1:1-1:18: invalid import path timezone

26

look’s like it is in Flux v0.134.0+

but still that should work in ./flux repl ? v0.139.0

pavel

27

@srbp,
I believe so.

28

I adapted this query as i attach below, so this returns the entire month grouped by days, but those days are in reality the previous day. Meaning that for example today is 18th of March and if i execute the query I get today’s data on 19th of March.

If I run with now() the server time seems correct.

Do someone know why is happening that or how to fix it?

from(bucket: "autarky_dev")
        |> range(start: 1646089200, stop: 1648763999)
        |> filter(fn: (r) => r["_measurement"] == "energyLogs")
        |> filter(fn: (r) => r["plantId"] == "1")
        |> aggregateWindow(
          every: 4h,
          fn: (tables=<-, column) =>
            tables
              |> integral(unit: 4h)
              |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
        |> aggregateWindow(fn: sum, every: 1d)
29

Hi,
try add "timeSrc: “_start” to last aggregateWindow like:
|> aggregateWindow(fn: sum, every: 1d, timeSrc: “_start”)

2 Likes
30

You need to enable time zone support I would assume. You are in UTC days/months.

import "timezone"

option location = timezone.location(name: "America/Los_Angeles")

(Source: https://www.influxdata.com/blog/time-zones-in-flux/

31

Sorry for the delay, I missed your post. Now data seems more appropiated but I need to adjust it for each temporality. I need to understand deeper this attribute.

Thanks!

32

I tested this functionality and is ignoring whatever i set on it.

33

Instead of

|> integral(unit: 1h)
|> map(fn: (r) => ({ r with _value: r._value / 1000.0}))

you can say
|> integral(unit: 1000h)

That divides by 1000 too and avoids the extra map