Hallo,
hier mal ein Update, ich schreibe im Moment ca. 120000 Datensätze pro Tag, das funktioniert noch.
Hier noch mal ein Abfragescript für Nutzer von pChart.
Im Moment nur für nicht Zähler Variablen.
<?
// Standard inclusions
include('pData.class');
include('pChart.class');
// Postgresql Einstellungen
$host = "192.168.X.X"; //Server IP hier eintragen
$port = "5432"; //Server Port
//
date_default_timezone_set("UTC");
$Variable = 59591; // Variable die dargestellt werden soll.
//
$variablenInfo = IPS_GetObject($Variable);
$variablenName = $variablenInfo['ObjectName'];
$varinfo = IPS_GetVariable($Variable);
$profil = IPS_GetVariableProfile($varinfo['VariableCustomProfile']);
$suffix = $profil['Suffix'];
$Vartyp = $profil['ProfileName'];
//
$startdatum = '01.05.2014 00:00:00 UTC';
$timing = 3600;
$steps = 745;
$dbconn = pg_connect("host=$host port=$port dbname=ipsgraphenlogging user=ips password=ips") or die ('Verbindungsaufbau fehlgeschlagen: ' . pg_last_error());
$query = "SELECT *
FROM (select ('$startdatum'::timestamp + s * '$timing seconds'::interval) AS updatezeit
FROM generate_Series(0,$steps)s)
foo left join
(
SELECT to_timestamp(trunc(date_part('epoch', updatezeit)/$timing) * $timing) at time zone 'UTC' AS updatezeit,
count(*) as thecount,
round (cast(avg (value)as numeric),1) as avg,
round (cast(min (value)as numeric),1) as min,
round (cast(max (value)as numeric),1) as max
FROM variablen WHERE updatezeit >= '$startdatum' AND varid = '$Variable'
GROUP BY to_timestamp(trunc(date_part('epoch', updatezeit)/$timing) * $timing) at time zone 'UTC'
ORDER BY updatezeit
) as n2
using(updatezeit)
order by 1
";
$result = pg_query($query) or die ('Abfrage fehlgeschlagen: ' . pg_last_error());
//
$query2 = "SELECT round (cast(min (value)as numeric),1) as min, round (cast(max (value)as numeric),1) as max FROM variablen WHERE updatezeit >= '$startdatum' AND varid = '$Variable'" ;
$result2 = pg_query($query2) or die ('Abfrage fehlgeschlagen: ' . pg_last_error());
//
pg_close($dbconn);
while ($werte2 = pg_fetch_row($result2))
{
//print_r ($werte2);
$temp_min = $werte2[0];
$temp_max = $werte2[1];
}
//
$DataSet = new pData;
while ($werte = pg_fetch_row($result))
{
$date = $werte[0];
if ($werte[2] == "")
{
$temp = $temp_min;
}
else
{
$temp = $werte[2];
}
$DataSet->AddPoint($temp,"AVG");
$DataSet->AddPoint($date,"TIME");
}
$DataSet->AddAllSeries();
$DataSet->RemoveSerie("TIME");
$DataSet->SetAbsciseLabelSerie("TIME"); //Y-Achsen Einteilung
$DataSet->SetYAxisName("$Vartyp");
$DataSet->SetYAxisUnit("$suffix");
// Initialise the graph
$Graph = new pChart(900,440);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawGraphAreaGradient(50,50,100,100,TARGET_BACKGROUND);
$Graph->setGraphArea(80,30,850,350);
$Graph->drawGraphAreaGradient(155,155,155,100);
$Graph->setFixedScale($temp_min,$temp_max);
$Graph->drawScale($DataSet->GetData(),$DataSet->GetDataDescription(),SCALE_NORMAL,255,255,255,TRUE,45,3,True,20);
// Draw the 0 line
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawTreshold(200,143,55,72,TRUE,TRUE);
// Draw the line graph
$Graph->drawBarGraph($DataSet->GetData(),$DataSet->GetDataDescription());
// Finish the graph
//$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawLegend(275,35,$DataSet->GetDataDescription(),255,255,255);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->drawTitle(60,22,"$Vartyp",255,255,255,585);
$Graph->setFontProperties("tahoma.ttf",8);
$Graph->addBorder(2);
$Graph->Render(IPS_GetKernelDir()."media/$variablenName.$Variable.png");
?>
Ansonsten hab ich noch noch eine zusatz Funktion in Arbeit die PG Daten in die „RS HighChartsPHP Spielwiese“ einliest.
Schöne Grüßen
Thomas
