Power Meter / Energy per Day / Date Problem


I use an Energymeter SDM630 that writes the data to the Flux database via MBMD (GitHub - volkszaehler/mbmd: ModBus Measurement Daemon - simple reading of data from ModBus meters and grid inverters). It writes every some seconds the current power.
This works as far as it goes.

I would like to display the energy consumption per day in Grafana. My problem is that the last date is double and the first date has wrong values (should be around 12kW per day).
(See screenshot markings)

This is my current query:
Do you have any idea how the query should be correct?

from(bucket: "sdm630")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "data")
  |> filter(fn: (r) => r["_field"] == "value")
  |> filter(fn: (r) => r["device"] == "SDM1.1")
  |> filter(fn: (r) => r["type"] == "Power")
  |> aggregateWindow(every: 24h, 
                    fn: (column, tables=<-) => tables |> integral(unit:1h, interpolate: "linear"),
                    createEmpty: false)


Hello @Guido_Berger,
Thanks for your question.
Your query looks fine. This seems like it might be a grafana problem.
Have you tried querying via the api or influxDB UI to check to see if you get the same results?
Thank you

Yes it is the same in the influxDB UI. I now installed the latest 2.3 Version to use the timezone feature. May be it is timeszone issue. So after update the old buckets were lost.
So I have to wait some days to collect new data. I will try to set the timezone in the query and come back to you in a few days


I have the same meter and also using MBMD, so familiar issues :wink:

You have a couple of things going on:

First you can get the actual Kwh meter count from the SDM630 by using the “import” field. You can then use the difference between the registrations at the end of all days. That is more accurate then trying to integrate the instant Power over time.

Secondly, you have an issue with timezones. As you can see it now aggregates using 00:00 UTC while you seem to be at UTC+2.

Third, you query now uses a different time range, depending when you execute this, so beware to pick the good time range manually (which you can not do if you would execute this as a task).

I ran also into the timezone and range issues, especially trying to automate this using a Task, see also the discussion here

This is how I solved this in my task, that produces the usage and the end-meter value and runs every day:

import "timezone"
import "experimental"

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

startoffset = 2d
tzoffset = 0h
start = experimental.subDuration(d: startoffset, from: today())
stop = experimental.subDuration(d: tzoffset, from: today())

data =
    from(bucket: "telegraf")
        |> range(start: start, stop: stop)
        |> filter(fn: (r) => r["_measurement"] == "data")
        |> filter(fn: (r) => r["_field"] == "value")
        |> filter(fn: (r) => r["device"] == "SDM1.1")
        |> filter(fn: (r) => r["type"] == "Import")

    |> aggregateWindow(every: 1d, fn: last, createEmpty: false, location: timezone.location(name: "Europe/Amsterdam"))
    |> map(fn: (r) => ({r with _endvalue: r._value}))
    |> difference(nonNegative: true, columns: ["_value"], keepFirst: false)
    |> map(fn: (r) => ({usage: r._value, endvalue: r._endvalue, _time: r._time, _measurement: "solar_daily"}))
    |> timeShift(duration: -1d)

Hope this helps!