No Timestamp if i summary Data

InfluxDB 2
1

Hi,
I have a problem when I summarize my data. I use Grafana for a Time Series Visualization and this need also a valid Timestamp to add the Data to graphic with formated Timestamps.

from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last)
  |> sum()

This is my Query, how can i add here a timestamp of the latest data

2

@Starfoxfs sum() is an aggregate function and _time isn’t in the group key, so it gets dropped. You can add it back in by duplicating the _start or _stop columns as the new _time column:

from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last)
  |> sum()
  |> duplicate(column: "_start", as: "_time")
3

Thanks for the fast Reply,

I have a Solar Power Station, and wan´t to visualize the Consumption for every month and over 1 Year (12 Bars 1 for every month).

The Data that is written in the InfluxDB is the Today Power Consumption but with a Datapoint every Minute.
So i need every last Datapoint of 1 Day.

I think i need for this 2 Querys because for the first Query what summarize the Data the Time of the Visualization doesnt match.

So that´s my first Query with the Collected Consumption for 1 Month.

from(bucket: "bucket1")
  |> range(start: -1mo)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 1d, fn: last, createEmpty: false)
  |> sum(column: "_value")
  |> group (columns: ["_time", "sum"])

Now i need a second one to add this first value (sum) to the right Date for this month.

from(bucket: "bucket1")
  |> range(start: today())
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 30d, fn: last, createEmpty: false)

I need to join or union the first Query with the seconds _time to visualize this in the right way.

4

@Starfoxfs

I know you collect a reading every 1 minute, whereas I collect my kWh every hour. See below from January thru August (8 months, or 243 days)

Here is the data by hour (5832 points on the graph, or 243 x 24)

image
image2188×735 235 KB

Here is the data by day (243 points on the graph)

image
image2188×735 189 KB

Here is the data by month (8 points on the graph):

image
image2188×735 124 KB

Note that I am aggregating the data differently in each of the above. I am using the fn: mean to collect the arithmetic average of the hourly values collected (the average of 24 readings in the 1d aggregateWindow, and the average of all hourly readings during the month in the 1mo aggregateWindow).

You can try the same, but using fn:sum

5

I tried this before,

from(bucket: "bucket1")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "power")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> yield(name: "sum")

With this i gettin wrong Data, i get 453 kilowatt Hours.

Here is a Graphic how it collects data:

collect
collect2048×1152 42.5 KB

If i use your Query i have the sum of all Data entrys a day e.g. for the first table on the Picture it is 15 in sum

But that´s wrong, i need every day only the last value, in this case 5 and for the next day 7 and then the sum of all last values over 30days or 1 month

6

I almost there, how can I make the first table (0) “invisible” ?

from(bucket: "solarkraftwerk")
  |> range(start: -1mo, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "solarkraftwerk")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last, createEmpty: false)
  |> sum(column: "_value")
  |> duplicate(column: "_start", as: "_time")
  |> group(columns: ["_measurement", "_field", "_time", "_value"])
  |> aggregateWindow(every: 30d, fn: last)

Unbenannt
Unbenannt1901×1021 138 KB