PV - daily usage

Grafana Time Series Panel
1

Hi,
I have a photovoltaics and am logging daily values to a mariadb.
I have values for daily production, consumption and selling.
What I want to have is a bar graph with daily production and consumption like in the pic attached, but the values should be displayed, including the daily total (production + consumption). Bedide those there should be a daily bar for selling (at this moment still zero as I can not sell yet).
Is this possible? How?

Screenshot_20230609_232059
Screenshot_20230609_2320591088×341 20.6 KB

2

Welcome

Yes it is possible. Please post table schema and data types?

3 
-- fhem.history definition

CREATE TABLE `history` (
  `TIMESTAMP` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `DEVICE` varchar(64) DEFAULT NULL,
  `TYPE` varchar(64) DEFAULT NULL,
  `EVENT` varchar(512) DEFAULT NULL,
  `READING` varchar(128) DEFAULT NULL,
  `VALUE` varchar(128) DEFAULT NULL,
  `UNIT` varchar(32) DEFAULT NULL,
  KEY `Search_Idx` (`DEVICE`,`READING`,`TIMESTAMP`),
  KEY `date` (`TIMESTAMP`,`DEVICE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
1 Like
4

Also some sample data so we can know what is considered consumption and production etc

5
datum device event reading value
2023-06-09 sungrow_daily taeglich_Daily_PV_Generation: 32.5 taeglich_Daily_PV_Generation 32.5
2023-06-09 sungrow_daily taeglich_Direct_Energy_Consumption: 27.3 taeglich_Direct_Energy_Consumption 27.3
2023-06-08 sungrow_daily taeglich_Daily_PV_Generation: 32.5 taeglich_Daily_PV_Generation 32.5
2023-06-08 sungrow_daily taeglich_Direct_Energy_Consumption: 26.8 taeglich_Direct_Energy_Consumption 26.8
2023-06-07 sungrow_daily taeglich_Daily_PV_Generation: 32.4 taeglich_Daily_PV_Generation 32.4
2023-06-07 sungrow_daily taeglich_Direct_Energy_Consumption: 26.5 taeglich_Direct_Energy_Consumption 26.5
2023-06-06 sungrow_daily taeglich_Daily_PV_Generation: 31.2 taeglich_Daily_PV_Generation 31.2
2023-06-06 sungrow_daily taeglich_Direct_Energy_Consumption: 26.7 taeglich_Direct_Energy_Consumption 26.7

hope the format is ok, unit of value is kWh
taeglich_Daily_PV_Generation is the energy created by my PV, taeglich_Direct_Energy_Consumption is the energy I have to buy.
forgot to mention that the real source for grafana is a tiny view on the table of the posting above (mainly for getting a date field without time):

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `sungrow_daily` AS
select
    cast(`history`.`TIMESTAMP` as date) AS `datum`,
    `history`.`DEVICE` AS `device`,
    `history`.`EVENT` AS `event`,
    `history`.`READING` AS `reading`,
    `history`.`VALUE` AS `value`
from
    `history`
where
    `history`.`DEVICE` = 'sungrow_daily';
1 Like
6

color is a bit wack using bar chart, you could also try time series.

image
image1797×686 38.3 KB

Time Series
image

I used the following query in microsoft sql, should be easy in mysql also
This approach seems a bit tedious but less tedious than pivots.

;with src as 
(
	select '2023-06-09' as datum,	'sungrow_daily' as device,	'taeglich_Daily_PV_Generation: 32.5' as junk,	'taeglich_Daily_PV_Generation' as reading,	32.5 value union
	select '2023-06-09',	'sungrow_daily',	'taeglich_Direct_Energy_Consumption: 27.3',	'taeglich_Direct_Energy_Consumption',	27.3 union
	select '2023-06-08',	'sungrow_daily',	'taeglich_Daily_PV_Generation: 32.5		' , 'taeglich_Daily_PV_Generation',	32.5  union
	select '2023-06-08',	'sungrow_daily',	'taeglich_Direct_Energy_Consumption: 26.8',	'taeglich_Direct_Energy_Consumption',	26.8  union
	select '2023-06-07',	'sungrow_daily',	'taeglich_Daily_PV_Generation: 32.4		' , 'taeglich_Daily_PV_Generation',	32.4  union
	select '2023-06-07',	'sungrow_daily',	'taeglich_Direct_Energy_Consumption: 26.5',	'taeglich_Direct_Energy_Consumption',	26.5  union
	select '2023-06-06',	'sungrow_daily',	'taeglich_Daily_PV_Generation: 31.2		' , 'taeglich_Daily_PV_Generation',	31.2  union
	select '2023-06-06',	'sungrow_daily',	'taeglich_Direct_Energy_Consumption: 26.7',	'taeglich_Direct_Energy_Consumption',	26.7
) 
select gen.device, gen.datum, gen.value as generated, con.value as consumed, gen.value + con.value as total
 from ( select * from src where reading = 'taeglich_Daily_PV_Generation') gen
 join (select * from src where reading = 'taeglich_Direct_Energy_Consumption') con 
   on gen.datum = con.datum 
  and gen.device = con.device
7

Thanks a lot man! Much better now using time series.

Screenshot_20230612_100818
Screenshot_20230612_1008181304×491 50.8 KB

Just two more questions:

  1. (how) is it possible to disable time in the x-axis legend? (12:00 am does not make sense)
  2. would it be possible to stack generated and consumed in the bars and have the total only in tooltip?
    Best regards, thanks again and thanks in advance,
    Otto
8
  1. Use some sort of date format function that strips the tine off