Referencing "_time" from previous query as "range(start: , stop:now())" for a following query. I can't get it to work

InfluxDB 2
, ,
1

Thanks for the help in advance!

import "sql"
data=from(bucket: "TEMP_DATA")
  |> range(start: -2m)
  |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
  |> group()
  |> last()
  |> keep(columns: ["_time"])
  |> limit(n:1)

batchinsert=from(bucket: "Bucket2")
    |> range(start: data["_time"], stop: now() )
    |> filter(fn: (r) => r["_measurement"] == "placeholder_1")

|> range(start: data["_time"], stop: now() ) <--- This is my Problem, what am I doing wrong?
2

Hi @mauflege

What does this return? I presume a timestamp? Please show screenshot from Influx Data Explorer.

data=from(bucket: "TEMP_DATA")
  |> range(start: -2m)
  |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
  |> group()
  |> last()
  |> keep(columns: ["_time"])
  |> limit(n:1)
  |> yield(name: "timestamp")
1 Like
3

Yes, the return is as you expected:

image
image908Ă—458 12.2 KB

4

So, does anyone know what I am doing wrong? - Or does anyone have a solution / workaround to my problem?

Even an alternative solution would be appreciated.

5

Hi @mauflege and sorry for not replying sooner (I have not been on this forum for a few weeks).

This is just a guess, but maybe put this after the first query:

StartTimeForBatchInsertQuery = data["time"]

and then redo your second query like this:

batchinsert=from(bucket: "Bucket2")
    |> range(start: StartTimeForBatchInsertQuery, stop: now() )
    |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
6

Thank you for the input, and you have nothing to be sorry about. I am grateful for your time.
Sadly, your suggestion just pushes the error back to the previous line:

image
image801Ă—115 8.83 KB

7

Hi @mauflege

Tried ChatGPT4 with this question and here is what it came up with. I have not had an opportunity to test it.

To use the timestamp obtained from one query as the start time value for another query’s range() function, you can use the join() function in InfluxDB. In this example, the timestamp_query gets the timestamp from the first query, and the second_query is defined as a function that takes the start time as a parameter. Then, the join() function is used to combine the timestamp from the first query with the second query, using the timestamp as the start time for the range() function.

// Replace these placeholders with your actual values
bucket_name = "TEMP_DATA"
measurement_name = "placeholder_1"
measurement_name_2 = "placeholder_2"

// Get the timestamp from the first query
timestamp_query = from(bucket: bucket_name)
  |> range(start: -2m)
  |> filter(fn: (r) => r._measurement == measurement_name)
  |> group()
  |> last()
  |> keep(columns: ["_time"])
  |> limit(n: 1)
  |> map(fn: (r) => ({_time: r._time, key: "timestamp"}))

// Second query that will use the timestamp from the first query as the start time
second_query = (startTime) => from(bucket: bucket_name)
  |> range(start: startTime)
  |> filter(fn: (r) => r._measurement == measurement_name_2)

// Join the timestamp_query and second_query and use the timestamp as the start time
result = join(
    tables: {timestamp: timestamp_query, data: second_query(timestamp_query[0]._time)},
    on: ["_time"]
)

// Yield the result
result |> yield(name: "result")
1 Like
8

hi @grant1,

thanks for looking into the problem again. I tried chatgpt as well, sadly got no helpful results.
Your’s was different and I tried it. Sadly, this is the result:

image
image1151Ă—190 12.1 KB

The same error that’s making things difficult.

9

I think that your problem is that data is still a stream of records. You’ll want to get an individual record before you can access fields of it.

1 Like
10

Brilliant suggestion @gazpachoking! You are correct in that one must get an individual record. @mauflege: There are probably several ways to do this, but in my sample dataset I got it to work using the Extract Scalar Value feature.

Something like this should work:

import "sql"
data=from(bucket: "TEMP_DATA")
  |> range(start: -2m)
  |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
  |> group()
  |> last()
  |> keep(columns: ["_time"]) // not sure this line is needed
  |> limit(n:1)
  |> yield(name: "table1")

tempInfo = data
    |> findRecord(
        fn: (key) => key._measurement == "placeholder_1",
        idx: 0,
    )

batchinsert=from(bucket: "Bucket2")
    |> range(start: tempInfo._time, stop: now())
    |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
    |> yield(name: "table2")
1 Like
11

thank so much @grant1 & @gazpachoking you for the effort you are putting in. Result of the first 2 queries are looking good:

image
image639Ă—793 22.3 KB

The second query, puts a stop to that:
(picture in second post)
any thoughts on that?

12

(picture referenced in first post)

image
image581Ă—824 27.4 KB

13

Hi @mauflege

About a week has passed since I worked on this, and no longer have my working example handy. But I am sure it worked, so you are getting close.

What if you change the range in the first query? For example:

  |> range(start: -2m)

or

    |> range(start: 2023-04-01T00:00:00Z, stop: 2024-04-02T00:30:00Z)
1 Like
14

the query is returning a "null’ value, it says it error on line 19. tempInfo doesn’t have _time property because it is a “null” value.

change this line |> keep(columns: ["_time"]) // not sure this line is needed
for this |> keep(columns: ["_time","_measurement"]) // not sure this line is needed or just delete it entirely.

the explanation:

tempInfo = data
    |> findRecord(
        fn: (key) => key._measurement == "placeholder_1",
        idx: 0,
    )

is looking for .

_measurement == “placeholder_1”

if you used |> keep(columns: ["_time"]) then there is no _measurement tag anymore, you dropped it! so, findRecord() is going to return a “null” value. because there isn’t any record with tag named _meassurement!

1 Like
15

Perfect explanation. Thanks!

1 Like
16

Thanks for the explanation, built myself a fresh test environment, did what you guys proposed-- same result:
Query

import "sql"
data=from(bucket: "TEMP_DATA")
  |> range(start: -2y)
  |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
  |> group()
  |> last()
  |> limit(n:1)
  |> yield(name: "table1")

tempInfo = data
    |> findRecord(
        fn: (key) => key._measurement == "placeholder_1",
        idx: 0,
    )
batchinsert=from(bucket: "TEST_DATA___X")
    |> range(start: tempInfo._time, stop: now())
    |> filter(fn: (r) => r["_measurement"] == "placeholder_1")
    |> yield(name: "table2")

Result:

image
image1276Ă—727 27.6 KB

If i run the query without the batchinsert and tempInfo, the result is a value:

0	placeholder_1 xxx xxx 2021-05-04T23:51:18.942Z	2023-05-05T11:51:18.942Z	2022-10-18T16:27:43.360Z	xxx	123456 xxx

“2022-10-18T16:27:43.360Z” ist the _time value

17

tempInfo is returning a null value, the fact that you can see the result of the first table does not mean that findRecord() is finding a record. delete |> group () or use except by time.

 // |> group()
  |> group(columns: ["_time"], mode:"except")

I cannot explain quite well why, but its like _time is reserved to be used as a group column, if you ungroup using group() then there won’t be finding a “_time” column and the result will be null.

probably renaming or duplicating the column _time to another before doing the findRecord, and then referencing the new name may work too.

1 Like
18

@fercasjr Thanks so much, that did it. Got it working.
@grant1 Thank you for your patience in looking for a solution and helping me out.
@gazpachoking Thank you for providing the initial push towards a solution!

1 Like