Show kWh per hour from SQLite data with cumulative value

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.
https://community.grafana.com/t/newbie-how-do-i-split-cumulative-kwh-measurement-to-difference-per-time-period/58521

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.

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 :stuck_out_tongue: 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:

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

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 :sweat_smile:

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

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.

Hi guys,

I’m trying to the same, but my database is mysql, i’m sending to my zabbix server (which uses mysql database) the power consumption in watts every minute.

ex:

SELECT FROM_UNIXTIME(clock) as ts, value FROM history WHERE itemid=54339

2024-03-17 21:36:40 261
2024-03-17 21:37:39 271
2024-03-17 21:38:40 271
2024-03-17 21:39:40 268
2024-03-17 21:40:39 264

So if i wanted to calculate the kWh and put in 4 graphics
consumption per hour
consumption per day
consumption per month
consumption per year

How could i achieve this with mysql queries?

I think you will get more traction on a mysql forum than grafana because your issue is more mysql than grafana

Yes, meanwhile i was able to get it right:

SELECT
FROM_UNIXTIME(clock, ‘%Y-%m-%d %H’) AS hour,
SUM(value * (1/1000) * (1/60)) AS kwh_consumed
FROM
history
WHERE itemid=54339
GROUP BY
hour;

SELECT
FROM_UNIXTIME(clock, ‘%Y-%m-%d’) As day,
SUM(value * (1/1000) * (1/60)) AS kwh_consumed
FROM
history
WHERE itemid=54339
GROUP BY
day;

SELECT
FROM_UNIXTIME(clock, ‘%Y-%m’) As month,
SUM(value * (1/1000) * (1/60)) AS kwh_consumed
FROM
history
WHERE itemid=54339
GROUP BY
month;

SELECT
FROM_UNIXTIME(clock, ‘%Y’) As year,
SUM(value * (1/1000) * (1/60)) AS kwh_consumed
FROM
history
WHERE itemid=54339
GROUP BY
year;

1 Like