PV - daily usage

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?


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

-- 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,
1 Like

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

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):

    cast(`history`.`TIMESTAMP` as date) AS `datum`,
    `history`.`DEVICE` AS `device`,
    `history`.`EVENT` AS `event`,
    `history`.`READING` AS `reading`,
    `history`.`VALUE` AS `value`
    `history`.`DEVICE` = 'sungrow_daily';
1 Like

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

Time Series

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

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

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,
  1. Use some sort of date format function that strips the tine off