Question 362229 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query to get kWh from every minute watts readings

I’m trying to get the amount of kWh consumed by my smart plug, my database is MySQL, I’m sending to my zabbix server (which uses MySQLdatabase) 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

  • consumption per hour
  • consumption per day
  • consumption per month
  • consumption per year

How could i achieve this with MySQL queries?

Tags:

2

Answers


  1. 0

    Consumption per hour:

    SELECT 
    FROM_UNIXTIME(FLOOR(clock / 3600) * 3600) AS hour_start,
    SUM(value) / 1000 / 60 / 60 AS kWh_consumption
FROM 
    history 
WHERE 
    itemid = 54339
GROUP BY 
    hour_start

    Per month you will have to concat year and month or select in separated columns:

    SELECT 
    CONCAT(YEAR(FROM_UNIXTIME(clock)), '-', LPAD(MONTH(FROM_UNIXTIME(clock)), 2, '0')) AS month_year,
    SUM(value) / 1000 / 60 / 60 AS kWh_consumption
FROM 
    history 
WHERE 
    itemid = 54339
GROUP BY 
    month_year
    Login or Signup to reply.
  2. 0

    Totals by several periods WITH ROLLUP.

    See example

    select coalesce(yy,'total') yy
  ,case when yy is null then '--'
   else coalesce(mm,'tot yr')
   end mm
  ,case when mm is null then '--' 
   else coalesce(dt,'tot mnth') 
   end dt
  ,case when dt is null then '--'
   else coalesce(hh,'tot dy') 
   end hh
  ,cons
from(
select year(clock) yy
   ,month(clock) mm,date(clock)dt,hour(clock) hh,sum(val) cons
from history
group by year(clock),month(clock),date(clock),hour(clock) with rollup
)t

    Output like this

    yy mm dt hh cons
    2024 3 2024-03-17 21 1071
    2024 3 2024-03-17 22 535
    2024 3 2024-03-17 23 532
    2024 3 2024-03-17 tot dy 2138
    2024 3 2024-03-18 21 264
    2024 3 2024-03-18 23 264
    2024 3 2024-03-18 tot dy 528
    2024 3 tot mnth — 2666
    2024 4 2024-04-01 7 264
    2024 4 2024-04-01 tot dy 264
    2024 4 tot mnth — 264
    2024 tot yr — — 2930
    2025 4 2025-04-01 7 101
    2025 4 2025-04-01 tot dy 101
    2025 4 tot mnth — 101
    2025 tot yr — — 101
    total — — — 3031

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search