<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 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@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:12.0pt;
font-family:"Times New Roman","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;}
span.E-MailFormatvorlage17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@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='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Hallo Jens,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>ich habe das erste SQL-Statement ausgeführt und hänge das CSV hier an.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Einen Nullwert kann ich da nicht ausmachen. Auch scheint mir die Ausgabe nicht so zu sein wie gewünscht/erwartet? Die Frage ist auch, ob der Ansatz so funktioniert, da ich die Aggregationstabelle ja neu aufgebaut habe und das minütliche Aggregieren aktuell fehlerfrei ist.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Viele Grüße<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Micha<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><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"'> Jens Scheidtmann [mailto:jens.scheidtmann@gmail.com] <br><b>Gesendet:</b> Freitag, 24. März 2023 21:13<br><b>An:</b> volkszaehler.org - users; Michael Hartmann<br><b>Betreff:</b> Re: [vz-users] Aggregation (minute) schlägt fehl<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Kann sein, dass die Mailinglistensoftware die Statements zerhackt hat. Deshalb hier nochmal as Anhang.<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Jens<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>Am Fr., 24. März 2023 um 21:06 Uhr schrieb Jens Scheidtmann <<a href="mailto:jens.scheidtmann@gmail.com">jens.scheidtmann@gmail.com</a>>:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Hallo Michael,<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Um die Datensätze, die den Fehler verursachen, einzugrenzen öffne bitte mysql als root und gebe folgende Befehle ein: <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>$ sudo mysql<o:p></o:p></p></div><div><p class=MsoNormal>> use volkszaehler; <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Dann kopiere dieses Statement hier ein:<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>--- schnipp ---<o:p></o:p></p></div><div><div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>type</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>SUM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.val_by_time) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>as</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> s <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>as</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> t_max <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MIN</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.prev_timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>as</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> t_min<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.timestamp) - </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MIN</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.prev_timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>as</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> delta<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>COUNT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(agg.value) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> count <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ( </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>value</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>value</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> * (</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> - @prev_timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> val_by_time<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>COALESCE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(@prev_timestamp, </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>0</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> prev_timestamp<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , @prev_timestamp := </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>data</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>CROSS JOIN</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> (</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> @prev_timestamp := UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) / </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>, </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"%Y-%m-%d %H:%i:00"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>), INTERVAL </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>minute</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>)) * </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>aggregate</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>WHERE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>type</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> = </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> aggregate.channel_id = </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"3"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> vars <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>WHERE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id = </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"3"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> >= </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>IFNULL</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>((</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) / </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>, </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"%Y-%m-%d %H:%i:00"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>), INTERVAL </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>minute</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>)) * </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>aggregate</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>WHERE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>type</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> = </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> aggregate.channel_id = </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"3"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ), </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>0</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> < UNIX_TIMESTAMP(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>DATE_FORMAT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>NOW</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(), </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"%Y-%m-%d %H:%i:00"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>)) * </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> agg <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>GROUP BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>YEAR</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>/</span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>))<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , DAYOFYEAR(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>/</span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>))<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>HOUR</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>/</span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>))<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>MINUTE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>/</span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>))<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>ORDER BY 1,2,3,4,5<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>INTO</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> OUTFILE </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'/var/lib/mysql/offending.csv'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> FIELDS TERMINATED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>','</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ENCLOSED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'"'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> LINES TERMINATED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'\n'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ;<o:p></o:p></span></p></div></div></div><div><p class=MsoNormal>--- schnapp ---<o:p></o:p></p></div><p class=MsoNormal>Wenn Du uns die Werte nicht verraten möchtest, kannst Du "value" am Anfang durch "0" ersetzen.<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Zeige uns bitte die Tabelle aus /var/lib/mysql/offending.csv! <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Dort musst Du dann die Zeile finden, die in der vorletzten Spalte eine "0" hat. <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>In den Zeilen davor und danach findest Du die timestamps, die Du im folgenden Statement für ****MIN**** und ****MAX**** einsetzen musst:<o:p></o:p></p></div><div><p class=MsoNormal>--- schnipp ---<o:p></o:p></p></div><div><div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>value</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>value</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> * (</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> - @prev_timestamp) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> val_by_time<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , </span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>COALESCE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(@prev_timestamp, </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>0</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> prev_timestamp<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> , @prev_timestamp := </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>data</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>CROSS JOIN</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> (</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>SELECT</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> @prev_timestamp := UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(</span><span style='font-size:10.5pt;font-family:Consolas;color:#DCDCAA'>MAX</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>(</span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) / </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>, </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"%Y-%m-%d %H:%i:00"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>), INTERVAL </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>minute</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>)) * </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1000</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>FROM</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>aggregate</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>WHERE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>type</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> = </span><span style='font-size:10.5pt;font-family:Consolas;color:#B5CEA8'>1</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> aggregate.channel_id = </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"3"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'>) </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AS</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> vars <o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>WHERE</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> channel_id = </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>"3"</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> >= ****MIN****<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>AND</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>timestamp</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> < ****MAX****<o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>INTO</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> OUTFILE </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'/var/lib/mysql/rawdata.csv'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> FIELDS TERMINATED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>','</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ENCLOSED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'"'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> LINES TERMINATED </span><span style='font-size:10.5pt;font-family:Consolas;color:#569CD6'>BY</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> </span><span style='font-size:10.5pt;font-family:Consolas;color:#CE9178'>'\n'</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'> ;<o:p></o:p></span></p></div></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-family:"Arial","sans-serif";color:#222222;background:white'>--- schnapp --- </span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-family:"Arial","sans-serif";color:#222222;background:white'>Wenn Du uns die Werte nicht verraten möchtest, kannst Du die zwei "value" am Anfang durch "0" ersetzen.</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-family:"Arial","sans-serif";color:#222222;background:white'>Auch diese Datei solltest Du anhängen.</span><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p></o:p></span></p></div><div><p class=MsoNormal style='line-height:14.25pt;background:#1E1E1E'><span style='font-size:10.5pt;font-family:Consolas;color:#D4D4D4'><o:p> </o:p></span></p></div></div></div></div></blockquote></div></div></body></html>