Aggregation per hour

Fluxlang
1

I am migrating from influxdb 1.8 to influxdb 2.0 and to influxql to flux.
I have some troubles to migrate this basic request that in computing electricity consumption per hour for the current day:

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

The goal is just to get some grafana bar gauge.

With flux, it would become something like that:

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()

However the behavior is a bit different, because the first consumption is not taken into account. It makes sense for me, because to get the first consumption, I would need the last value from the previous day, and the range is not giving this value.

I can solve the issue, by using first instead of last in my aggregateWindow, however by doing that I am loosing the real time feature where I can see the electricity consumption increase for the current hour.

Do you have a fix for this issue ?

Thank you in avance,
Bill

2

Update: Another solution that should solve the problem. The idea is to substract 1h to the range start and perform the same computation using last.

import "date"
import "experimental"

day = experimental.subDuration(d:1h,from: date.truncate(t: now(), unit: 1d))

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

I have found a workaround

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

The function spread is basically doing last() - firsrt(). So I don’t need the last value from the previous day. However it is not perfect because I am loosing some values… Indeed I am losing the electrical consumption every hour between the first computation of the current hour and the last computation of the previous hour. It is not a big deal… however it is not perfect and if there is a way to get things perfectly working I would be interested.

3

Hello @billdangerous,
I’m not sure I entirely understand your problem. It would be helpful for you to share some input data and your expected output data. However looking a this:

It might be worth noting that you can query data from the start of the day with:

import "date"
today = date.truncate(t: now(), unit: 1d)

You can also apply multiple range() functions.

Oh nvm it looks like you found some solutions. I’ll keep the above info just for future community members.

I’m confused what you mean here. Are you saying your data looks like this:
time value hour_position
1 2 top
2 4 bottom
3 7 top
4 3 bottom
And right now you’re getting:
time value_spread hour_position
2 2 bottom
4 4 bottom
But you want:
time value_spread hour_position
2 2 bottom
3 3 top
4 4 bottom
?

4

Hello,

Sorry my english is not the best one :slight_smile:

I have a source that gives me the electrical consumption since my subscription. So it is an only increasing source, meaning that if I subtract the value retrieved at 2pm from the value at 3pm, I have my electrical consumption between 2pm and 3pm.

I retrieve multiple values in an hour, so i have multiple choices:

  • I can subtract the first value from 2pm to the value from 3pm.
    from(bucket: "bucket")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "measure" and r["_field"] == "toto")
    |> aggregateWindow(every: 1h, fn:first)
    |> difference()

The drawback is that I do not have any realtime value, because the consumption of the current hour is not displayed and cannot be. To get the current consumption I need the first of the next hour, which does not currently exist.

  • I can use the spread function which is doing last() - first(). I do have realtime display, because a last value always exist. However, I am loosing some computations:
F = FIRST, S=SECOND, L=LAST
| F1 S1 ..... L1 | F2 S2 .... L2 | F3 S3 ... L3 |
Consumption Hour1 = L1-F1
Consumption Hour2 = L2-F2
Consumption Hour3 = L3-F3

In that computation that is performed by the following function

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

I am losing the following values : F2-L1, F3-L2, so the result is not perfect.

  • Finally, my preferred method is to use the last function:
    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 have the realtime display (because I do not need any value from the next hour), I am not losing any values (Consumption Hour2 = L2-L1) however there is one drawback :frowning:
The consumption of the Hour1 needs this computation : L1-L0, and unfortunately the range Today so far from grafana does not give me the L0 value which belong to the previous day…

I do not have this problem with influxql, for an unknown reason… and when I compare both results (Influxql vs flux), it is different for this first hour due to the fact that L0 is missing.

I hope I am a bit clearer :sweat_smile:

Thank you

5

I’m having the exact same issue here

my flux query looks like:

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "kWh" and r._field == "value")
  |> filter(fn: (r) => r.entity_id == "daily_energy_nt")
  |> aggregateWindow(every: 1h, fn:max, createEmpty: false)
  |> difference()

however the issue here is that I lose the first values of the time window due to the diffrence