Show kWh per hour from SQLite data with cumulative value

[kjetilkl]

I am using Grafana 8.3.3 connected by with SQLite datasource from HomeSeer
(Not sure if this will be the same in SQLite vs MYSQL ref tags)

I am trying to calculate my power cost for a specific Smarthome device, and to help me do this i have cumulative kWh values from this specific device, and the power cost per specific hour.

To do the calculations i am trying to group the cumulative Kwh values difference per hour.

Firstly, i am almost a complete novice when it comes to SQL, so i am painfully aware i probably need to be spoonfed anything but the simplest queries.
I have been searching a lot, but most relevant examples i find seem to be using Influxdb as datasource, and alas time-functions i cannot use, or be too complex or general to help me understand them

My problem is similar to this, but using SQLite as datasource.

Newbie: How do I split cumulative kWh measurement to difference per time period?

Grafana 7.3.4 I’m trying to get a graph that would display my heat pump power consumption per hour or day or week or month I get cumulative kWh readings every minute from a Gavazzi sensor through modbus and nodered that are being fed into my influxdb database. I’m getting the cumulative values displayed correctly in Grafana, but how do I split it to only show difference since last value? Be that value say last day, last hour or last month depending on my time period set. This is my query tha…

My current query looks like this, and creates a timeseries graph that shows current kWh value for that device after converting the SQLite time to the correct format:

WITH converted AS (
          SELECT "NEW_VALUE", REPLACE("CHANGE_DATE", ' ','T') || 'Z' AS datetime FROM "DEVICE_VALUE_HISTORY" WHERE "DEVICE_REF"=437
)
SELECT datetime, "NEW_VALUE" FROM converted WHERE NEW_VALUE >1 ORDER BY datetime ASC

What i would like is to instead have a bar-chart that shows how many kWh was consumed per hour.

The (time-converted)table data for the cumulative values look like this:

Any help would be greatly appreciated.

[thingwizard]

Basically if you have the power draw in watts at any given minute, and you want kWh, you should fetch a mean value for one hour, and you have how many watt hours you drew that hour. Then set the query min interval to 1h to show that mean. the query interval needs to be 1h to get the mean over 1h for kWh. If you have it set to 1week for example you would get kWw kilowattweek You need to group by $_interval but if you don’t get the right things out it might be dependent on how your time field is formatted.

I’m using influxDB as a source here but the logic should be the same:

[frser]

@kjetilkl

I have been searching a lot, but most relevant examples i find seem to be using Influxdb as datasource, and alas time-functions i cannot use, or be too complex or general to help me understand them

@thingwizard

I’m using influxDB as a source here but the logic should be the same:

Not sure if an influxDB example is the best way to illustrate with his previous problems with the influxDB examples.

@kjetilkl

I am using Grafana 8.3.3 connected by with SQLite datasource from HomeSeer
(Not sure if this will be the same in SQLite vs MYSQL ref tags)

Are you sure “HomeSeer” is correct? I will just continue on the assumption you meant the SQLite plugin from “frser”. And at the same time the MYSQL ref tags, do indeed hinder the search for SQLite problems

I would suggest trying the grouping macro from the SQLite plugin as a first step: GitHub - fr-ser/grafana-sqlite-datasource: Grafana Plugin to enable SQLite as a Datasource

If that one fails you can fall back on raw SQL but I don’t see why it won’t work.
Your query would look like this (untested):

WITH converted AS (
          SELECT "NEW_VALUE", REPLACE("CHANGE_DATE", ' ','T') || 'Z' AS datetime FROM "DEVICE_VALUE_HISTORY" WHERE "DEVICE_REF"=437
)
SELECT $__unixEpochGroupSeconds(datetime, 3600), "NEW_VALUE"
FROM converted WHERE NEW_VALUE >1
ORDER BY datetime ASC

[buuuh]

I’m doing this manually in the sql like (assuming the time is a unix timestamp)

SELECT time/3600*3600 as cumulated, avg(value) FROM table GROUP BY cumulated

Since i’m currently looking for performance issues, this takes (on my system of course) half the time of using $__unixEpochGroupSeconds.