Extracting the timestamp of the newest data point by tag

InfluxDB 2
1

Hi, @Anaisdg Total newbie here. I have a bucket “energy” and a measurement “inverterData” and data points with one of two tags “SD12345678” and “SD23456789”. I can pull data points, including contemporaneous timestamps, for a each data point for a whole day (about 280 points) from the inverter manufacturers cloud and populate my bucket with the data points. What I’m trying to do is to query Influxdb 2.3 to find the last date when data points were entered for each tag. I will then use that date to query the next date from the manufacturer’s cloud for each tag.

I’ve muddled along to:

from(bucket: "energy")
  |> range(start: 2022-01-01T08:00:00.000Z, stop: 2022-08-29T08:00:00.000Z)
  |> filter(fn: (r) => r["_measurement"] == "InverterData")
  |> filter(fn: (r) => r["device"] == "SD12345678")
//   |> filter(fn: (r) => r["device"] == "SD23456789")
  |> top(n: 3)

This gives me three data points for one inverter but they aren’t the latest timestamps. I’ve tried to group on _time but that gives me an error about string and float data types. I’ve also tried to filter on one of the data point key values (temperature) and while that looks to have given me the three highest temperatures, it’s the three latest timestamps, later limited to just the latest one.

Can anyone give me a pointer or resource that can help? It’s unfortunate that the first thing I want to do with Influxdb is to work with the timestamp rather than the data, but that will come later.

Thanks

2

Does anyone have an answer or at least any hints?

3

@GwelforBodad You can use last() to return the last row in each input table. By default, InfluxDB returns data sorted by time, so the last row should be latest point. One thing to note however is that if no data is returned in the queried range, last() won’t return anything. So you may need to “overshoot” your query range to ensure you get results.

from(bucket: "energy")
    |> range(start: 2022-01-01T08:00:00.000Z, stop: 2022-08-29T08:00:00.000Z)
    |> filter(fn: (r) => r["_measurement"] == "InverterData")
    |> filter(fn: (r) => r["device"] == "SD12345678")
    |> last()

To extract a time stamp from the returned results, you can use findColumn():

data = from(bucket: "energy")
    |> range(start: 2022-01-01T08:00:00.000Z, stop: 2022-08-29T08:00:00.000Z)
    |> filter(fn: (r) => r["_measurement"] == "InverterData")
    |> filter(fn: (r) => r["device"] == "SD12345678")
    |> last()

lastTime = (data |> findColumn(column: "_time", fn: (key) => true))[0]

You could then use the lastTime to execute another query. The problem here is that to get the latest point, you already have to query beyond the time you may want to query, so I don’t know how much value this adds unless you’re trying to aggregate values from a very specific point in time.

4

Mate, it doesn’t matter! You’ve given me the “starter for ten” as we say. I can work with this now I have something to go at. Cheers!

1 Like