Please help with flux query - date not correct on bar chart

First I have to thank each and everyone here in the community - setting up and getting into the whole Grafana and InfluxDB topic would be impossible without you.
Now I’ve reached my first thing I can’t solve just by reading - please help. Thank you!
I’m currently rebuilding my power metering log from my APC UPS - this was done via InfluxDB v1.8 in the past - but I’d like to move over to v2.

  • What Grafana version and what operating system are you using?
    Docker - 9.3.4
  • What are you trying to achieve?
    Show the power usage of the last 7 days - get the data via a flux query and then show by day (or date)
  • How are you trying to achieve it?
    Using a flux query and calculating the kWh - then showing it in a Bar Chart.
  • What happened?
    The BarChart shows the correct data (kWh), but one day ahead.
    E.g. I have data from the 25th but the data is shown on the 26th.
  • What did you expect to happen?
    Data and date to match up on the cart.
  • Can you copy/paste the configuration(s) that you are having problems with?
This is my query:
import "timezone"

option location = timezone.location(name: "Europe/Vienna")

from(bucket: "ServerRack-UPS")
  |> range(start: -1w)
  |> filter(fn: (r) => r["_measurement"] == "upsd")
  |> filter(fn: (r) => r["_field"] == "load_percent")
  |> map(fn: (r) => ({r with _value: r._value * 720 / 100}))
  |> aggregateWindow(
    every: 1h,
    fn: (tables=<-, column) =>
        |> integral(unit: 1h)
        |> map(fn: (r) => ({ r with _value: r._value / 1000.0})))
  |> aggregateWindow(every: 1d, fn: sum)

And this is how the chart looks:

Hope I opened this Topic the right way. Thanks very much in advance!

Hi @n0ne117

(nice looking bar chart!)

I have seen this happen with certain utility companies (power, natural gas, etc.) that seem to report the usage 12h or 24h later than the actual.

Have you tried this at the very end (after the last aggregateWindow function)?

|> timeShift(duration: -24h)

Or the Line Interpolation options in Grafana?

Utility companies may do this, but surely not an APC UPS?



I tried the timeShift function - it unfortunately works only as intended :slight_smile: and moves all data points back one day - even the ones it actually shouldn’t.

I am using the new Telegraf UPSD Input Plugin which connects to my NUT server docker container - as far as I can see it delivers data every 10 seconds. Another chart on the Dashboard shows the current load in Watts - and there everything is fine.

The Plugin only delivers the current load in percent - I am able to calculate the load in Watts simply by knowing the maximal nominal load of 720 Watts.

That’s why I believe that the issue is not the display of the data but my query.
More specifically (as I found out in the meantime) with the

  |> aggregateWindow(every: 1d, fn: sum)

at the end - without that, the data is displayed at the correct days - just not grouped).

It is a bit hard to see in Grafana - but the InfluxDB Data Explorer shows it nicely:

(without aggregateWindow)

Sorry for the long reply! I try to be as clear as possible (and maybe show a way for others who want to use the new UPSD plugin and flux).

Thank you all again!