Simulate the usage of a battery

Grafana MySQL
,
1

I want to Simmulate the usage of a battery in a Grafana Panel with Grafana 8.4.4.

As Input I use two mysql tables. With two queries I can estimate the local power supply and the consuption. With these querries I get the input and output of the battery for each hour. From both Queries and a Varible with holds the max amount energery, that can be stored.

I know that this calculation would be a bit more complicated in SQL, therefore I want to ask, whether Grafana has some other ways to calculate these a bit more callengeing task.

a)
First I would have to calculate the diff of supply and consumption of the last hour, because this is the engery that can potentially be stored or taken for the battery (or the grip, in case the battery is empty). From that
So I start with a list that is moved by one index to the right (future). Use last diffence to find out of the current will be “added or drawn”.

b)
Than I would need to incrempentially calculate the profile of charging/ decharging the battery.
The next battery fill value is: max(min(LastFillValue + DiffOfTheLastHour - CurrentPowerGAP), $BatterySizeVaribale, 0)). This has to be calculated for all the hours of the time frame.

This Is relative easy to write in Python, Matlab, etc. But A bit less easy in SQL. What is the prefered way to do that in for Grafana? Should a change to another database? With a different query language?

2

please post at least some sample data as DDL and DML?

create table x1(battery_id int, readingtime datetime, powerlevel int )
insert into x1
select 1, getdate(), 5 union
select 2, getdate(), 13

Then provide a similar sample data of the output you want to plot in grafana

etc. Then we can use this in our database and plug it in to try to help you.

3

Hi I wanted to make the question as generic as I could, so that the answer can be abstracted to similar question. A somewhat simplified take would be:

My current state is, that I have two different queries for the incoming current I got:


SELECT

STR_TO_DATE(time,'%Y-%m-%dT%H:%i:%s') AS "time",

ProfileIn* $TotalPower * 0.76 / 1000 AS Power -- 0.76 is a correction factor

FROM grafana_in

WHERE Orientation = $Orientation AND Slope= $Slope

as for the consumption that either draws from the incoming or the battery (buffer):


SELECT STR_TO_DATE(time,'%Y-%m-%dT%H:%i:%s') AS "time",

-1* ConsomptionProfile* $TotalConsumption/ 1000 * AS Power

FROM grafana_out

WHERE BuildingType = $BuildingType

I multiplied the consumption by -1, because it is outgoing.

The next steps are to use the data from these two queries, to estimate to currently used buffer size, While the buffer cannot be zero and has a $MaxBufferSize.

I try to sketch the next steps in Python meta code because I, know it better:


diff = in_coming + out_going # outgoing is negative

rest = np.where(in_coming < out_going , out_going - in_coming, 0)

buffer = [0, ]

for i in range(1, len(diff)):

new_fill = total[i-1] + diff[i] - rest[i]

buffer.append(max(min(new_fill, $MAXWERT), 0)))

The question is how would I transfer this into a query, that I can use in Grafana. Including the shift in the index (i-1) and the iteration. Which query language, plug-in in grafana etc. is best suited. Because I not simply query existing data but, calculate something in between, before I can show it in Grafana.

4

you say generic & abstracted etc but yet you do provide two queries with no sample data and you want the answer to be provided as a query. sorry but not sure I can help. some others might chime in