Downsampling Power to Energy with influxdb2

InfluxDB 2 Tasks
1

Hi there,

I am quite new to InfluxDB, ran across it, when we got our new PV system from Victron and I learnt about the power of Nodered.

So storing all the metering information works fine. The PV system sends to the power Info in Watt every 10 seconds.

Now I want to calculate the produced energy in Wh for every hour and this is where I currently fail.

I created a task that is run 1 minute past the hour every hour. I truncate the start and stoptime to the full hour and Substrat 1 hour each to only deal with the last full hour.

  1. problem that I have is, that when summing the 10secs values, I get the double amount of lines (720 instead of 360). I can deal with that dividing the total amount by 720 instead of the correct 360 count, but still strange. I am using every 1h aggregation when summing up.

  2. my bigger problem is the time stamp of the resulting set. I want to have the aggergates 1h into the future, so all generations between 7:00 and 7:59 shall be at the 8:00 timestamp. I tried using offset:1h to achieve this.

What I don‘t understand is the displaying of result sets in InfluxDB. When I try to query the result set and set the aggregation window to 5secs, I get all timestamps 5 secs after the hour. When I set it to 1 hour I get all timestamps shifted to the next hour. How can I validate, the timestamp was set correctly?

Thanks for your help, much appreciated.

2

This is the task code I am using currently:

import “date”

option task = {name: “PV-Ertrag in Wh”, every: 1h, offset: 1m}

temptime = date.truncate(t: now(), unit: 1h)

stoptime = date.sub(from: temptime, d: 1h)
starttime = date.sub(from: stoptime, d: 1d)

from(bucket: “input”)
|> range(start: starttime, stop: stoptime)
|> filter(fn: (r) => r[“_measurement”] == “Strom”)
|> filter(fn: (r) => r[“Quelle”] == “PV”)
|> filter(fn: (r) => r[“Version”] == “Ist”)
|> map(fn: (r) => ({r with _value: r._value / 720.0}))
|> aggregateWindow(every: 1h, fn: sum, createEmpty: false, offset: 1h)
|> yield(name: “sum”)
|> to(bucket: “test”, fieldFn: (r) => ({“Energie”: r._value}))

3

Welcome @AminShAT to the InfluxDB forum.

This can require some trial-and-error because there are so many functions (truncating, aggregating, summing, etc.) and it’s difficult to understand the sequence / priority of each.

Anyway, can you try the timeSrc option in your aggregateWindow function and see if that helps, like this?

|> aggregateWindow(every: 1h, fn: sum, createEmpty: false, offset: 1h, timeSrc: "_start")

More explanation here

4

Thanks a lot Grant,

I got rid of the aggregate window clause and can now see it works just fine. It is a bit tricky, that raw data view in InfluxDB is using UTC timestamps :-). That got me confused a bit.

I just still have the strange 720 data points phenomenon, but for that I have the work-around,

5

Start time is 1d less than stop time, so you are querying more than 1 hr worth of data points, you are querying 24 hrs or I am missing something ?

6

No, you are absolutely right.

I put it that way, to be safe, that really all data is downsampled. I tested, that InfluxDB will overwrite data if it has exactly the same time stamp and all tags are the same as well.

7

Well I still don’t understand exactly what are trying to achieve but I know something that may poont you in the right direction.

So, as you notice influxdb works on utc timestamps, wich gets tricky if you try to group on days.

You can use

import "timezone"

option location = timezone.fixed(offset: -8h)

And all your time related aggregations will work with that time offset.

8

The script works for me now and i actually also found the reason for the 720 data points. There are data points every 5 seconds, that sums up to 720 per hour.

The influxDB querybuilder got me confused because the lowest aggregation window solution there is 10 seconds. When switching to script editor and eliminating the aggregation window clause I can now see al the data points.

9

Thanks for the hint with the location option,I will try that for sure!