<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Segoe UI Emoji";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Vorformatiert Zchn";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.HTMLVorformatiertZchn
{mso-style-name:"HTML Vorformatiert Zchn";
mso-style-priority:99;
mso-style-link:"HTML Vorformatiert";
font-family:"Courier New";}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
p.HTMLPreformatted, li.HTMLPreformatted, div.HTMLPreformatted
{mso-style-name:"HTML Preformatted";
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.E-MailFormatvorlage22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage23
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage24
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage25
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.E-MailFormatvorlage26
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage27
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage28
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.E-MailFormatvorlage29
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.E-MailFormatvorlage30
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.E-MailFormatvorlage32
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DE link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Hallo Alex,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:35.4pt'><span style='color:#1F497D'>Wenn Du keine Aggregation benutzt ist gegen diese Art des Aufräumens m.E. nichts einzuwenden. Allerdings muss ich an dieser Stelle auch nochmal klarstellen, dass ich keiner der VZ-Entwickler bin, sondern nur Benutzer mit recht guten Unix-Kenntnissen, aber eher rudimentären Datenbank- und Programmierkenntnissen. Also auf Deine eigene Verantwortung.<o:p></o:p></span></p><p class=MsoNormal>Das sowieso <span style='font-family:"Segoe UI Emoji",sans-serif'>😉</span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-left:35.4pt'><span style='color:#1F497D'>Bzgl. der Aggregation – schau doch nochmal ob eine Tabelle volkszaehler.aggregate existiert, und falls ja, welchen Inhalt sie hat. <o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Auf dem Testsystem ist eine Datenbank aggregate vorhanden, aber leer. Muss ich mir im Live-System angucken und prüfen.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-left:35.4pt'><span style='color:#1F497D'>Du hattest glaube ich geschrieben, dass es um eine Handvoll Sensoren geht, die alle 5 Minuten loggen, oder habe ich da was falsches in Erinnerung?<o:p></o:p></span></p><p class=MsoNormal>Korreckt. 8 Sensoren jeweils alle 5 Minuten<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-left:35.4pt'><span style='color:#1F497D'>Diese paar Einträge der letzten 24 Stunden einmal täglich auszuwerten und selektiv zu löschen sollte die Datenbank locker abkönnen. Wenn Du alle Anfragen in einem SQL-Skript zusammenfasst, werden sie ohnehin seriell abgearbeitet. <o:p></o:p></span></p><p class=MsoNormal>OK Danke (seriell war ich unsicher) <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-left:35.4pt'><span style='color:#1F497D'>Mehrere Abfragen machen aber nur dann Sinn, wenn Du wie in Deinem letzten Beispiel unterschiedliche Grenzwerte setzen willst.</span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='text-indent:35.4pt'><span style='color:#1F497D'>Mehrere gleichartige Sensoren könntest Du auch einfach mittels <o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:35.4pt;text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>DELETE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`data`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>WHERE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`channel_id`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>IN</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> (</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>x,y,z</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>))<o:p></o:p></span></p><p class=MsoNormal style='margin-left:35.4pt;text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:maroon'>timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>unix_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>current_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>24</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>1000<o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt;text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(<b>VALUE</b></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'><-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>5</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>or</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>VALUE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>40</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>);<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span style='color:#1F497D'>zusammenfassen. Das wäre dann für die DB noch weniger Last.<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Danke Dir. Simuliere und teste die Befehle in PHPMYADMIN. Sonst kommen bei mir zu viele Tippfehler da rein <span style='font-family:"Segoe UI Emoji",sans-serif'>😊</span> <o:p></o:p></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal>Noch ein Punkt, da ich verschiedene Antworten finde im Netz. Wo sollte ich das Script speichern?<o:p></o:p></p><p class=MsoNormal>Hatte an das home Verzeichnis gedacht und dort nen Ordner scripte erstellen:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>* */1 * * * /home/scripte/sql.sh<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Danke und Grüße,<o:p></o:p></p><p class=MsoNormal>Ralf<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma",sans-serif'> volkszaehler-users [<a href="mailto:volkszaehler-users-bounces@demo.volkszaehler.org">mailto:volks<span lang=EN-US>zaehler-users-bounces@demo.volkszaehler.org</span></a></span><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>] <b>On Behalf Of </b>Ralf Wismann<br><b>Sent:</b> Tuesday, January 28, 2020 10:53 AM<br><b>To:</b> 'volkszaehler.org - users'<br><b>Subject:</b> Re: [vz-users] 1-Wire Messwerte Filtern/Löschen<o:p></o:p></span></p></div></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal>Hallo Alex,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>vielen Dank! Habe mir ein Testsystem aufgebaut auf Basis eines Backups. Das laufende System stammt aus Januar 2018.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Die crontabs unter dem user pi ist leer bzw. ist noch anzulegen. Entsprechend wird derzeit keine Aggregation durchgeführt.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Allgemein ist mir die Lösung per SQL schon ganz recht, da sie für mich recht greifbar ist da die SQL Datenbank des Volkszählers eh per Microsoft Query in Excel ausgelesen wird. Somit ist der Wiedereinstieg recht flott wieder aufgefrischt und die Handhabung „bekannter“.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Bzgl. der verschieden Kanäle sollten 2 oder mehr Befehle funktionieren, da Microsoft Query auch mehrere Anfrage absetzt….allerdings nur SELECT/lesend.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Was ich gerne machen würde z.B. für Innentemperatur und Heizungvorlauf:<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>DELETE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`data`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>WHERE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>((</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`channel_id`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>=</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>11</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:maroon'>timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>unix_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>current_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>24</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>1000<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(<b>VALUE</b></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'><-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>5</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>or</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>VALUE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>40</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>);<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>DELETE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`data`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>WHERE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>((</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`channel_id`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>=</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>12</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:maroon'>timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>unix_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>current_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>24</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>1000<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(<b>VALUE</b></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'><15</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>or</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>VALUE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>);<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Wenn es unproblematisch ist, warum dann nicht alle 8 Temperaturen im jetzigen System so „bereinigen/prüfen“?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Danke und Viele Grüße<o:p></o:p></p><p class=MsoNormal>Ralf<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b>Von:</b> volkszaehler-users <<a href="mailto:volkszaehler-users-bounces@demo.volkszaehler.org">volkszaehler-users-bounces@demo.volkszaehler.org</a>> <b>Im Auftrag von </b><a href="mailto:rgb@nord-com.net">rgb@nord-com.net</a><br><b>Gesendet:</b> Montag, 27. Januar 2020 11:01<br><b>An:</b> 'volkszaehler.org - users' <<a href="mailto:volkszaehler-users@demo.volkszaehler.org">volkszaehler-users@demo.volkszaehler.org</a>><br><b>Betreff:</b> Re: [vz-users] 1-Wire Messwerte Filtern/Löschen<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Hallo Ralf,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Bzgl. Aggregation schaue mal in die crontabs… standardmässig steht in der crontab des Benutzers pi in etwa so etwas drin:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>pi@avz:~:(3)> crontab -l | grep aggregate<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>*/10 * * * * php /var/www/volkszaehler.org/bin/aggregate run -m delta -l minute >/dev/null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>1 * * * * php /var/www/volkszaehler.org/bin/aggregate run -m delta -l hour >/dev/null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New"'>0 1 * * * php /var/www/volkszaehler.org/bin/aggregate run -m delta -l day >/dev/null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Dann hast Du die Aggregation aktiviert. Bzgl. mehrerer Kanäle/Sensoren – die kannst Du mit einem Befehl abfragen/löschen:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>DELETE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`data`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>WHERE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>((</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`channel_id`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>=</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>x</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>OR</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>channel_id`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>=</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>y</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>))</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'><o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:maroon'>timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>unix_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:navy'>current_timestamp</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>7</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>24</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>)*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>1000<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>(<b>VALUE</b></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'><-</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>5</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>or</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>VALUE</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:purple'>40</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'>Die channel_id stehen in der entities Tabelle.<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>SELECT</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:olive'>`entities`</span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'>;<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:9.0pt;font-family:"Courier New";color:blue'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'>Diese zeigt u.a. die Zuordnung von UUID und (channel_)id. Probiere die Abfragen aber auf jeden Fall aus, bevor Du ans Löschen gehst, dazu das „DELETE FROM“ durch ein „SELECT * FROM“ ersetzen, dann siehst Du genau die Zeilen, die Du ansonsten löschen würdest.<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'>Diese Zuordnung hättest Du automatisch, wenn Du die Abfragen über die Middleware machst, ich glaube das wäre wirklich der sauberere Weg, zumal wenn Du Dir mit SQL nicht sicher bist.<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'>Gruss,<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='color:#1F497D'>Alex</span><span style='font-size:10.0pt;color:blue'><o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma",sans-serif'> volkszaehler-users [<a href="mailto:volkszaehler-users-bounces@demo.volkszaehler.org">mailto:volkszaehler-users-bounces@demo.volkszaehler.org</a>] <b>On Behalf Of </b>Ralf Wismann<br><b>Sent:</b> Monday, </span><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>January 27, 2020 10:18 AM<br><b>To:</b> 'volkszaehler.org - users'<br><b>Subject:</b> Re: [vz-users] 1-Wire Messwerte Filtern/Löschen<o:p></o:p></span></p></div></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal>Hallo<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>vielen Dank Alex und Thomas!<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Muss mir erst noch ein Backup/Testsystem aufsetzen und dann lass ich es mal laufen.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Bzgl. Aggregation bin ich mir unsicher. In der vzlogger.conf ist hinterlegt, dass die Temperaturen alle 10 Minuten gemessen werden. Eine weitere/zusätzliche Verdichtung der Daten ist von mir nicht aktiviert. Somit nicht vorhanden? Du meinst z.B. dieses vzcompress oder so ähnlich wie es im Wiki steht?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Noch eine Frage vom Einsteiger dazu: Ich habe ja mehrere Temperaturen und darunter machen 2,3 schon mal diese Probleme. Also würde ich jetzt mehre DELETE Befehle in das SQL Script einfügen wollen.<o:p></o:p></p><p class=MsoNormal>Die Zeitspanne der Daten wollte ich möglichst „sinnvoll“ zum Zyklus des Crontab wählen, nichtsdestotrotz glaube ich, dass eine SQL-Abfrage/Befehl noch nicht fertig sein könnte, wenn der zweite Befehl ausgeführt wird. Gibt das irgendwelche Probleme (Überlast, undefinierte Ergebnis, Verworfene Befehle/Daten, etc.) oder kann eine/die Datenbank das handeln?<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Vielen Dank<o:p></o:p></p><p class=MsoNormal>Ralf<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>P.S.: Stelle mit Freude fest, dass die Wichtigkeit der Schreib/Zitierweise in der Mailingliste an Priorität verloren hat. Das war für mich als Outlook-Nutzer immer ein großer künstlicher Krampf es so hinzubiegen. Hoffe auch so können wir uns gut Verständigen. Danke! <o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b>Von:</b> volkszaehler-users <<a href="mailto:volkszaehler-users-bounces@demo.volkszaehler.org">volkszaehler-users-bounces@demo.volkszaehler.org</a>> <b>Im Auftrag von </b><a href="mailto:rgb@nord-com.net">rgb@nord-com.net</a><br><b>Gesendet:</b> Sonntag, 26. Januar 2020 22:50<br><b>An:</b> 'volkszaehler.org - users' <<a href="mailto:volkszaehler-users@demo.volkszaehler.org">volkszaehler-users@demo.volkszaehler.org</a>><br><b>Betreff:</b> Re: [vz-users] 1-Wire Messwerte Filtern/Löschen<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'>Hallo Ralf,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>die Abfrage müsste so aussehen<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>DELETE</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>FROM</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:olive'>`volkszaehler`</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>.</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:olive'>`data`</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>WHERE</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:olive'>channel_id</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>=</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>xxxxxx<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:maroon'>timestamp</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'><</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:navy'>unix_timestamp</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>(</span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:navy'>current_timestamp</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>)-</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>7</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>24</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>*</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>60</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>)*</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>1000<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>AND</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>(<b>VALUE</b></span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'><-</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>5</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>or</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>VALUE</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>></span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:purple'>40</span><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New";color:blue'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Und das SQL Script darf dann nur den SQL Code enthalten aber nicht den Aufruf – sprich Du speicherst nur z.B. den o.g. Code in eine Datei und trägst in die Crontab dann folgendes ein:<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>mysql –ubenutzername -ppasswort volkszaehler < /path/to/sql.script<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Ich würde für die DB auch nicht den Root-Account verwenden, der Standard User „vz“ (Password „demo“ falls es nicht geändert wurde) sollte reichen…<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Aber: Ich sehe da ein weiteres Problem – wenn bei dem genannten Kanal die Datenaggregation aktiviert ist, hast Du die falschen Werte da schon mit drin bevor Du sie löschen kannst. Und da dabei verschiedene Werte miteinander verrechnet werden, lässt sich das dann auch nicht mehr so leicht per Schema s.o. entfernen.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Vielleicht kann das die Middleware-Anfrage, die Andreas empfohlen hat… ich weiss es nicht? Auf jeden Fall kannst Du auch Middleware Aufrufe z.B. über wget oder curl automatisch laufen lassen.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Ansonsten bliebe Dir nur – entweder die Aggregation für den betroffenen Kanal ausschalten oder sie nach dem Lauf des Skriptes neu aufbauen – sinnvollerweise nur wenn auch wirklich falsche Werte aufgetreten sind. Sprich vor dem DELETE nochmal einen SELECT laufen lassen und den Rückgabewert abfragen.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Gruss,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Alex<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>From:</span></b><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'> volkszaehler-users [</span><a href="mailto:volkszaehler-users-bounces@demo.volkszaehler.org"><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>mailto:volkszaehler-users-bounces@demo.volkszaehler.org</span></a><span lang=EN-US style='font-size:10.0pt;font-family:"Tahoma",sans-serif'>] <b>On Behalf Of </b>USER VZ<br><b>Sent:</b> Sunday, January 26, 2020 6:40 PM<br><b>To:</b> volkszaehler.org - users<br><b>Subject:</b> Re: [vz-users] 1-Wire Messwerte Filtern/Löschen<o:p></o:p></span></p></div></div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Der SQL- Befehl wird nicht funktionieren. <o:p></o:p></span></p><div><p class=MsoNormal><span lang=EN-US>Die Abfrage nach Value muss mit ODER verknüpft werden. Z.b.:<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>DELETE FROM `volkszaehler` WHERE and channel_id = 12 AND timestamp< (unix_timestamp(current_timestamp)-7*24*60*60)*1000 AND (value >-5 OR value <40)</span><span lang=EN-US><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Ob der mysql Syntax stimmt kann ich sagen, aber die Richtung würde ich auch gehen.<o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Thomas <o:p></o:p></span></p><div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US>Mail: </span><a href="mailto:thomas@thhoe.de"><span lang=EN-US>thomas@thhoe.de</span></a><span lang=EN-US><o:p></o:p></span></p></div><div><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p></div></div><div><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal style='margin-bottom:12.0pt'><span lang=EN-US>Am 26.01.2020 um 16:28 schrieb Ralf Wismann <</span><a href="mailto:ralf.wismann@gmx.de"><span lang=EN-US>ralf.wismann@gmx.de</span></a><span lang=EN-US>>:<o:p></o:p></span></p></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal><span lang=EN-US></span><span lang=EN-US style='font-size:12.0pt;font-family:"Times New Roman",serif'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Hallo<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span lang=EN-US>Am Sonntag, 26. Januar 2020 15:04 schrieb Anrears Götz:<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:35.4pt'><span lang=EN-US>„Die Middleware kann DELETE auch übers Api, dabei lässt sich auch ein Wertfilter angeben. Benutzung lässt sich auf der ML recherchieren. „<o:p></o:p></span></p><div><p class=MsoNormal><span lang=EN-US>Danke Andreas. Schau ich mir auch gerne an. Durch den manuellen Eingriff scheint mir das hier weniger gut, da es nicht mein Volkszähler ist und der Besitzer nicht selber an der Datenbank arbeiten möchte. Ich habe / möchte auch nicht unbedingt manuell dort regelmäßig aktiv sein </span><span lang=EN-US style='font-family:"Segoe UI Emoji",sans-serif'>😉</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal style='text-indent:35.4pt'><span lang=EN-US>Am 26.01.2020 um 14:25 schrieb Rupert Schöttler <</span><a href="mailto:rupert.schoettler@gmx.de"><span lang=EN-US>rupert.schoettler@gmx.de</span></a><span lang=EN-US>>:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal style='margin-left:35.4pt'><span lang=EN-US>„Dein vergleichsweise einfaches Fehlerbild, nämlich 85°C = Fehler, kann man einfach auf der Datenbank reparieren: <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal style='margin-left:35.4pt'><span lang=EN-US>DELETE FROM `data` WHERE (channel_id IN (<Kanalliste>)) AND (value = 85). Diesen Befehl gelegentlich in phpMyAdmin absetzen oder z.B. 1x tgl. per cron laufen lassen und ausgeputzt ist's. Aber Achtung mit der Syntax und den Kanalnummern: Was weg ist ist weg! ;-)“<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Danke Rupert. Hatte ich vermutet, wenn es keine Standardfehlerkorrekturoption gibt.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Da ich so etwas noch nicht gemacht habe. Bitte um kurzen Quercheck.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Also ich lege ein Bash-Script an „reinigung.sh“<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Tippe dort rein:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>#!/bin/bash</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>mysql --user=root -praspberry</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>DELETE FROM `volkszaehler` WHERE and channel_id = 12 AND timestamp< (unix_timestamp(current_timestamp)-7*24*60*60)*1000 AND value >-5 AND value <40;</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>Dies zB soll Werte für eine Innenraumtemperatur (Kanal 12) kleiner -5Grad and Größer 40 Grad der letzten 7 Tage.</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>Dieses Bash Script lasse ich Täglich um 3h laufen mit cron:</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>00 3 * * * root /verzeichnis/script >/dev/null 2>&1</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>Geht das in die Richtung?</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>Danke und Grüße</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'>Ralf</span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US style='font-size:10.0pt;font-family:"Courier New"'> </span><span lang=EN-US><o:p></o:p></span></p></div></div></blockquote></div></div></div></div></div></body></html>