Differences

This shows you the differences between two versions of the page.

--- smart-home:home-assistant [2025/05/03 04:15] – [HA mysql] admin
+++ smart-home:home-assistant [2025/06/01 05:38] (current) – [HA CCTV frigate / scrypted] admin
@@ Line 27: / Line 27: @@
 https://www.homeautomationguy.io/blog/running-frigate-on-proxmox -- frigate install docker on proxmox\\
 https://coral.googlesource.com/webcoral/ - TPU accelerator coral start \\
+<code BASH>
+# get video VOD
+http://localhost:5000/vod/<camera>/start/<start-timestamp>/end/<end-timestamp>/index.m3u8
+</code>
+Full reference config - https://docs.frigate.video/configuration/reference/ \\
+good config example - https://github.com/blakeblackshear/frigate/discussions/12870 \\
 ===== HA. configue =====
@@ Line 42: / Line 49: @@
 SELECT
     states_meta.entity_id,
-    count(*),
+    COUNT(*),
    -- states.state,
-    from_unixtime(min(states.last_updated_ts)) as last_updated_ts,
+    from_unixtime(MIN(IF(states.last_changed_ts IS NULL,
-    from_unixtime(min(IF(states.last_changed_ts IS NULL,
+        states.last_updated_ts,
+        states.last_changed_ts))) AS first_changed_ts,
+    from_unixtime(Max(IF(states.last_changed_ts IS NULL,
         states.last_updated_ts,
         states.last_changed_ts))) AS last_changed_ts
@@ Line 52: / Line 61: @@
         LEFT JOIN
     states_meta ON (states.metadata_id = states_meta.metadata_id)
-group by states_meta.entity_id
+GROUP BY states_meta.entity_id
-order by 2 desc
+ORDER BY 2 DESC
-limit 10;
+LIMIT 10;
 -- size of all tables
@@ Line 60: / Line 69: @@
   ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)`
   FROM information_schema.TABLES
-  ORDER BY (data_length + index_length) DESC;
+  ORDER BY (data_length + index_length) DESC
+  limit 10;
+-- optimize
+OPTIMIZE TABLE homeassistant.states;
 </code>
 ++++