New query with result of multiplying 2 queries

Grafana Dashboards
1

Hello!

I have 2 queries in a time series to get the energy pricing per hour and the energy usage from grid per hour. The data is in a MySQL database.

Energy pricing is kind of straight forward. There is an API from the company where I pull ever hour the pricing and when new data is available, I insert it into the database. Pricing is different for every hour. In the last 50 days it was between -14,13 and 23,51c/KWh, average is about 7c/KWh.

SELECT
  unix_timestamp(time) AS "time",
  val AS "price"
FROM energypricing
WHERE
  $__timeFilter(time)
ORDER BY time

Energy usage is different. I have a Shelly3EM installed and access its API every 6 seconds and write it into the database. But since it’s a lot of data I calculate the average for every 6min and write that into the history table and use that. And since we have a PV system with batteries, I’m only interested in the energy we buy from the grid.

SELECT 
    DATE_FORMAT(`time`, '%Y-%m-%d %H:00:00') AS hour,
    unix_timestamp(time) AS "time",
    SUM(`val` * 0.1) AS Wh
FROM
    shelly3em_history
WHERE
  $__timeFilter(time) AND
  topic = "history/home/shelly3em/totalPower" AND
  val > 0
group BY
    hour
ORDER BY
    hour;

Both work and give me plausible results. But they also don’t always line up correctly, see pic:

xxxxxxx
xxxxxxx3118×718 134 KB

Green is energy pricing.
Blue is energy usage.

Question is, What’s the best way to multiply both to get the actual price I’m paying for energy per hour?

Energy_pricing * Energy_usage / 1000

Or new query with select of all the data and calculate it in SQL?
Or would it be easier to do that outside Grafana and write it into a database and just add a third query to visualize that?

BR Alex

2

for performance it’s usually better to let the db do the work…

if you just want to play with it, you can use the binary calculation transformations, but you will need to do it in two steps, pricing * usage = total, then total * 1000

3

OK thank you for the reply!

I just thought maybe there is a simple way to do it in Grafana since I already have the data here.
I‘ll play around more with it later.
Maybe it might even be the simplest to do it in NodeRed or so. Just run a script every hour to calculate the pricing for the last hour and insert it into a new table or so.
Or maybe i can get it run in SQL.

4

this portion you can do in grafana