Created
March 15, 2022 10:37
-
-
Save rufik/e478cc49587894b3a08520ad2c3dbdc9 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- This is Postgresql 11 procedure to fix spikes generated by Huawei Solar integration in Home Assistant | |
| -- https://github.com/Emilv2/huawei_solar/issues/80 | |
| -- Usage: save this proc in HA DB. Then use query like that: | |
| -- CALL fix_spikes('sensor.total_yield_bt2160214111', '2022-03-10 00:00:00') | |
| -- DROP PROCEDURE IF EXISTS public.fix_spikes(character varying, timestamp with time zone); | |
| CREATE OR REPLACE PROCEDURE public.fix_spikes( | |
| sensor_name character varying, | |
| date_from timestamp with time zone) | |
| LANGUAGE 'plpgsql' | |
| AS $BODY$ | |
| DECLARE | |
| threshold FLOAT DEFAULT 10.0; -- threashold for examining spikes | |
| stats_cur CURSOR (p_sensor character varying, p_from timestamp with time zone, p_to timestamp with time zone) for | |
| select id, created, metadata_id, state, sum from "statistics" where metadata_id=(select id from statistics_meta where statistic_id = p_sensor) | |
| and created >= p_from and created <= p_to order by created asc; | |
| -- local vars here | |
| rec RECORD; | |
| nrec RECORD; | |
| diff FLOAT DEFAULT 0.0; | |
| start_date timestamp with time zone; | |
| end_date timestamp with time zone; | |
| BEGIN | |
| end_date = now()::date + time '23:59:59.999999'; -- end timestamp is always today EOD | |
| OPEN stats_cur(sensor_name, date_from, end_date); -- start timestamp is taken from args | |
| LOOP | |
| FETCH NEXT FROM stats_cur INTO rec; | |
| exit when not found; | |
| RAISE NOTICE 'Current record: % % % %', rec.id, rec.created, rec.state, rec.sum; | |
| FETCH NEXT FROM stats_cur INTO nrec; | |
| exit when not found; | |
| RAISE NOTICE ' Next record: % % % %', nrec.id, nrec.created, nrec.state, nrec.sum; | |
| IF (nrec.sum - rec.sum) > threshold THEN | |
| RAISE NOTICE '* Difference is above threshhold: % (% vs %)', diff, rec.sum, nrec.sum; | |
| RAISE NOTICE '* Starting fix from record: %', nrec; | |
| diff = nrec.sum - rec.sum; | |
| start_date = nrec.created; | |
| update "statistics" set sum=(sum - diff) where metadata_id=(select id from statistics_meta where statistic_id = sensor_name ) and created between start_date and end_date; | |
| update "statistics_short_term" set sum=(sum - diff) where metadata_id=(select id from statistics_meta where statistic_id = sensor_name ) and created between start_date and end_date; | |
| RAISE NOTICE '* Done.'; | |
| RETURN; -- only one fix per call | |
| END IF; | |
| FETCH BACKWARD FROM stats_cur INTO rec; | |
| END LOOP; | |
| END | |
| $BODY$; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment