Add option to only update when value changed (-> save to history)

[gsantner]

I've been trying this script since yesterday and it's about 3MB data / day.
The published value for many topics stay the same for quite some time however.
Thus would be very cool to have an opt-in option/flag that only runs the insert/update statement when the value is different to the existing one for a given topic (ignore timestamp in this case). This results in no automated trigger for copy to history.

Example: Some service publishes data every minute via mqtt , however data rarely change. I'm not interested in the history whether or not it was 30 times the same a row, only the change is relevant for history.

Note: I know it's possible to set specific topics, but I want all topics to history (#)

[curzon01]

possible:

as historical data creation is not functional part of the python daemon, I suggest to handle this on SQL side too.

Remove existing trigger mqtt_after_update with the following mqtt_before_update trigger:

DROP TRIGGER IF EXISTS `mqtt_after_update`;

DROP TRIGGER IF EXISTS `mqtt_before_update`;
DELIMITER //
CREATE TRIGGER `mqtt_before_update` BEFORE UPDATE ON `mqtt` FOR EACH ROW BEGIN
    IF ((SELECT value FROM mqtt WHERE id=NEW.id)!=NEW.value) THEN
        INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
    END IF;
END//
DELIMITER ;

This will insert data into history table only if the current value is different to the last published

[curzon01]

An alternate way with detailed control for each topic is

1. add an additonal field history

ALTER TABLE `mqtt`
	ADD COLUMN `history` TINYINT(4) NOT NULL DEFAULT '1' AFTER `active`;

2. insert values (check in triggers) only if history is set, so change the existing original triggers like:

DROP TRIGGER IF EXISTS `mqtt_after_insert`;
DROP TRIGGER IF EXISTS `mqtt_after_update`;
DELIMITER //
CREATE TRIGGER `mqtt_after_insert` AFTER INSERT ON `mqtt` FOR EACH ROW BEGIN
	IF NEW.history!=0 THEN
		INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
	END IF;
END//
CREATE TRIGGER `mqtt_after_update` AFTER UPDATE ON `mqtt` FOR EACH ROW BEGIN
	IF NEW.history!=0 THEN
		INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
	END IF;
END//
DELIMITER ;

This solution allows you to fine control using the history column of mqtt table which topic should be in history and which not.

[gsantner]

Cool thanks for the statements! I think they would also fit good for README?

I prefer the history for all, and no exclusions, so nr 1 😃 .

EDIT: Actually, I think it would make sense to add the "history" flag in general (i.e. to the default README statement), and have the trigger "changed only" optionally as "alternative trigger setup"?

[gsantner]

I've created a PR to add the history flag to mqtt mysql table on README.

If you are interested, after that PR we could upgrade it to have both suggestions coexisting in DB and in trigger: enable_history and enable_history_changes_only

[gsantner]

btw, what I want todo with the data that this script produces: create simple dashboard for e.g. temperature history and turn heating on/off.
I'm running openhab since quite some time but it's quite unstable and resource consuming while I only need a very small faction of features. ... as data anyway coming from mqtt this should be enough to realize it in the simplest fashion