Start range from the first data

InfluxDB 2
1

I have a measurement storing data from my solar inverter. I want to create a graph showing the generation for the current day. If I specify |> range(start: today()) the graph has a lot of empty space from midnight until the first reading comes in. I want the range to start from the first reading of the day. Outputpower is the value where the current generated power is stored.

I did this, but it is not working:

starttime = from(bucket: "nodered")
  |> range(start: today())
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "outputpower")
  |> group()
  |> first()

from(bucket: "nodered")
  |> range(start: starttime._time)
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "outputpower")

The first selection is working, I get the first record for today, and now I would like the second query to start from the _time field of the 1st selection. But the starttime._time reference is not accepted. Also I was not able to just store the _time value in starttime.
Maybe my approach is incorrect, but I was not able to find any better option.

2

You are going in the right direction. Good work!
Just starttime is table stream, even with just a single table. You have to extract record:

rec = from(bucket: "nodered")
  |> range(start: today())
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "outputpower")
  |> group()
  |> first()
  |> group(columns: ["_field", "_measurement"], mode:"by")
  |> tableFind(fn: (key) => key._field == "outputpower")
  |> getRecord(idx: 0)

from(bucket: "nodered")
  |> range(start: rec._time)
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "outputpower")

This should work for you.

3

@VlastaHajek, thanks a lot for the tip. It is working.
If I may ask, can you explain why the last 3 lines were needed:

  |> group(columns: ["_field", "_measurement"], mode:"by")
  |> tableFind(fn: (key) => key._field == "outputpower")
  |> getRecord(idx: 0)

I mean even without these 3 lines the filter returns a single line with _time field. So I am not sure why I was not able to reference to the _time field with rec._time. I am guessing because there may have been multiple lines in the table. So the getRecord(idx:0) makes sense, but I still don’t understand why the second group and tableFind was required.

4

By default, all data going through the functions is a table stream. Even if there is just a single row.
You can see this in View Raw Data in Data Explorer.

IIUC, you cannot access a single row from a table stream.

You need to convert stream data to the table struct with records.

This is done via tableFind, which looks for a specific table in the table stream. Each table is identified by group keys. So, if you remove group keys by calling empty group(), tableFind cannot find a table. By calling group(columns: ["_field", "_measurement"], mode:"by"), group keys are added back, even it is not necessary for filters. It should be sufficient to have only _field as a grouping key.
Once you have a table with records, you can just select 1st record row you need by getRecord(idx: 0)

5

This was very useful thank you. I think I will use the Table view more as well, as that shows the Group Keys as well.
I will also try to see if I can eliminate the first empty group() so I don’t have to use the second group just to re-introduce the group keys for tableFind.

6

Ungrouping via group() is necessary to merge data into a single table to find the first row. You can play around with Flux and check what are results. :wink:
I found that you don’t to use the second group() if your tell findTable to get the first possible table by |> tableFind(fn: (key) => true)