# Aggregation per hour **URL:** \ **Category:** Fluxlang\ **Tags:** influxql\ **Created:** [June 5, 2021, 11:01am UTC](https://community.influxdata.com/t/aggregation-per-hour/20254 "2021-06-05T11:01:41Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![billdangerous](https://avatars.discourse-cdn.com/v4/letter/b/f4b2a3/32.png) [@billdangerous](https://community.influxdata.com/u/billdangerous)\ **Post date:** [June 5, 2021, 11:01am UTC](https://community.influxdata.com/t/aggregation-per-hour/20254/1 "2021-06-05T11:01:41Z")
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 ---
**Author:** ![billdangerous](https://avatars.discourse-cdn.com/v4/letter/b/f4b2a3/32.png) [@billdangerous](https://community.influxdata.com/u/billdangerous)\ **Post date:** [June 5, 2021, 11:40am UTC](https://community.influxdata.com/t/aggregation-per-hour/20254/2 "2021-06-05T11:40:50Z")
_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. ---
**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\ **Post date:** [June 8, 2021, 7:01pm UTC](https://community.influxdata.com/t/aggregation-per-hour/20254/3 "2021-06-08T19:01:16Z")
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: > [@billdangerous](#): > > 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. It might be worth noting that you can query data from the start of the day with: ```auto 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. > [@billdangerous](#): > > 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… 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 ? ---
**Author:** ![billdangerous](https://avatars.discourse-cdn.com/v4/letter/b/f4b2a3/32.png) [@billdangerous](https://community.influxdata.com/u/billdangerous)\ **Post date:** [June 9, 2021, 7:38pm UTC](https://community.influxdata.com/t/aggregation-per-hour/20254/4 "2021-06-09T19:38:54Z")
Hello, Sorry my english is not the best one 🙂 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. ```auto 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: ```auto 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 ```auto 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: ```auto 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 ☹ 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 😅 Thank you ---
**Author:** ![mazzy89](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/mazzy89/32/8729_2.png) [@mazzy89](https://community.influxdata.com/u/mazzy89)\ **Post date:** [November 12, 2021, 5:48pm UTC](https://community.influxdata.com/t/aggregation-per-hour/20254/5 "2021-11-12T17:48:13Z")
I’m having the exact same issue here my flux query looks like: ```auto 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