Latest of Kilowatthours over 24hours counted or sum to 1month Bars?

Grafana PostgreSQL
1

Hi,

i think that is the most difficult Query i have in Flux needs to be converted to Postgresql:

import "timezone"
// Set location to be Europe/Berlin
option location = timezone.location(name: "Europe/Berlin")

from(bucket: "solarpower")
  |> range(start: -12mo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "solar")
  |> filter(fn: (r) => r["_field"] == "todaykwh")
  |> aggregateWindow(every: 24h, fn: last, offset: -1s, createEmpty: false)
  |> truncateTimeColumn(unit: 1m)
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc:"_start")

I have tried this with 2 Select Querys:

SELECT
  SUM(todaykwh) AS "monthly_sum"
FROM (
  SELECT
    max(todaykwh) AS "last_todaykwh"
  FROM
    solarpower
  GROUP BY
    date_trunc('day', time)
)
GROUP BY
  date_trunc('month', time)

That didnt work i get an Error todaykwh didnt exists

timestamp / todaykwh

timestamp/ 5000
timestamp/ 4999
timstamp/4998

This latest or max value (5000) from 24hours and all latest 24hour values summarized for 1 month. over 12 months or 12 bars in Grafana.

2

That’s correct, because you have
max(todaykwh) AS "last_todaykwh"
, so you need
SUM(last_todaykwh) AS "monthly_sum"

3

Hmm ok, but it doesnt work again:

SELECT
  MAX(todaykwh) AS "last_todaykwh"
FROM (
  SELECT
    SUM(last_todaykwh) AS "monthly_sum"
  FROM
    solarpower
  GROUP BY
    date_trunc('month', time)
)
GROUP BY
  date_trunc('day', time)

Nearly the same Error: db query error: pq: column “last_todaykwh” does not exist

4

Let’s be visual:

image
image546×388 23.3 KB

5

Now i gettin Error Time Column doesnt exists.

I think because of Time Series Visualisation in Grafana

6 
SELECT
 SUM(last_todaykwh) AS "monthly_sum"
FROM (
  SELECT
    max(value) AS "last_todaykwh"
  FROM
    solarpower WHERE field = 'todaykwh'
  GROUP BY
    date_trunc('day', time)
)
GROUP BY date_trunc('month', time)

todaykwh was also wrong the Field with Data is value

But still : db query error: pq: column “time” does not exist

7

This doesnt work, If i remove the last GROUP BY then it works and i get the summary of all last values but not grouped by month.

If i add the last GROUP BY i get the above Error column “time” does not exist.