Union() and events.duration() don't return the right value

Fluxlang
1

Hey,
i have 2 tables. The first one contains an ID from a machine and the other one contains the same ID but from the end of the process.
Table 1:
image
Table 2:
image

What i need is to calculate the time between the last value of table 1 (2021-01-06 03:07:50) and the time in table 2 (2021-01-07 02:45:00)

But the query returns the value 0.

image
image1245×72 3.67 KB

What do i have to change in my query that i can calculate the time between these two timestamps?
Or is there a better way to achive this?
My query is the following

union(

tables:[tab1,tab2],

)

|>group(columns:[“ID”])

|>events.duration(

unit: 1m,

columnName: “duration”,

timeColumn:"_time",

stopColumn: “_time”

)

2

@Patse events.duration() calculates the time between subsequent rows. You need to compare times in the same row. To do this correctly, you should use join() instead of union(), and then map() to calculate the difference between two column values:

tab1 = // ... your table1 query
  |> last()

tab2 = // ... your table2 query

join(
  tables: {t1: tab1, t2: tab2},
  on: ["ID"]
)
|> map(fn: (r) => ({ r with
  duration: int(v: r.time_t1) - int(v: r._time_t2)
}))
3

Thank you @scott. That was the answer i was looking for.
The only minor issue i have is that the last funktion doesn’t return a value.

I call the the Table like this:

tab2 = from(bucket: “zone/autogen”)

|> range(start: 2021-01-01T00:00:00Z)

|> filter(fn: (r) =>

r._measurement == “Measurement” and

r.short == “Machine_ID”

)

|> pivot(

rowKey:["_time"],

columnKey: [“short”,"_field"],

valueColumn: “_value”

)

|> keep(columns:["_time",“Machine_ID”,"_stop"])

|> rename(columns: {“Machine_ID”: “ID”})

|>toString()

|>first()

But i get only the error image
Do you know a way to prevent this?
Is there a way to do the first() function on another column?

4

The _value column gets removed with pivot() and is replaced by the columnKey. toString() requires a _value column, but you can replicate the transformation with map() and string(). In fact, you can replace keep(), rename(), and toString() with a single map() call:

tab2 = from(bucket: “zone/autogen”)
  |> range(start: 2021-01-01T00:00:00Z)
  |> filter(fn: (r) =>
    r._measurement == “Measurement” and
    r.short == “Machine_ID”
  )
  |> pivot(
    rowKey:["_time"],
    columnKey: ["short","_field"],
    valueColumn: "_value"
  )
  |> map(fn: (r) => ({
      _time: r._time,
      ID: string(v: r.Machine_ID),
      _stop: r._stop
  }))
  |>first()
5

Thank you so much. i guess i have to learn more about the map() function. It seems to me that it is quite powerfull.