Understanding value aggregation

1

Hello,

here is the query:

import "timezone"

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

from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "FrigoConsoElec")
  |> difference(nonNegative: true, initialZero: true, columns: ["_value"])
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)

In this one, I’m using the difference function to calculate electricity consumption from a meter reading.

Then I use the aggregateWindow function to aggregate the data by day.

Here’s what I get for 7 days:

|0|FrigoConsoElec|value|803.2340000000004|2024-05-22T21:59:59.000Z|
|0|FrigoConsoElec|value|1694.5110000000004|2024-05-23T21:59:59.000Z|
|0|FrigoConsoElec|value|1688.393|2024-05-24T21:59:59.000Z|
|0|FrigoConsoElec|value|931.7039999999997|2024-05-25T21:59:59.000Z|
|0|FrigoConsoElec|value|2398.459999999999|2024-05-26T21:59:59.000Z|
|0|FrigoConsoElec|value|1908.0040000000008|2024-05-27T21:59:59.000Z|
|0|FrigoConsoElec|value|1757.4040000000005|2024-05-28T21:59:59.000Z|
|0|FrigoConsoElec|value|982.6709999999985|2024-05-29T10:59:01.562Z|

Apart from the time (normally it’s 12:59 at my place), the problem is that the data is wrong.

it looks like a shift

Starting with today.
If I use the calendar to set 05/29 00:00 to 05/29 23:59, this is what I get:
0 FrigoConsoElec value 415.5499999999993 2024-05-29T21:59:59.000Z

The same thing with 26/05 :
0 FrigoConsoElec value 1487.4399999999987 2024-05-26T21:59:59.000Z

I refer to you to understand and maybe correct this

thanks

2

@Yohan Are you copying the results directly from the CSV returned from InfluxDB or are you copying from a client like Grafana? The reason I ask is because all timestamps returned from InfluxDB are UTC timestamps unless you explicitly shift them. Some clients automatically update timestamps when they visualize/output results.

Setting the location option does not shift timestamps to the specified timezone. All the location option does is change how window boundaries are defined when they cross a time shift (daylight savings, British summer time, etc.).

To shift the actual timestamps returned from a query, you can use timeShift() with your timezone’s offset:

from(bucket: "GIN")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "FrigoConsoElec")
  |> difference(nonNegative: true, initialZero: true, columns: ["_value"])
  |> timeShift(duration: 2h)
  |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)