<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hallo Thomas,<br/></div><div><br/></div><div>1) das hier "sum(value)" macht aus allen Werten eine Summe und Gruppen-> daher dauert es so lange, ist bei dir aber nicht notwendig<br/></div><div><br/></div><div>Ok, neue Abfrage mal so:<br/></div><div><br/></div><div>select from_unixtime (timestamp/1000),value,channel_id from data where from_unixtime(substr(timestamp,1,10)) between '2013-02-23 23:59:50' and '2013-02-24 00:00:01';<br/></div><div><br/></div><div>Wenn nicht kommt die Zeiten etwas aufmachen.<br/></div><div><br/></div><div>Gruß<br/></div><div>Michael<br/></div><div><br/>
<br/></div><div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;">
<b>Gesendet:</b> Sonntag, 24. Februar 2013 um 14:39 Uhr<br/>
<b>Von:</b> "Thomas Janz" <janz.thomas@gmail.com><br/>
<b>An:</b> "'volkszaehler.org - users'" <volkszaehler-users@lists.volkszaehler.org><br/>
<b>Betreff:</b> Re: [vz-users] SQL Abfrage Zählerstände Tagesende ?
</div>
<div name="quoted-content">
<!--p.MsoNormal, li.MsoNormal, div.MsoNormal {
margin: 0.0cm;
font-size: 12.0pt;
font-family: "Times New Roman" , serif;
}
a:link, span.MsoHyperlink {
color: blue;
text-decoration: underline;
}
a:visited, span.MsoHyperlinkFollowed {
color: purple;
text-decoration: underline;
}
span.E-MailFormatvorlage17 {
font-family: Calibri , sans-serif;
color: rgb(31,73,125);
}
*.MsoChpDefault {
font-size: 10.0pt;
}
div.WordSection1 {
page: WordSection1;
}
--><div><div class="WordSection1"><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Hallo Michael,</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Danke !</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Hier das Ergebnis nach 17 min !</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">DAY) and now() group by channel_id,date(now());</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">+-------------------------------------+--------------------+------------+</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| from_unixtime (max(timestamp)/1000) | sum(value) | channel_id |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">+-------------------------------------+--------------------+------------+</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| 2013-02-24 14:08:39 | 60375602593.699905 | 5 |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| 2013-02-24 14:08:39 | 21907339276.805634 | 6 |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| 2013-02-23 18:47:43 | 7261394309.398813 | 7 |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">+-------------------------------------+--------------------+------------+</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">3 rows in set (16 min 54.98 sec)</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Aber ich möchte nur einen Schnappschuß der Zählerstände am Tagesende</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">z.B.</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-24 23:59:59 | 12345| 5 </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-24 23:59:59 | 78900| 6 </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-24 23:59:59 | 78900| 7 </span><br/></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-25 23:59:59 | 12350| 5 </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-25 23:59:59 | 78910| 6 </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">2013-02-25 23:59:59 | 78912| 7 </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Vielen Dank</span><br/></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Thomas</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span><br/></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);"> </span></p><div><div style="border: none;border-top: solid rgb(181,196,223) 1.0pt;padding: 3.0pt 0.0cm 0.0cm 0.0cm;"><p class="MsoNormal"><b><span style="font-size: 10.0pt;font-family: Tahoma , sans-serif;">Von:</span></b><span style="font-size: 10.0pt;font-family: Tahoma , sans-serif;"> volkszaehler-users-bounces@lists.volkszaehler.org [mailto:volkszaehler-users-bounces@lists.volkszaehler.org] <b>Im Auftrag von </b>"Michael Wußler"<br/><b>Gesendet:</b> Sonntag, 24. Februar 2013 12:33<br/><b>An:</b> volkszaehler.org - users<br/><b>Betreff:</b> Re: [vz-users] SQL Abfrage Zählerstände Tagesende ?</span></p></div></div><p class="MsoNormal"> </p><div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">Hallo Thomas,</span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"> </span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">für deine Abfrage versuche mal dies:</span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"><br/>select from_unixtime (max(timestamp)/1000), sum(value),channel_id from data where from_unixtime(substr(timestamp,1,10)) between date_sub(now(),INTERVAL 1 DAY) and now() group by channel_id,date(now());</span><br/></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"> </span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">Gruß</span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">Michael</span></p></div><div><p class="MsoNormal"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"> </span></p></div><div style="border: none;border-left: solid rgb(195,217,229) 1.5pt;padding: 0.0cm 0.0cm 0.0cm 8.0pt;margin-left: 7.5pt;margin-top: 7.5pt;margin-right: 3.75pt;margin-bottom: 3.75pt;"><div style="margin-bottom: 7.5pt;"><p class="MsoNormal"><b><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">Gesendet:</span></b><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"> Sonntag, 24. Februar 2013 um 10:46 Uhr<br/><b>Von:</b> "Thomas Janz" <<a href="janz.thomas@gmail.com" target="_parent">janz.thomas@gmail.com</a>><br/><b>An:</b> <a href="volkszaehler-users@lists.volkszaehler.org" target="_parent">volkszaehler-users@lists.volkszaehler.org</a><br/><b>Betreff:</b> [vz-users] SQL Abfrage Zählerstände Tagesende ? </span></p></div><div><div><div><p class="MsoNormal">Hallo,</p><p class="MsoNormal"> </p><p class="MsoNormal">ich hatte hier in der Mailingliste folgende SQL Abfrage gefunden die alle erfassten Zählerstände ausgibt:</p><p class="MsoNormal"> </p><p class="MsoNormal">select from_unixtime (timestamp/1000), value from data;</p><p class="MsoNormal"> </p><p class="MsoNormal">ich möchte die Datenausgabe so modifizieren, das nur noch die Zählerstände am Ende des Tages ausgeben werden.</p><p class="MsoNormal">Kann mir hier jemand weiterhelfen ?</p><p class="MsoNormal">Schön wäre es wenn man die Ausgabe direkt in eine Datei umleiten würde.</p><p class="MsoNormal"> </p><p class="MsoNormal">Danke !</p><p class="MsoNormal"> </p><p class="MsoNormal">Gruß Thomas</p><p class="MsoNormal"> </p></div></div></div></div><div><p class="MsoNormal" style="margin-bottom: 12.0pt;"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;"> </span></p></div></div></div></div>
</div>
</div><div><br/><br/></div></div></body></html>