<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-15">
</head>
<body text="#000000" bgcolor="#FFFFFF">
Hallo zusammen,<br>
<br>
ich glaube es gab schon mal einen Thread zur Lösung meines aktuellen
"Problems": ich suche nach einer bequemen Echtzeit-Visualisierung
einiger selbstgestrickter SQL-Abfragen.<br>
<br>
Beispiel: die Quelltemperatur meiner Wärmepumpe schwankt täglich. Je
länger die Pumpe in Betrieb ist, desto mehr kühlt die Sonde aus.
Nach einer Pause "regeneriert" sich das ganze. Zur Überwachung logge
ich die Eintrittstemperatur per 1wire. Nun interessiert mich die
Minimaltemperatur, und zwar tagesweise und mit 4 verschiedenen
channels (3 versch. Eintrittsstränge, 1 gesamter).<br>
<br>
Dafür hab ich eine eigene Query:<br>
<!--?xml version="1.0" encoding="UTF-8"?-->
<pre><code><span class="sql1-reservedword">SELECT</span><span class="sql1-space"> </span><span class="sql1-identifier">d</span><span class="sql1-symbol">.</span><span class="sql1-identifier">channel_id</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-identifier">p</span><span class="sql1-symbol">.</span><span class="sql1-identifier">value</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-function">from_unixtime</span><span class="sql1-symbol">(</span><span class="sql1-datatype">timestamp</span><span class="sql1-symbol">/</span><span class="sql1-number">1000</span><span class="sql1-symbol">),</span><span class="sql1-space"> </span><span class="sql1-function">min</span><span class="sql1-symbol">(</span><span class="sql1-identifier">d</span><span class="sql1-symbol">.</span><span class="sql1-identifier">value</span><span class="sql1-symbol">)
</span><span class="sql1-reservedword">FROM</span><span class="sql1-space"> </span><span class="sql1-reservedword">data</span><span class="sql1-space"> </span><span class="sql1-identifier">d</span><span class="sql1-space"> </span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-tablename">properties</span><span class="sql1-space"> </span><span class="sql1-identifier">p
</span><span class="sql1-reservedword">where</span><span class="sql1-space"> </span><span class="sql1-identifier">p</span><span class="sql1-symbol">.</span><span class="sql1-identifier">entity_id</span><span class="sql1-symbol">=</span><span class="sql1-identifier">d</span><span class="sql1-symbol">.</span><span class="sql1-identifier">channel_id</span><span class="sql1-space"> </span><span class="sql1-reservedword">and</span><span class="sql1-space"> </span><span class="sql1-identifier">p</span><span class="sql1-symbol">.</span><span class="sql1-identifier">pkey</span><span class="sql1-symbol">=</span><span class="sql1-delimitedidentifier">"title"</span><span class="sql1-space"> </span><span class="sql1-reservedword">and
</span><span class="sql1-symbol">(</span><span class="sql1-identifier">channel_id</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-number">2</span><span class="sql1-space"> </span><span class="sql1-reservedword">or</span><span class="sql1-space"> </span><span class="sql1-identifier">channel_id</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-number">3</span><span class="sql1-space"> </span><span class="sql1-reservedword">or</span><span class="sql1-space"> </span><span class="sql1-identifier">channel_id</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-space"> </span><span class="sql1-number">6</span><span class="sql1-space"> </span><span class="sql1-reservedword">or</span><span class="sql1-space"> </span><span class="sql1-identifier">channel_id</span><span class="sql1-space"> </span><span class="sql1-symbol">=</span><span class="sql1-number">7</span><span clas
s="sql1-symbol">)
</span><span class="sql1-reservedword">GROUP</span><span class="sql1-space"> </span><span class="sql1-reservedword">BY</span><span class="sql1-space"> </span><span class="sql1-identifier">channel_id</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-datatype">DATE</span><span class="sql1-symbol">(</span><span class="sql1-function">from_unixtime</span><span class="sql1-symbol">(</span><span class="sql1-datatype">timestamp</span><span class="sql1-symbol">/</span><span class="sql1-number">1000</span><span class="sql1-symbol">))
</span><span class="sql1-reservedword">ORDER</span><span class="sql1-space"> </span><span class="sql1-reservedword">BY</span><span class="sql1-space"> </span><span class="sql1-identifier">d</span><span class="sql1-symbol">.</span><span class="sql1-identifier">channel_id</span><span class="sql1-symbol">,</span><span class="sql1-space"> </span><span class="sql1-function">from_unixtime</span><span class="sql1-symbol">(</span><span class="sql1-datatype">timestamp</span><span class="sql1-symbol">/</span><span class="sql1-number">1000</span><span class="sql1-symbol">)</span><span class="sql1-space"> </span><span class="sql1-reservedword">ASC
</span></code></pre>
<title>Untitled</title>
<meta http-equiv="Content-Type" content="text/html;
charset=ISO-8859-15">
<meta name="generator" content="SynEdit HTML exporter">
<style type="text/css">
<!--
body { color: #000000; background-color: #FFFFFF; }
.sql1-comment { color: #808080; font-style: italic; }
.sql1-conditionalcomment { color: #808080; font-style: italic; }
.sql1-datatype { color: #800000; font-weight: bold; }
.sql1-defaultpackages { font-weight: bold; }
.sql1-delimitedidentifier { color: #808000; }
.sql1-exception { font-style: italic; }
.sql1-function { color: #000080; font-weight: bold; }
.sql1-identifier { color: #808000; }
.sql1-number { color: #800080; }
.sql1-plsql-reservedword { font-weight: bold; }
.sql1-reservedword { color: #0000FF; font-weight: bold; }
.sql1-space { }
.sql1-sqlplus-command { font-weight: bold; }
.sql1-string { color: #008000; }
.sql1-symbol { color: #0000FF; }
.sql1-tablename { color: #FF00FF; }
.sql1-variable { color: #800080; }
-->
</style><br>
Nun würd ich mir gern sowas wie eine php-Seite basteln, in der ich
diese und ggf. noch ein paar andere Queries auswählen und an die DB
schicken könnte. Das ERgebnis hätt ich dann in einem Graphen
automatisch dargestellt.<br>
<br>
a) hat jemand einen Tipp oder noch besser ein Muster, das ich
anpassen kann?<br>
b) @Andreas: gibts für Temperaturen auch etwas aus deiner
aggregate-Tabelle zu holen?<br>
<br>
Merci... LG Heiko<br>
</body>
</html>