Flux: First, Last and the differnce per day

, ,
1

Hello, i have following data:

Time value
30.04.2024 00:05 65
30.04.2024 08:00 65
30.04.2024 23:55 75
01.05.2024 00:05 75
01.05.2024 08:00 75
01.05.2024 23:55 60
02.05.2024 00:05 60
02.05.2024 08:00 60
02.05.2024 09:00 62
02.05.2024 23:55 65

i would like to have:

date first time first value last time last value differnce
30.04.2024 30.04.2024 00:05 65 30.04.2024 23:55 75 10
01.05.2024 01.05.2024 00:05 75 01.05.2024 23:55 60 -15
02.05.2024 02.05.2024 00:05 60 02.05.2024 23:55 65 5

it’s my first tim with flux.

Thank you

2

@hans-gustav Do you have an existing Flux query or are you working from scratch? If working from scratch, what is the schema of your data? What fields and tags do you have?

3

@scott

Blockquote
from(bucket: “ttn”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “waage-1”)
|> filter(fn: (r) => r[“_field”] == “wight”)

Do you need more information?

Thank you

4

To get the exact output format you showed above, you’d have to do something like this:

import "array"
import "date"

from(bucket: "ttn")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "waage-1")
    |> filter(fn: (r) => r["_field"] == "wight")
    |> window(every: 1d)
    |> reduce(
        identity:
            {   idx: 0,
                date: today(),
                "first time": today(),
                "first value": 0,
                "last time": today(),
                "last value": 0,
                difference: 0
            },
        fn: (r, accumulator) => {
            idx = accumulator.idx + 1
            date = date.truncate(t: r._time, unit: 1d)
            firstTime = if accumulator.idx == 0 then r._time else accumulator["first time"]
            firstValue = if accumulator.idx == 0 then r._value else accumulator["first value"]
            lastTime = r._time
            lastValue = r._value
            difference = lastValue - firstValue
            
            return {
                idx: idx,
                date: date,
                "first time": firstTime,
                "first value": firstValue,
                "last time": lastTime,
                "last value": lastValue,
                difference: difference,
            }
    })
    |> drop(columns: ["idx", "_start", "_stop"])

But if you’re just trying to show the difference between the first and last value of each day, there’s a simpler way.

5

@scott Wow, thank you so much.

I definitely need the table. I also need the scale to increase and decrease per day. This is then the difference between the first and last value. I have already developed something here.

from(bucket: “ttn”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “waage-1”)
|> filter(fn: (r) => r[“_field”] == “wight”)
|> aggregateWindow(every: 1d, fn:last, createEmpty: false)
|> yield(name: “last”)

But now the scale can also be in maintenance mode and these values ​​should not be taken into account. The information regarding maintenance is in status.

from(bucket: “ttn”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “waage-1”)
|> filter(fn: (r) => r[“_field”] == “status” or r[“_field”] == “wight”)

The data is stored as follows.

Time wight
Time 1 25
Time 2 25,2
Time 3 25,3
Time 4 30,0
Time 5 25,1
Time 6 27
Time 7 27,5
Time status
Time 1 0
Time 2 0
Time 3 1
Time 4 2
Time 5 2
Time 6 3
Time 7 0

Maintenance begins at status 1. Status 2 means maintenance continues and status 3 means maintenance completed. Only the difference between values ​​0 to 0, 1 to 0 and 0 to 3 should be taken into account. In this case:
(25.2 - 25) + (25.3 - 25.2) + (27.5 - 27) = 0.8

Unfortunately I have no idea how to do this and it would be great if you could help me too.

6

I have another idea about status. Status 1 and 3 are omitted and are 0. Status 2 remains and indicates measurement during maintenance. This would allow you to simply take all records with status 0.

7

I’m not sure I fully understand. How is the scale stored? How should it be applied to the values?

8

First of all, sorry for the late response and thank you very much. I try to explain what I would like.

The scale sends the absolute weight and the status of the scale at regular intervals. The statuses are normal and maintenance.

If the status is 0 then we are in normal. At 1 in maintenance. Attached an example. Red is maintenance.
Now I want the difference of every connected measurement with status 0. As soon as a status 0 measurement is interrupted by status 1, then this should be ignored. The differences should be added up. It may happen that there are several maintenance services per day.

image

I can retrieve the data in the influxdb as follows.

status:

from(bucket: "ttn")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "waage-1")
  |> filter(fn: (r) => r["_field"] == "status")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

wight:

from(bucket: "ttn")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "waage-1")
  |> filter(fn: (r) => r["_field"] == "wight")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

like this

image
image1639Ă—908 74.2 KB

If necessary, I can also set the status differently.

I hope this helps.

9

Ah, ok. I understand the use case now. The challenge is conditionally applying an aggregate operation. My initial thought is to just filter out all the rows where status is 1 and then use difference() to show the difference between subsequent rows. The problem with this approach is that you’d end up getting the difference between the last row of a status 0 “cycle” and the first row of the next status 0 cycle.

With the current functionality available in Flux, I can’t really think of a way to this. There is a proposed feature called scan() that would make this type of operation trivial. Here’s a link to the GitHub issue: EPIC: scan function · Issue #4671 · influxdata/flux · GitHub

1 Like
10

I have a new idea. I manipulate the data with a script before writing it to the database. This means that each data record receives the current daily difference. This means I only have to display the last difference value of the day. Always the green ones here

image
image240Ă—513 5.79 KB

If I retrieve the following I get an offset of the day.

from(bucket: "ttn")
  |> range(start: 2024-05-02T00:00:00Z, stop: 2024-05-07T20:00:00Z)
  |> filter(fn: (r) => r["_measurement"] == "waage-1")
  |> filter(fn: (r) => r["_field"] == "differenceday")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> yield(name: "mean")

image

But I would like the following

image

What am I doing wrong? Do you have another tip for me?

11

It’s hard to pinpoint exactly what’s going on here, but I suspect it has something to do with timezone. The spreadsheet software you’re taking screenshots of (I’m guessing) is showing timestamps in your local timezone. All timestamps stored in InfluxDB are UTC. All the window boundaries defined by Flux, but default, use UTC.

What timezone are you in? Try adding your local timezone’s hour offset as an offset on aggregateWindow():

// ...
  |> aggregateWindow(every: 1d, offset: -2h, fn: last, createEmpty: false)
12

Thank you very much, that is the solution. I chose an offset of -7201s.