HOW-TO: postgresql query to show graph by metering date about homeassistant-eloverblik HOT 3 CLOSED

CREATE TABLE eloverblik(
   sensor varchar(100),    
   metering_date date,  
   value numeric,
   insource_date timestamp without time zone,	
   PRIMARY KEY (metering_date, sensor)
);

with CTE as ( 
	select *
	,row_number() over (partition by entity_id, state, metering_date order by time desc) as rn
	from (
			select time,
					entity_id,
				   cast(state as decimal)
					,to_date(replace(cast(json_extract_path(attributes::json,'metering_date') as text),'"',''),'YYYY-MM-DD') as metering_date
					
			from public.ltss
			where entity_id like 'sensor.eloverblik_%'

	) a
)

insert into eloverblik
select entity_id as sensor,
		metering_date,
		state as value,
		time as insource_date

from cte where rn = 1 and entity_id != 'sensor.eloverblik_energy_total'

CREATE OR REPLACE FUNCTION eloverblik_insert()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
	if NEW.entity_id like 'sensor.eloverblik_energy_%' 
	and NEW.entity_id <> 'sensor.eloverblik_energy_total'
	THEN
		 INSERT INTO eloverblik(sensor, metering_date, value, insource_date)
		 VALUES(NEW.entity_id,to_date(replace(cast(json_extract_path(NEW.attributes::json,'metering_date') as text),'"',''),'YYYY-MM-DD'),cast(NEW.state as decimal),now())
		ON CONFLICT (metering_date,sensor) DO UPDATE SET   (value, insource_date) = (cast(NEW.state as decimal),now());
	END IF;

	RETURN NEW;
END;
$$

CREATE TRIGGER eloverblik_insert
  AFTER INSERT
  ON ltss
  FOR EACH ROW
  EXECUTE PROCEDURE eloverblik_insert();