Prepare aggregateWindow() for Meter-Reading ... or for correct time display in the output

InfluxDB 2
1

Prepare aggregateWindow() for Meter-Reading … or for correct time display in the output.

Motivation get the right meter value in the right time zone.

There are several issues that’s why I make 3 steps for testing.

  • step 1 prepare aggregate Windows (this Topic)
  • step 2 use the correct functions for meter reading
  • step 3 test time zone

I am a very enthusiastic user of flux.
And I’ve been busy with meter readings for a while.

What I ran into is very ‘basic’ and I see that this topic has been communicated several times in the past.

Because I couldn’t find a “White Paper” or document on this
I would like to explain my findings in detail.

Influxdb is made for storage of measurements without beginning and end. (streaming data).

Therefore, we want to intercept the values by extracting units of time for an operation. (sum, mean, spread, first, last, max, min)

Especially for meter reading is determining the value of the difference between two values.

The window function or aggregateWindows() has been created for this purpose.

aggregateWindow(every: 1d,
	fn: mean,
	createEmpty: false)

Before you know it, it is automatically filled in by the UI and the results fly across the screen. but !!!

Let’s take a closer look at what results this produces.

First we generate predictable data.

Test script flux

For this we use the flux data generator which will give a predictable outcome.

Data description:

we take the first week of 2023
January 1 starts on Sunday
day of the year = 1
day of the month = 1
day of the week = 0

period exactly 1 week = 7 days = 10080 minutes
And for  clarity ***no*** 'timezone'.

We can fill the values per minute with the following data.

  • fn: (n) => (1) all _vallue 's ’ = 1

  • fn: (n) => n/1440 "week_day [sun = 0 , mon = 1, … sat = 6) ]

  • fn: (n) => (n) erery minuut sum up with 1 unit [ 2023-01-01T00:00:00 = 0, … 2023-01-01T23:59:59 = 10080]

Flux test script

start_block.flux → 

import "generate"
import "date"

start_proc = 2023-01-01T00:00:00Z
stop_proc = 2023-01-08T00:00:00Z

raw_data =
	generate.from(
	// 24 * 60 = 1440 minuten for 1 day window
	// 7 * 24 * 60 =10080 minuten for 7 days window
	count: 10080,
	// comment out for 1 of the 3 funtions
	// 3 different function's
	
	//fn: (n) => n / 1440 ,
	fn: (n) => (1),
	//fn: (n) => (n),
	
	start: start_proc,
	stop: stop_proc
)

analyse_block.flux

// replace this with the experimnet's
// pass true first 20 records
output_data =
	raw_data
		|> range(start: start_proc, stop: stop_proc)
		|> limit(n: 20)

output_block.flux

Continuous output with test for date time calendar functions.


output_data
	|> range(start: start_proc, stop: stop_proc)
	|> map(fn: (r) => ({r with week_day : date.weekDay(t: r._time)}))
	|> map(fn: (r) => ({r with month : date.month(t: r._time)}))
	|> map(fn: (r) => ({r with month_day : date.monthDay(t: r._time)}))
	|> map(fn: (r) => ({r with year_day : date.yearDay(t: r._time)}))
	|> keep(columns: ["_value", "week_day","month","month_day","year_day" "_time"])
	|> yield(name: "output data")

first experiment

we fill the week with a 1 every minute and see what it produces with a standard aggregateWindow() function.

The expected outcome should be 7 days with 1440 minutes each.

output_data =
	raw_data
		|> range(start: start_proc, stop: stop_proc)
		|> aggregateWindow(every: 1d,
			fn: sum,
			createEmpty: false)
_time _value week_day month mont_day year_day
2023-01-02T00:00:00Z 1440 1 1 2 2
2023-01-03T00:00:00Z 1440 2 1 3 3
2023-01-04T00:00:00Z 1440 3 1 4 4
2023-01-05T00:00:00Z 1440 4 1 5 5
2023-01-06T00:00:00Z 1440 5 1 6 6
2023-01-07T00:00:00Z 1440 6 1 7 7

You would quickly overlook this but you miss the first day.
and the value of the first day is January 2.

The last day goes to next week.

How do we know that the values have moved a day???

we now fill the values with fn: (n) => n/1440 .
(by moving the comment in start_block.flux)

week_day [sun = 0 , mon = 1, ... ,sat = 6 ]

_time _value week_day month mont_day year_day
2023-01-02T00:00:00Z 0 1 1 2 2
2023-01-03T00:00:00Z 1440 2 1 3 3
2023-01-04T00:00:00Z 2880 3 1 4 4
2023-01-05T00:00:00Z 4320 4 1 5 5
2023-01-06T00:00:00Z 5760 5 1 6 6
2023-01-07T00:00:00Z 7200 6 1 7 7

Sunday has only added values 0 that should be 0

This value is therefore at Monday.

Cause ??

This is because aggregateWindow moves time forward.

The function is made up of underlying functions.

|> aggregateWindow(every: 1d,
			fn: sum,
			createEmpty: false)

same as:

	// ===============================================
	|> window(every: 1d)
	|> sum()
	|> duplicate(as: "_time", column: "_stop")
	|> window(every: inf, timeColumn: "_time")
	// ===============================================

We can

duplicate(as: "_time", column: "_stop") change into

duplicate(as: "_time", column: "_start")

this solved this problem.

Or use an additional option for aggregateWindows().

output_data =
	raw_data
		|> range(start: start_proc, stop: stop_proc)
		|> aggregateWindow(every: 1d,
			fn: sum,
			timeSrc : "_start",
			timeDst : "_time",
			createEmpty: false)

last and correct result.

_time _value week_day month mont_day year_day
2023-01-01T00:00:00Z 0 0 1 1 1
2023-01-02T00:00:00Z 1440 1 1 2 2
2023-01-03T00:00:00Z 2880 2 1 3 3
2023-01-04T00:00:00Z 4320 3 1 4 4
2023-01-05T00:00:00Z 5760 4 1 5 5
2023-01-06T00:00:00Z 7200 5 1 6 6
2023-01-07T00:00:00Z 8640 6 1 7 7

This extra option gives the correct days and values in the right place. (This applies to the functions that are suitable for aggregate.)

Conclusion:

  1. AggregateWindows has option to take the _start time instead of _stop to prevent time shift.

go to the next topic:

  • step 2 use the correct functions for meter-reading.

Have fun with flux