Grafana in izračun porabe v kWh

Vse o programih, ki jih uporabljate

Moderator: Kroko

Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 29 Jul 2020, 20:03

Pred kratkim sem pričel uporabljati programski paket Grafana za izrisovanje grafov iz merilnika porabe električne energije. Podatke črpam iz MariaDB baze. V tabeli so shranjene meritve po ena na sekundo.

Struktura baze in vzorec meritev:
Koda: Izberi vse
(measurements)root$ DESCRIBE meritve;
+-----------+----------------------+------+-----+----------------------+--------------------------------+
| Field     | Type                 | Null | Key | Default              | Extra                          |
+-----------+----------------------+------+-----+----------------------+--------------------------------+
| id        | bigint(20) unsigned  | NO   | PRI | NULL                 | auto_increment                 |
| timestamp | timestamp(3)         | NO   | MUL | CURRENT_TIMESTAMP(3) | on update CURRENT_TIMESTAMP(3) |
| did       | smallint(5) unsigned | NO   | MUL | NULL                 |                                |
| tarifa    | varchar(2)           | NO   | MUL | NULL                 |                                |
| f         | float(4,2)           | NO   |     | NULL                 |                                |
| ua        | float(5,2)           | NO   |     | NULL                 |                                |
| ia        | float(6,4)           | NO   |     | NULL                 |                                |
| pa        | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pb        | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pc        | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pd        | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pe        | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pf        | smallint(5) unsigned | NO   |     | NULL                 |                                |
+-----------+----------------------+------+-----+----------------------+--------------------------------+
13 rows in set (0.00 sec)


Vzorec meritev:
Koda: Izberi vse
(measurements)root$ SELECT * FROM meritve LIMIT 20;
+-----------+-------------------------+-----+--------+-------+--------+--------+-----+----+----+----+----+----+
| id        | timestamp               | did | tarifa | f     | ua     | ia     | pa  | pb | pc | pd | pe | pf |
+-----------+-------------------------+-----+--------+-------+--------+--------+-----+----+----+----+----+----+
| 316716435 | 2020-07-22 00:00:00.110 |   4 | MT     | 50.00 | 229.72 | 0.9750 | 160 |  5 |  1 |  2 |  3 | 13 |
| 316716436 | 2020-07-22 00:00:00.420 |   4 | MT     | 50.00 | 229.72 | 0.9750 | 160 |  5 |  1 |  2 |  3 | 13 |
| 316716437 | 2020-07-22 00:00:00.740 |   4 | MT     | 50.00 | 229.73 | 0.9810 | 162 |  5 |  1 |  2 |  3 | 13 |
| 316716438 | 2020-07-22 00:00:01.050 |   4 | MT     | 50.00 | 229.77 | 0.9720 | 159 |  5 |  1 |  2 |  3 | 13 |
| 316716439 | 2020-07-22 00:00:01.380 |   4 | MT     | 50.00 | 229.71 | 0.9760 | 160 |  5 |  1 |  2 |  3 | 13 |
| 316716440 | 2020-07-22 00:00:01.700 |   4 | MT     | 50.00 | 229.64 | 1.0140 | 170 |  5 |  1 |  2 |  3 | 13 |
| 316716441 | 2020-07-22 00:00:02.010 |   4 | MT     | 50.00 | 229.60 | 0.9810 | 161 |  5 |  1 |  2 |  3 | 13 |
| 316716442 | 2020-07-22 00:00:02.330 |   4 | MT     | 50.00 | 229.56 | 0.9950 | 164 |  5 |  1 |  2 |  3 | 13 |
| 316716443 | 2020-07-22 00:00:02.640 |   4 | MT     | 50.00 | 229.63 | 1.0120 | 169 |  5 |  1 |  2 |  3 | 13 |
| 316716444 | 2020-07-22 00:00:02.960 |   4 | MT     | 50.00 | 229.68 | 0.9930 | 163 |  5 |  1 |  2 |  3 | 13 |
| 316716445 | 2020-07-22 00:00:03.270 |   4 | MT     | 50.00 | 229.70 | 0.9960 | 162 |  5 |  1 |  2 |  3 | 13 |
| 316716446 | 2020-07-22 00:00:03.590 |   4 | MT     | 50.00 | 229.79 | 0.9970 | 162 |  5 |  1 |  2 |  3 | 13 |
| 316716447 | 2020-07-22 00:00:03.910 |   4 | MT     | 50.00 | 229.72 | 1.0000 | 162 |  5 |  1 |  2 |  3 | 13 |
| 316716448 | 2020-07-22 00:00:04.220 |   4 | MT     | 50.00 | 229.47 | 1.0090 | 163 |  5 |  1 |  2 |  3 | 13 |
| 316716449 | 2020-07-22 00:00:04.540 |   4 | MT     | 50.00 | 229.64 | 1.0260 | 167 |  5 |  1 |  2 |  3 | 13 |
| 316716450 | 2020-07-22 00:00:04.850 |   4 | MT     | 50.00 | 229.63 | 1.0050 | 164 |  5 |  1 |  2 |  3 | 13 |
| 316716451 | 2020-07-22 00:00:05.170 |   4 | MT     | 50.00 | 229.68 | 0.9860 | 161 |  5 |  1 |  2 |  3 | 13 |
| 316716452 | 2020-07-22 00:00:05.480 |   4 | MT     | 50.00 | 229.71 | 0.9890 | 162 |  5 |  1 |  2 |  3 | 13 |
| 316716453 | 2020-07-22 00:00:05.800 |   4 | MT     | 50.00 | 229.59 | 0.9810 | 160 |  5 |  1 |  2 |  3 | 13 |
| 316716454 | 2020-07-22 00:00:06.110 |   4 | MT     | 50.00 | 229.62 | 0.9780 | 159 |  5 |  1 |  2 |  3 | 13 |
+-----------+-------------------------+-----+--------+-------+--------+--------+-----+----+----+----+----+----+
20 rows in set (0.01 sec)

(measurements)root$


Trenutno imam narejen graf porabe v Wattih. Zanima me in kako bi za prikazano časovno obdobje dobil števec koliko kWh je v tem času porabljenih.
Za to bi verjetno dodal nov panel tipa "stat" oziroma pozneje nameravam narediti "heat map". Ne vem pa kako sestaviti query.
Screenshot na katerem je graf porabe:
grafana.jpg


Torej prosim za pomoč pri izdelavi querya za prikaz kWh v določenem obdobju.
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 29 Jul 2020, 20:13

Group by dodaj (tisti plus) stat in kumulativo (integral).

Zdajle sem na plaži, ko pridem domov ti pošljem primer. Naredilsem za stanje baterije, kjer je osnovni podatek trenuten tok in časovno obdobje. V bistvu ista stvar.
Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 29 Jul 2020, 20:14

Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 29 Jul 2020, 22:14

Mislim, da je tole vse, kar rabiš:
kumulativa.JPG


Tisti math(*1000)je zaradi tega, ker so v grafu mAh, vhodni podatki so pa "SI", torej Ah.
Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 01 Avg 2020, 21:32

Hm nimam moznosti cumulative_sum. Mogoce zato, ker ni influxDB pac pa SQL.
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 01 Avg 2020, 22:57

Kaj pa SQL OVER ? Query lahko tudi čisto ročno vneseš (mimo GUI).
Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a xfce » 03 Avg 2020, 10:48

Kako shranjujete in prikazujete podatke iz raznih IoT senzorjev? Naredite neko preprosto skripto, ki preko http shrani vrednosti in potem prikazujete z grafano direktno iz podatkovne baze?
Uporabniški avatar
xfce
 
Prispevkov: 958
Pridružen: 14 Feb 2015, 12:38
Kraj: Poljane nad Škofjo loko
Zahvalil se je: 132 krat
Prejel zahvalo: 290 krat
Uporabnika povabil: S52O
Število neizkoriščenih povabil: 58

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 03 Avg 2020, 12:02

TTN -> nodered(mqtt) -> InfluxDB -> grafana

Ali
TTN(http integration) -> php -> InfluxDB -> grafana

Gre še enostavneje, samo jquery še nisem uporabljal :)
Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a xfce » 03 Avg 2020, 12:31

s54mtb je napisal/-a:TTN -> nodered(mqtt) -> InfluxDB -> grafana

Ali
TTN(http integration) -> php -> InfluxDB -> grafana

Gre še enostavneje, samo jquery še nisem uporabljal :)


Uf, to pomeni, da imaš nekje server? Ali imaš doma kak server ali pa VPS? Sam razmišljam, da bi si kupil kak VPS za kak par eur/mesec, verjento je bolje in cenješe kot kakr RPi na mizi :)
Uporabniški avatar
xfce
 
Prispevkov: 958
Pridružen: 14 Feb 2015, 12:38
Kraj: Poljane nad Škofjo loko
Zahvalil se je: 132 krat
Prejel zahvalo: 290 krat
Uporabnika povabil: S52O
Število neizkoriščenih povabil: 58

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a s54mtb » 03 Avg 2020, 13:11

Je kar arnesov server, ker IoT v NM postavljajo dijaki v okviru Scidroma in ni namenjeno komercialni rabi ampak izobraževanju mladine.
Always going the extra mile.
https://www.buymeacoffee.com/s54mtb
Uporabniški avatar
s54mtb
 
Prispevkov: 14710
Pridružen: 15 Jan 2015, 01:10
Zahvalil se je: 2183 krat
Prejel zahvalo: 6096 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 255

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a xfce » 13 Avg 2020, 14:31

s54mtb je napisal/-a:Je kar arnesov server, ker IoT v NM postavljajo dijaki v okviru Scidroma in ni namenjeno komercialni rabi ampak izobraževanju mladine.


Kakšna je pa dobra opcija za navadne smrtnike? Verjetno je še najbolj ugodnje, da na spletku zakupim nek VPS strežnik in potem gostim vso to ropotijo tam? Vidim, da bi me nek osnovni VPS prišel cca 60€ letno, kar pa ni slabo. Seveda je opcija tudi RPI, ampak tam je vzdrževanje + UPS.
Uporabniški avatar
xfce
 
Prispevkov: 958
Pridružen: 14 Feb 2015, 12:38
Kraj: Poljane nad Škofjo loko
Zahvalil se je: 132 krat
Prejel zahvalo: 290 krat
Uporabnika povabil: S52O
Število neizkoriščenih povabil: 58

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 13 Avg 2020, 14:38

xfce je napisal/-a:Vidim, da bi me nek osnovni VPS prišel cca 60€ letno, kar pa ni slabo.


https://www.hetzner.com/cloud
Jaz imam CX11 (3,04€ mesecno) + RPI doma.
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a xfce » 14 Avg 2020, 21:36

sundancer mega hvala. Res super stvar. To je edina pot, da bom lahko uporabljal te stvari. Je 2GB RAM-a dovolj? Verjetno ob SSD disku ja?
Uporabniški avatar
xfce
 
Prispevkov: 958
Pridružen: 14 Feb 2015, 12:38
Kraj: Poljane nad Škofjo loko
Zahvalil se je: 132 krat
Prejel zahvalo: 290 krat
Uporabnika povabil: S52O
Število neizkoriščenih povabil: 58

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 14 Avg 2020, 22:05

Mislim, da ne bi smelo bit težav z 2GB.
Če bodo pa lahko povečaš količino RAM na VPS.

Zase imam grafano ter influx na RPI v LXD kontejnerju tako da prilagajam izpis procesov za občutek:

Koda: Izberi vse
raspi4a:~ $ lxc exec GRAFLUX ps fuxaw
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root      1134  0.0  0.0   8280  2324 ?        Rs+  22:00   0:00 ps fuxaw
root         1  0.0  0.0  27384  5780 ?        Ss   Aug12   0:02 /sbin/init
root        41  0.0  0.1  24140  8580 ?        Ss   Aug12   0:02 /lib/systemd/systemd-journald
root        49  0.0  0.0  12348  2192 ?        Ss   Aug12   0:00 /lib/systemd/systemd-udevd
systemd+    63  0.0  0.0  17232  3664 ?        Ss   Aug12   0:02 /lib/systemd/systemd-networkd
root        83  0.0  0.0   5088  2064 ?        Ss   Aug12   0:00 /usr/sbin/cron -f
syslog      84  0.0  0.0  19792  2760 ?        Ssl  Aug12   0:00 /usr/sbin/rsyslogd -n
root        85  0.0  0.0   7680  1988 ?        Ss   Aug12   0:00 /lib/systemd/systemd-logind
root        86  0.0  0.1  28780 11720 ?        Ssl  Aug12   0:00 /usr/bin/python3 /usr/bin/networkd-dispatcher --run-startup-triggers
message+    87  0.0  0.0   5724  2904 ?        Ss   Aug12   0:00 /usr/bin/dbus-daemon --system --address=systemd: --nofork --nopidfile --systemd-activation --syslog-only
systemd+    92  0.0  0.0   8220  3652 ?        Ss   Aug12   0:02 /lib/systemd/systemd-resolved
influxdb    94  6.4  3.3 1473008 266936 ?      Ssl  Aug12 189:15 /usr/bin/influxd -config /etc/influxdb/influxdb.conf
grafana     95  0.1  0.6 936704 55956 ?        Ssl  Aug12   3:18 /usr/sbin/grafana-server --config=/etc/grafana/grafana.ini --pidfile=/var/run/grafana/grafana-server.pid --packaging=deb cfg:default.paths.logs=/v
root       161  0.0  0.0   3920  1524 console  Ss+  Aug12   0:00 /sbin/agetty -o -p -- \u --noclear --keep-baud console 115200,38400,9600 linux


Ubistvu imam kar precej kontejnerjev in približno 1,4GB + 1,7GB porabe RAM-a.

Koda: Izberi vse
raspi4a:~ $ lxc list --fast
+------------+---------+--------------+----------------------+----------+-----------+
|    NAME    |  STATE  | ARCHITECTURE |      CREATED AT      | PROFILES |   TYPE    |
+------------+---------+--------------+----------------------+----------+-----------+
| BACKUPS    | RUNNING | armv7l       | 2020/06/16 04:00 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| DNS-AUTH   | RUNNING | armv7l       | 2020/07/17 09:11 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| GITEA      | RUNNING | armv7l       | 2020/06/06 19:26 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| GRAFLUX    | RUNNING | armv7l       | 2020/07/21 12:35 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| LDAP-A     | RUNNING | armv7l       | 2020/06/06 19:41 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| MAIL       | RUNNING | armv7l       | 2020/06/06 17:23 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| MONIT      | RUNNING | armv7l       | 2020/06/06 17:02 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| NEXTCLOUD  | RUNNING | armv7l       | 2020/06/22 19:14 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| NODERED    | RUNNING | armv7l       | 2020/06/06 13:40 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| PHPMYADMIN | STOPPED | armv7l       | 2020/06/25 09:03 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| PIHOLE     | RUNNING | armv7l       | 2020/06/06 13:35 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| PROXY      | RUNNING | armv7l       | 2020/08/06 12:02 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| SQL-A      | RUNNING | armv7l       | 2020/06/06 19:52 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
| TEST       | RUNNING | armv7l       | 2020/06/06 19:32 UTC | default  | CONTAINER |
+------------+---------+--------------+----------------------+----------+-----------+
raspi4a:~ $ free -m
              total        used        free      shared  buff/cache   available
Mem:           7875        1434        4716          81        1725        6465
Swap:          4095           0        4095
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a xfce » 21 Avg 2020, 14:10

Sedaj pa ne vem, kaj je boljše en RPI ali pa VPS?

Verjento je VPS bolj stabilen na dolgi rok in manj dela.. RPI je zabavna reč, samo je spet 100€ + nekako moraš to vzdrževat.. no, čeprav spet ni toliko dela.. pa tudi RPI ima 1-8 GB RAM-a. Mogoče bi vseeno RPI kupil. Ali pa oboje :)
Uporabniški avatar
xfce
 
Prispevkov: 958
Pridružen: 14 Feb 2015, 12:38
Kraj: Poljane nad Škofjo loko
Zahvalil se je: 132 krat
Prejel zahvalo: 290 krat
Uporabnika povabil: S52O
Število neizkoriščenih povabil: 58

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 21 Avg 2020, 20:48

Dodaj na tehtnico še zasebnost :) VPS je nekje v cloudu, RPI pa doma.
Še vedno lahko backupiraš RPI na VPS. SD kartice so poceni, SSDji pa tudi niso več dragi.
In še če greš mimo vrste dobiš LC Power Zunanje aluminijasto ohišje za trdi disk 6,35 cm (2,5") 25U3 Becrux, USB 3.0 za slabih 11 EUR :)
RPI4 itak podpira USB booting.

Tako narediš strežnik z 8GB RAM-a, ki si ga lahko všiješ v jakno! :)
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 27 Sep 2020, 00:33

sundancer je napisal/-a:Torej prosim za pomoč pri izdelavi querya za prikaz kWh v določenem obdobju.


Po dolgem času sem se tegale spet lotil in mi je uspelo z SQL queryjem nad obstoječimi meritvami. Torej v eni vrstici zabeležen v bazi imamo unikaten ID, timestamp ter trenutno porabo v Wattih (v stolpcu pa, ki mero porabo na fazi takoj za elektro števcem).
Torej za izbrano časovno obdobje delam diff nad timestampom v dveh sosednjih vrsticah ( id ter id + 1 ). Ta "timestmap diff" množim z izmerjeno porabo v drugi vrstici (id +1). Tako dobim rezultat v WattSekundah. Ker gre zadeva čez vse vrstice (v izbranem obdobju) moram te izračunane wattsekunde na koncu sumarizirati. Dobljen rezultat še delim s 3600*1000 da dobim kWh.

Koda: Izberi vse
SELECT
   t2.timestamp AS "time",
   SUM(((UNIX_TIMESTAMP(t2.timestamp) - UNIX_TIMESTAMP(t1.timestamp)) * t2.pa) / 3600000) AS kWh
FROM
   meritve t1
   INNER JOIN
   meritve t2 on t2.id = t1.id + 1
WHERE
   t1.timestamp BETWEEN FROM_UNIXTIME($__from / 1000) AND FROM_UNIXTIME($__to / 1000);


Kot kaže slika je izračun pravilen. Iz porabe se namreč vidi, da je med približno 4:00 in 4:50 uro zjutraj laufal bojler - cca 2kW.
Izračunana poraba v tem obdobju je 1.7kWh. Za lastne potrebe je to dovolj natančen podatek.
Priponke
grafana-poraba-kwh.png
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61

Re: Grafana in izračun porabe v kWh

OdgovorNapisal/-a sundancer » 11 Jun 2023, 22:05

Torej baje se nam obetajo spremembe pri obračunavanju porabljenih kWh.
viri:
https://www.rtvslo.si/gospodarstvo/kako ... eva/668298
https://www.zurnal24.si/pod-streho/varc ... zji-405870

Skladno s tem bodo na hobi projektu za izračun porabljene električne energije potrebne modifikacije.

Na MariaDB bazi imam trenutno takšno shemo:
Koda: Izberi vse
$ DESCRIBE meritve;
+------------+----------------------+------+-----+----------------------+--------------------------------+
| Field      | Type                 | Null | Key | Default              | Extra                          |
+------------+----------------------+------+-----+----------------------+--------------------------------+
| id         | bigint(20) unsigned  | NO   | PRI | NULL                 | auto_increment                 |
| timestamp  | timestamp(3)         | NO   | MUL | CURRENT_TIMESTAMP(3) | on update CURRENT_TIMESTAMP(3) |
| did        | smallint(5) unsigned | NO   | MUL | NULL                 |                                |
| tarifa     | varchar(2)           | NO   | MUL | NULL                 |                                |
| tarifa2024 | tinyint(1) unsigned  | YES  | MUL | 0                    |                                |
| f          | float(4,2)           | NO   |     | NULL                 |                                |
| ua         | float(5,2)           | NO   |     | NULL                 |                                |
| ia         | float(6,4)           | NO   |     | NULL                 |                                |
| wsa        | float(15,5)          | YES  |     | NULL                 |                                |
| pa         | smallint(5) unsigned | NO   |     | NULL                 |                                |
| sa         | smallint(5)          | YES  |     | NULL                 |                                |
| qa         | smallint(5)          | YES  |     | NULL                 |                                |
| phA        | float(5,2)           | YES  |     | NULL                 |                                |
| pwrF       | float(5,3)           | YES  |     | NULL                 |                                |
| upA        | float(5,2)           | YES  |     | NULL                 |                                |
| pb         | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pc         | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pd         | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pe         | smallint(5) unsigned | NO   |     | NULL                 |                                |
| pf         | smallint(5) unsigned | NO   |     | NULL                 |                                |
+------------+----------------------+------+-----+----------------------+--------------------------------+
20 rows in set (0.01 sec)


Koda: Izberi vse
$ SHOW CREATE TABLE meritve\G
*************************** 1. row ***************************
       Table: meritve
Create Table: CREATE TABLE `meritve` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `timestamp` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `did` smallint(5) unsigned NOT NULL,
  `tarifa` varchar(2) NOT NULL,
  `tarifa2024` tinyint(1) unsigned DEFAULT '0',
  `f` float(4,2) NOT NULL,
  `ua` float(5,2) NOT NULL,
  `ia` float(6,4) NOT NULL,
  `wsa` float(15,5) DEFAULT NULL,
  `pa` smallint(5) unsigned NOT NULL,
  `sa` smallint(5) DEFAULT NULL,
  `qa` smallint(5) DEFAULT NULL,
  `phA` float(5,2) DEFAULT NULL,
  `pwrF` float(5,3) DEFAULT NULL,
  `upA` float(5,2) DEFAULT NULL,
  `pb` smallint(5) unsigned NOT NULL,
  `pc` smallint(5) unsigned NOT NULL,
  `pd` smallint(5) unsigned NOT NULL,
  `pe` smallint(5) unsigned NOT NULL,
  `pf` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `timestamp` (`timestamp`),
  KEY `did` (`did`),
  KEY `tarifa` (`tarifa`),
  KEY `tarifa2024` (`tarifa2024`)
) ENGINE=InnoDB AUTO_INCREMENT=7431407 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)




Zadnjih nekaj meritev je takšnih:
Koda: Izberi vse
$ SELECT * FROM meritve WHERE timestamp > NOW() - INTERVAL 1 MINUTE;
+---------+-------------------------+-----+--------+------------+-------+--------+--------+------------+-----+------+------+--------+--------+------+----+----+----+----+----+
| id      | timestamp               | did | tarifa | tarifa2024 | f     | ua     | ia     | wsa        | pa  | sa   | qa   | phA    | pwrF   | upA  | pb | pc | pd | pe | pf |
+---------+-------------------------+-----+--------+------------+-------+--------+--------+------------+-----+------+------+--------+--------+------+----+----+----+----+----+
| 7431392 | 2023-06-11 21:39:58.050 |   4 | MT     |          4 | 49.99 | 228.77 | 1.6510 | 1624.38220 | 305 |  378 |  176 | 153.60 | -0.807 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431393 | 2023-06-11 21:40:03.340 |   4 | MT     |          4 | 49.98 | 228.85 | 1.6480 | 1607.41382 | 305 |  377 |  176 | 151.40 | -0.807 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431394 | 2023-06-11 21:40:08.640 |   4 | MT     |          4 | 49.97 | 229.03 | 1.6570 | 1608.47510 | 307 |  379 |  176 | 152.00 | -0.808 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431395 | 2023-06-11 21:40:13.940 |   4 | MT     |          4 | 49.97 | 228.83 | 1.6470 | 1608.59900 | 306 |  377 |  178 | 148.80 | -0.811 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431396 | 2023-06-11 21:40:19.240 |   4 | MT     |          4 | 49.98 | 228.86 | 1.6330 | 1605.44849 | 302 |  373 |  178 | 151.20 | -0.808 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431397 | 2023-06-11 21:40:24.530 |   4 | MT     |          4 | 49.98 | 228.78 | 1.6660 | 1609.67212 | 312 |  381 |  178 | 150.30 | -0.817 | 0.00 |  5 |  6 |  2 |  5 |  8 |
| 7431398 | 2023-06-11 21:40:29.820 |   4 | MT     |          4 | 49.99 | 228.80 | 1.5520 | 1552.35144 | 278 |  355 |  175 | 147.50 | -0.782 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431399 | 2023-06-11 21:40:35.120 |   4 | MT     |          4 | 49.99 | 228.92 | 1.5350 | 1454.16638 | 274 |  351 |  175 | 146.40 | -0.779 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431400 | 2023-06-11 21:40:40.420 |   4 | MT     |          4 | 49.98 | 228.60 | 1.5230 | 1448.32690 | 271 |  348 |  174 | 149.20 | -0.776 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431401 | 2023-06-11 21:40:45.710 |   4 | MT     |          4 | 49.98 | 228.81 | 1.5470 | 1486.78235 | 276 |  354 |  174 | 148.80 | -0.777 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431402 | 2023-06-11 21:40:51.010 |   4 | MT     |          4 | 50.00 | 228.95 | 1.5960 | 1477.32422 | 290 |  365 |  175 | 145.90 | -0.792 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431403 | 2023-06-11 21:40:56.300 |   4 | MT     |          4 | 49.99 | 229.05 | 1.5470 | 1521.47412 | 278 |  354 |  175 | 149.60 | -0.782 | 0.00 |  5 |  7 |  1 |  5 |  8 |
| 7431404 | 2023-06-11 21:41:01.600 |   4 | MT     |          4 | 49.98 | 229.08 | 1.5810 | 1560.49475 | 286 |  362 |  179 | 151.00 | -0.789 | 0.00 |  5 |  7 |  2 |  5 |  8 |
| 7431405 | 2023-06-11 21:41:06.890 |   4 | MT     |          4 | 49.98 | 228.85 | 1.6100 | 1504.88525 | 294 |  368 |  178 | 151.50 | -0.796 | 0.00 |  5 |  7 |  2 |  5 |  8 |
| 7431406 | 2023-06-11 21:41:12.190 |   4 | MT     |          4 | 49.98 | 228.86 | 1.5470 | 1485.79395 | 276 |  354 |  177 | 148.60 | -0.779 | 0.00 |  5 |  7 |  1 |  5 |  8 |
+---------+-------------------------+-----+--------+------------+-------+--------+--------+------------+-----+------+------+--------+--------+------+----+----+----+----+----+
15 rows in set (0.00 sec)


Polje wsa vsebuje preračunane wattsekunde za obdobje zadnjih 5 sekund.
Kot sem omenil v prejšnjem postu moram ta podatek skozi določeno obdobje akumulirati, da to isto obdobje izračunam wattsekunde in potem pretvorim v kWh.

Podatek v poljih tarifa in tarifa2024 se dodaja dinamično in sicer pomočjo TRIGGER-jev ob vsakem INSERT stavku:
Koda: Izberi vse
$ SHOW TRIGGERS\G
*************************** 1. row ***************************
             Trigger: before_insert_meritve
               Event: INSERT
               Table: meritve
           Statement: BEGIN
  SET new.tarifa = is_mtvt();
  SET new.tarifa2024 = GetTariff2();
END
              Timing: BEFORE
             Created: 2023-06-05 21:39:00.53
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)


Funkciji is_mtvt() ter GetTariff2() sta takšni:
Koda: Izberi vse
$ SHOW CREATE FUNCTION is_mtvt\G
*************************** 1. row ***************************
            Function: is_mtvt
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`dbuser`@`localhost` FUNCTION `is_mtvt`() RETURNS varchar(2) CHARSET latin1
BEGIN

DECLARE tarifa VARCHAR(2);
DECLARE tarifa1 VARCHAR(2);
DECLARE tarifa2 VARCHAR(2);

SET tarifa1 = IF ( EXISTS ( SELECT * FROM delaProstiDnevi WHERE date=CURDATE() ),'MT', 'VT' );
SET tarifa2 = IF( HOUR(NOW())<6 OR HOUR(NOW())>=22 ,'MT', 'VT' );

IF tarifa1 = 'MT' OR WEEKDAY(NOW())>4 THEN SET tarifa='MT';
ELSE SET tarifa = tarifa2;
END IF;

RETURN tarifa;

END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)


Koda: Izberi vse
$ SHOW CREATE FUNCTION GetTariff2\G
*************************** 1. row ***************************
            Function: GetTariff2
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`dbuser`@`localhost` FUNCTION `GetTariff2`() RETURNS int(11)
    DETERMINISTIC
BEGIN
    DECLARE current_hour INT;
    DECLARE current_day_of_week INT;
    DECLARE current_month INT;
    DECLARE is_high_season BOOLEAN;
    DECLARE is_low_season BOOLEAN;
    DECLARE is_working_day BOOLEAN;
    DECLARE is_weekend BOOLEAN;
    DECLARE tariff INT;

    SET current_hour = HOUR(NOW());
    SET current_day_of_week = DAYOFWEEK(NOW());
    SET current_month = MONTH(NOW());

    SET is_high_season = (current_month IN (11, 12, 1, 2));
    SET is_low_season = (current_month IN (3, 4, 5, 6, 7, 8, 9, 10));
    SET is_working_day = (current_day_of_week BETWEEN 2 AND 6);
    SET is_weekend = (current_day_of_week IN (1, 7));

    SET tariff = CASE current_hour
        WHEN 0 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 1 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 2 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 3 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 4 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 5 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 6 THEN IF(is_high_season AND is_working_day, 2, IF(is_low_season AND is_weekend, 4, 3))
        WHEN 7 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 8 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 9 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 10 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 11 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 12 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 13 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 14 THEN IF(is_high_season AND is_working_day, 2, IF(is_low_season AND is_weekend, 4, 3))
        WHEN 15 THEN IF(is_high_season AND is_working_day, 2, IF(is_low_season AND is_weekend, 4, 3))
        WHEN 16 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 17 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 18 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 19 THEN IF(is_high_season AND is_working_day, 1, IF(is_low_season AND is_weekend, 3, 2))
        WHEN 20 THEN IF(is_high_season AND is_working_day, 2, IF(is_low_season AND is_weekend, 4, 3))
        WHEN 21 THEN IF(is_high_season AND is_working_day, 2, IF(is_low_season AND is_weekend, 4, 3))
        WHEN 22 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        WHEN 23 THEN IF(is_high_season AND is_working_day, 3, IF(is_low_season AND is_weekend, 5, 4))
        ELSE 0
    END;

    RETURN tariff;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)


Na ta način lahko za tekoči dan izračunam porabo po tarifnih blokih:
Koda: Izberi vse
$
SELECT t1.tarifa2024 AS tarifa,
 SUM(((UNIX_TIMESTAMP(t2.timestamp) - UNIX_TIMESTAMP(t1.timestamp)) * t2.pa) / 3600000) AS kwh
FROM
 meritve t1 INNER JOIN meritve t2 on t2.id = t1.id + 1
WHERE
 t1.timestamp > CURDATE()
GROUP BY t1.tarifa2024;
+--------+-----------+
| tarifa | kwh       |
+--------+-----------+
|      3 | 5.0971172 |
|      4 | 2.9877690 |
|      5 | 1.3178689 |
+--------+-----------+
3 rows in set (0.09 sec)


Pomožna tabela delaProstiDnevi zahteva vzdrževanje saj je takšna:
Koda: Izberi vse
$ DESCRIBE delaProstiDnevi;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| date        | date         | NO   |     | NULL    |       |
| description | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Koda: Izberi vse
$ SELECT * FROM delaProstiDnevi ORDER BY date LIMIT 20;
+------------+--------------------------------+
| date       | description                    |
+------------+--------------------------------+
| 2014-01-01 | novo leto                      |
| 2015-01-01 | novo leto                      |
| 2015-02-08 | Presernov dan                  |
| 2015-04-06 | velika noc                     |
| 2015-04-27 | dan upora proti okupatorju     |
| 2015-05-01 | praznik dela                   |
| 2015-05-02 | praznik dela                   |
| 2015-06-25 | dan drzavnosti                 |
| 2015-08-15 | marijino vnebovzetje           |
| 2015-10-31 | dan reformacije                |
| 2015-11-01 | dan spomina na mrtve           |
| 2015-12-25 | bozic                          |
| 2015-12-26 | dan samostojnosti in enotnosti |
| 2016-01-01 | novo leto                      |
| 2016-02-08 | Presernov dan                  |
| 2016-03-28 | velika noc                     |
| 2016-04-27 | dan upora proti okupatorju     |
| 2016-05-01 | praznik dela                   |
| 2016-05-02 | praznik dela                   |
| 2016-06-25 | dan drzavnosti                 |
+------------+--------------------------------+
20 rows in set (0.00 sec)


Upam, da še komu pride prav.
Dyslexic man walks into a bra.
Uporabniški avatar
sundancer
 
Prispevkov: 960
Pridružen: 16 Jan 2015, 23:36
Kraj: Domžale
Zahvalil se je: 423 krat
Prejel zahvalo: 515 krat
Uporabnika povabil: Vrtni palček
Število neizkoriščenih povabil: 61


Vrni se na Uporabniški programi

Kdo je na strani

Po forumu brska: 0 registriranih uporabnikov in 1 gost