SHOW:
|
|
- or go back to the newest paste.
| 1 | DROP EVENT IF EXISTS `Archive Eloverblik states`; | |
| 2 | CREATE DEFINER=`[user]`@`[server].%` EVENT `Archive Eloverblik states` ON SCHEDULE EVERY 24 HOUR STARTS '2022-01-01 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO | |
| 3 | ||
| 4 | INSERT INTO archive (entity_id,event_date,state) | |
| 5 | SELECT DISTINCT | |
| 6 | A.entity_id, | |
| 7 | SUBSTR(attributes,LOCATE('Metering date":',attributes)+16,10) AS Dato,
| |
| 8 | A.state | |
| 9 | FROM | |
| 10 | states A | |
| 11 | LEFT JOIN | |
| 12 | archive B on A.entity_id=B.entity_id AND SUBSTR(A.attributes,LOCATE('Metering date":',A.attributes)+16,10)=B.event_date
| |
| 13 | WHERE | |
| 14 | A.entity_id like 'sensor.eloverblik%' | |
| 15 | AND A.state not in ('unknown','unavailable','')
| |
| 16 | AND B.entity_id is Null; | |
| 17 | ||
| 18 | SET GLOBAL event_scheduler = ON; |
