Where is my last value!

Grafana InfluxDB
1

Hello,

First I am sorry, because I am not sure if this message should go in the influxdb community, or the grafana one. I first posted in the influxdb one, but I think that more and more this is a grafana way of working I miss.

I am doing a daily gauge graph in order to display my electrical consumption per hour. The range set in grafana is Today so far.

At the very beginning I was using Influxql and it was working well

SELECT difference(last("value")) FROM "table" WHERE ("field" = 'toto') AND $timeFilter GROUP BY time(1h) fill(null)

The source gives me a cumulative value, so the goal is just to have a difference per hour between the last value of the current hour and the last value of the previous hour.
The result would be the same if I was using the first value, but the ergonomy would be different because I would have to wait the next hour to get the consumption of the current hour. By using the last value, I have a real time graph and my wife loves that.

Now I have just migrated to flux

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "measure" and r["_field"] == "toto")
  |> aggregateWindow(every: 1h, fn:last)
  |> difference()

I thought this kind of request would do the job, because it is quite straightforward, however it does not work as intended. First because flux is not taking into account the timezone (I know that is a flux problem and not a grafana one), but also and mainly because I do not have the last value of the previous hour (the last value of the day before). Therefore I am not able to get the electrical consumption of midnight.

It would work with the first function but I am loosing the real time feature… which I don’t mind… but it is important for my wife and therefore becomes naturally a top priority for me :wink:

It was working well with influxql but not anymore with flux.

My more general question is how we can use the last function, because due to the range given by grafana, we will always have a last value missing, which was not the case (for an unknown reason to me) with influxql.

For reference here is my message on the influxdb community Aggregation per hour - Fluxlang - InfluxData Community
I have some very ugly workaround I described, and I am pretty sure that there is a better way to go.

Thank you in advance for your help,
Bill

2

Hi,
this question is long time open, but I just moved as well to flux base query and have the same problem, this may help setting in the beginning of a query:

Import “timezone”
option location = timezone.location(name: “Europe/Berlin”)