<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div><br/>
Hallo Thomas,<br/></div><div><br/></div><div>ich kommen von Oracle und MS-SQL, arbeite mich also in MYSQL ein.<br/></div><div>Vermutlich weil die Abfrage sehr lange dauert.<br/></div><div><br/></div><div>Aber: Warung ist kein Fehler<br/></div><div><br/></div><div>Gruß<br/></div><div>Michael<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 18:56 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;
}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate {
margin: 0.0cm;
font-size: 8.0pt;
font-family: Tahoma , sans-serif;
}
span.SprechblasentextZchn {
font-family: Tahoma , sans-serif;
}
span.E-MailFormatvorlage19 {
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);">das ist es !</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Ich habe die Abfrage noch etwas eingegrenzt 23:59:5%' hast du eine Idee warum die Warnung kommt ?</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-23 23:59:54 | 1321217.7 | 5 |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| 2013-02-23 23:59:54 | 459350.4 | 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 23:59:57 | 1321217.9 | 5 |</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">| 2013-02-23 23:59:57 | 459350.4 | 6 |</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);">171 rows in set, 1 warning (3 min 13.25 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);">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);">Gruß Thomas</span></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 17:51<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;">teste mal:</span></p></div><div><p class="MsoNormal" style="margin-bottom: 12.0pt;"><span style="font-size: 9.0pt;font-family: Verdana , sans-serif;">select from_unixtime (timestamp/1000),value,channel_id from data where from_unixtime (timestamp/1000) like '2013-% 23:5%';</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" style="margin-bottom: 12.0pt;"><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 17:28 Uhr<br/><b>Von:</b> "Thomas Janz" <<a href="janz.thomas@gmail.com" target="_parent">janz.thomas@gmail.com</a>><br/><b>An:</b> "'volkszaehler.org - users'" <<a href="volkszaehler-users@lists.volkszaehler.org" target="_parent">volkszaehler-users@lists.volkszaehler.org</a>><br/><b>Betreff:</b> Re: [vz-users] SQL Abfrage Zählerstände Tagesende ? </span></p></div><div><div><div><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);">nun kommen wir der Sache näher.</span></p><p class="MsoNormal"><span style="font-size: 11.0pt;font-family: Calibri , sans-serif;color: rgb(31,73,125);">Gibt es noch eine Möglichkeit den letzten Zählerstand des Tages abzufragen, so dass man den kompletten Monat abfragen kann ?</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 für deine Mühe !</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);">Gruß Thomas</span></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;"> <a href="volkszaehler-users-bounces@lists.volkszaehler.org" target="_parent">volkszaehler-users-bounces@lists.volkszaehler.org</a> [<a href="volkszaehler-users-bounces@lists.volkszaehler.org" target="_parent">mailto:volkszaehler-users-bounces@lists.volkszaehler.org</a>] <b>Im Auftrag von </b>"Michael Wußler"<br/><b>Gesendet:</b> Sonntag, 24. Februar 2013 17:12<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;">1) das hier "sum(value)" macht aus allen Werten eine Summe und Gruppen-> daher dauert es so lange, ist bei dir aber nicht notwendig</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;">Ok, neue Abfrage mal so:</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;">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';</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;">Wenn nicht kommt die Zeiten etwas aufmachen.</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;">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" style="margin-bottom: 12.0pt;"><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 14:39 Uhr<br/><b>Von:</b> "Thomas Janz" <<a>janz.thomas@gmail.com</a>><br/><b>An:</b> "'volkszaehler.org - users'" <<a>volkszaehler-users@lists.volkszaehler.org</a>><br/><b>Betreff:</b> Re: [vz-users] SQL Abfrage Zählerstände Tagesende ? </span></p></div><div><div><div><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></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></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></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;"> <a>volkszaehler-users-bounces@lists.volkszaehler.org</a> [<a>mailto:volkszaehler-users-bounces@lists.volkszaehler.org</a>] <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></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><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><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>