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.
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”.
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?