Torej baje se nam obetajo spremembe pri obračunavanju porabljenih kWh.
viri:
https://www.rtvslo.si/gospodarstvo/kako ... eva/668298https://www.zurnal24.si/pod-streho/varc ... zji-405870Skladno 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.