SQL Statement to calculate my total daily power consumption based on Watts

Hello community,

I’m currently creating a Dashboard to visualize my solar generation, how much energy comes from the grid and how much my total power consumption is groued by day for the last 90 days.

The solar generation and the grid consumption are done, because these values I have as total kWh from my energy meter. I initially used a transformation to calculate the power consumption, but the values were incorrect, because i can’t use my solar power completely: approx 50 % are send to the grid.

The attached image show the expected diagram, but the total consumption must be lower

This is the query i used for solar and from the grid

SELECT
  $__unixEpochGroupAlias(ts_sec,1d,0),
  avg(val) - lag(avg(val)) OVER (ORDER BY $__unixEpochGroup(ts_sec,1d,0)) AS "From Grid"
FROM ts_number
WHERE
  ts_sec >= $__unixEpochFrom() and ts_sec <= $__unixEpochTo() and id = 2
GROUP BY 1
ORDER BY 1

I’m struggeling to calculate my total power consumption, because these values I only have as the current Watts.

It would be great if you can help me with the SQL to query to the daily power consumption .

Many Thx & Regards

Martin

What database type is it? Mysql?

ah, sorry. Its PostgreSQL

1 Like

how do you define power consumption, what is the formula base on the existing columns you have

consumption=yadi+yada/bomshaka

Seinfeld ref ^

btw this is more of a postgres question than a grafan question. might be better off asking it in a postgres forum but we can try to help

1 Like

mmh, i guess you’re right that is morge a postgresql question. I will post the questen there aswell.

So whats my definition of power consumption

In the 2nd picture you can see how much power i use every 10 sec for the last 24 hours. Sometimes i use more energy, sometimes less

So the value i want to have is the avarage of these values for the last hour. This would be the power consumption of one hour. If i want to check the time period of 24 hours i have to build the average of the last 24 hours and multiply it with 24 hours

Hopefully this was understandable :slight_smile:

Thx in advance

1 Like

yes, writing down this definition already helped to find a solution myself.

Now i’m able to see the power consumption, my solar generation, how much I send to the grid and how much I consume from the grid.

avg(val) *24 / 1000 as "Verbrauch"

I just had to build the average, multiply it by 24 hours and devide it by 1000 to come from Wh to kWh