4 Months Time Series Chart per Month collected?

Grafana PostgreSQL
1

Hi,

i want to make a chart per Month collection of Power Consumption in a Grafana Time Series Visualization.

How to make this with Postgresql ?

I have one Time Field with timestampz, the value and the sensor Information in Database.

Collected is the Powermeters Total consumption so i have to substract the End value of a Month from the first value of a month and show the Last 4 Month of this calculation.

I have this Query but that Outputs the wrong values:

SELECT date_trunc('month', time) AS time_month, sum(value) AS kwh_month FROM emeter WHERE field = 'totalemeter' GROUP BY time_month

Any Ideas how to make this ?

2

This is from a working example of InfluxDB with FlightSQL, but maybe it will help?

SELECT date_Bin(INTERVAL '1 month', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time, sum("value") AS sum_value, region
 FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN (${region}) AND $__timeRange(time) GROUP BY 1, region
 ORDER BY time ASC
3

Hi tanks for the answer but it helped not really.

Time / Total

Timestamp / 192002
Timestamp / 192001
Timestamp / 192000

So i have to calculate the difference between the first (192000) and the last value (192002) per month and Group this to monthly Output in Grafana. Here the difference is 2

This Calculation for 4 Month or 4 Bars in Grafana.

I have the Influx Flux Query here but i dont know how to convert this into PostgreSql or SQL:

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

from(bucket: "solar")
  |> range(start: -4mo, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "emeter")
  |> filter(fn: (r) => r["sensor2"] == "power")
  |> filter(fn: (r) => r["_field"] == "total")
  |> difference()
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false, timeSrc:"_start")
4

I guess the value is cumulative counter, so IMHO it can simplified:

SELECT 
  date_trunc('month', time) AS time_month, 
  MAX(value)-MIN(value) AS kwh_month 
FROM emeter 
WHERE field = 'totalemeter' 
GROUP BY time_month

You can use your favorite LLM model. It may provide some hints/explanations/ideas, e.g.:

WITH MonthlyCounters AS (
    SELECT
        DATE_TRUNC('month', event_date)::date AS month_start,
        MAX(counter_value) AS monthly_max_counter -- Assuming counter values are cumulative
    FROM
        your_table_name
    GROUP BY
        1
)
SELECT
    month_start,
    monthly_max_counter,
    monthly_max_counter - LAG(monthly_max_counter, 1, 0) OVER (ORDER BY month_start) AS monthly_difference
FROM
    MonthlyCounters
ORDER BY
    month_start;
1 Like
5

That works fine big thanks for that :smiley:

6

I have a second Query what doesnt work:

time / kwh

timstamp / 8000
timestamp / 7000
timestamp / 6000

Here i need only the summary of all last or max values on a day calculated together for a month over 12 month (12 bars in Grafana)

The Influx Flux Query was this:

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