Use the correct functions for meter-reading

InfluxDB 2
1

Use the correct functions for meter reading.

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 (prev Topic)
  • step 2 use the correct functions for meter reading (this Topic)
  • step 3 test time zone (next topic)

We use the additional option for aggregateWindow() as discussed in the previous topic.

Test Data generator see also previous Topic prepare aggregate Windows flux test scripts

Next step use Meter simulation data.

we comment-out fn: (n) => n .

(by moving the comment in start_block.flux)

For this purpose we create a window and e.g. of 10 minutes.

and the min and max value.
replacement for this is ‘spread’.

output_data =
	raw_data
		|> aggregateWindow(every: 10m,
		fn: spread,
		timeSrc : "_start",
		timeDst : "_time",
		createEmpty: false
	|> limit(n: 20)
_time _value week_day month mont_day year_day
2023-01-01T00:00:00Z 9 0 1 1 1
2023-01-01T00:10:00Z 9 0 1 1 1
2023-01-01T00:20:00Z 9 0 1 1 1
2023-01-01T00:30:00Z 9 0 1 1 1
2023-01-01T00:40:00Z 9 0 1 1 1
2023-01-01T00:50:00Z 9 0 1 1 1
2023-01-01T01:00:00Z 9 0 1 1 1

that’s the next step we need to solve.
The value of 10 minutes is 9.

This is a common mistake that is made and can be explained as follows.

we take the first two windows as an example.

[1 ,2 , 3, 4, 5 , 6, 7, 8, 9, 10], [11, 12, 13, 14, 15 ,16, 17, 18, 19, 20]

if per window the minimum value is subtracted from the maximum value.

20 - 11 = 9

10 - 1 = 9

You will also make this mistake if you subtract the first value of the week or month from the last value of the week or month. You lose the smallest sample period in time and value.

solution use the difference() function.

the first value window 2 = 11

the first value window 1 = 1

11 - 1 = 10 (the correct value.)

output_data =
    raw_data
    |> range(start: start_proc)
    |> aggregateWindow(every: 10m,
        fn: first,
        timeSrc : "_start",
        timeDst : "_time",
        createEmpty: false)
    |> difference()
_time _value week_day month mont_day year_day
2023-01-01T00:10:00Z 10 0 1 1 1
2023-01-01T00:20:00Z 10 0 1 1 1
2023-01-01T00:30:00Z 10 0 1 1 1
2023-01-01T00:40:00Z 10 0 1 1 1
2023-01-01T00:50:00Z 10 0 1 1 1
2023-01-01T01:00:00Z 10 0 1 1 1
2023-01-01T01:10:00Z 10 0 1 1 1

Wat is wrong ???

shift 10 minuten

This is also wrong again with every: 1d is again shifted with time and you are wrong again by a day.

_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

Next step timeshift

output_data =
    raw_data
    |> range(start: start_proc)
    |> aggregateWindow(every: 1d,
        fn: first,
        timeSrc : "_start",
        timeDst : "_time",
        createEmpty: false)
    |> timeShift(duration: -1d) // correct date
    |> difference()
_time _value week_day month mont_day year_day
2023-01-01T00:00:00Z 1440 0 1 1 1
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

last issue we miss day 7

For this we need to add the flux data generator for a minute.


start_proc = 2023-01-01T00:00:00Z
stop_proc = 2023-01-08T00:01:00Z // + 1 minuut

raw_data =
    generate.from(

    // 7 * 24 * 60 =10080 minuten for 7 days window
    count: 10081, // + 1 minuut extra for difference funtion

   ...

end result

_time _value week_day month mont_day year_day
2023-01-01T00:00:00Z 1440 0 1 1 1
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

Conclusion:

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

  2. Use difference. …Spread and (Max - Min) is only for within the window frame and not suitable for meter-reading .

  3. Also difference uses the last time value of the sequence frame but has no option to use the prev time.

correction requires a timeShift() .

  1. For difference() needs an extra value in the new period. in practice this is not a problem.

Final Test (Month & Year)

import "generate"
import "date"

start_proc = 2022-01-01T00:00:00Z
stop_proc = 2023-01-01T01:00:00Z

raw_data =
generate.from(
count: 8761, // 8760 hours in a year 365 * 24 + 1

fn: (n) => (n), // fill all hours with a increment integer 1-8761

start: start_proc,
stop: stop_proc
)

month_data =
    raw_data
        |> range(start: start_proc, stop: stop_proc)
        |> aggregateWindow(every: 1mo,
            timeSrc : "_start",
            timeDst : "_time",
            fn: first)
        |> difference()
        |> timeShift(duration: -1h) // time shift between 1mo and 1h

        // Only for clean readable output
        |> map(fn: (r) => ({r with month : date.month(t: r._time)}))
        |> map(fn: (r) => ({r with year : date.year(t: r._time)}))
        |> keep(columns: ["_time", "_value","month", "year"])
        |> yield(name: "month")


// Year = summatie of all month's.
output_data =
    month_data
    |> range(start: start_proc, stop: stop_proc)
    |> aggregateWindow(every: 1y,
        timeSrc : "_start",
        timeDst : "_time",
        fn: sum)

output_data
    |> range(start: start_proc, stop: stop_proc)
    |> map(fn: (r) => ({r with year : date.year(t: r._time)}))
    |> keep(columns: ["_value", "year" ])
    |> yield(name: "dag data")

Next step (Topic) is check this with :

  • timezone adjustment and

  • Task timing.

Have fun with flux :smile: