CronJob-Service
bei SELFPHP mit ...
|
+ minütlichen Aufrufen
+ eigenem Crontab Eintrag
+ unbegrenzten CronJobs
+ Statistiken
+ Beispielaufrufen
+ Control-Bereich
Führen Sie mit den CronJobs von
SELFPHP zeitgesteuert Programme
auf Ihrem Server
aus. Weitere Infos
|
:: Anbieterverzeichnis ::
Globale Branchen
Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP
:: Newsletter ::
Abonnieren Sie hier den kostenlosen
SELFPHP Newsletter!
|
MySQLi/PDO/(MySQL) Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQLi/PDO/(MySQL) diskutieren |
21.05.2018, 11:05:47
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
mehrere Tabellen über Timestamp abfragen
Hallo zusammen,
ich brauch mal wieder Eure Hilfe bzw. Rat.
Wie in der oben schon genannt möchte ich ein Query über 4 Tabellen mit dem Timestamp als gemeinsame Basis.
An und für sich ist das kein Problem, jedoch sind die Timestamps der einzelnen Tabellen NICHT synchron und unterscheiden sich jeweils im Sekundenbereich.
Worum geht es:
Ich hab mir ein kleines Monitoring für mein Haus erstellt und möchte nun die Daten entsprechend auswerten.
Im speziellen Fall geht es um die Daten vom Strom und Heizungsschaltung in Verbindung mit der PV-Anlage.
Um in etwa sagen zu können wie und wann ist die Heizung im PV-Modus gelaufen und wieviel musste ich wirklich Heizstrom einkaufen.
Heizung und Stromzähler werden im "MINUTEN-TAKT" mitgeloggt und die Daten direkt oder einmal am Tag das Logfile in die Datenbank übertragen.
Dadurch befinden sich in jeder Tabelle ca. 500.000 Datensätze und tägl. kommen 1440 pro Tabelle hinzu.
Leider sind die Timestamps nicht Sekundengleich.
Ich brauch auch keine Sekundengleiche Abfrage, wenn diese auf die Minute ist reicht mir das auch!!
Ich habe zwar ein Query das soweit funktioniert, nur braucht es fast 4 Minuten bis ein Ergebnis kommt.
In dem Query wird quasi die Differenz zweier Timestamps gebildet und wenn diese unter 1 Minute ist.... Treffer.
Aber ich denke er muss von jeder Tabelle jedesmal alle gut 500.000 Datenzästze abgleichen und das dauert dann eben eine Ewigkeit.
Hier mal die Abfrage:
PHP-Code:
// Datum der Form dd.mm.yyyy umwandeln in YYYY-mm-dd
if($von !== NULL && $bis !== NULL){
$vondatum_split = explode(".",$von);
$vondatum = $vondatum_split[2]."-".$vondatum_split[1]."-".$vondatum_split[0];
$bisdatum_split = explode(".",$bis);
$bisdatum = $bisdatum_split[2]."-".$bisdatum_split[1]."-".$bisdatum_split[0];
$zeitraum = " heizung_webdaten.timestamp
BETWEEN '".$vondatum."' AND '".$bisdatum."'";
} elseif ($jahr !== NULL){
$zeitraum = " YEAR(heizung_webdaten.timestamp) = ".$jahr."";
}
$sql_query = "SELECT heizung_webdaten.*, strom_heizungsdaten.timestamp AS timestamp_strom, strom_heizungsdaten.WirkenergieSigmaL_imp, strom_hausdaten.WirkleistungSigmaL
FROM heizung_webdaten, strom_heizungsdaten, strom_hausdaten
WHERE ".$zeitraum."
AND heizung_webdaten.wp_status = 12
AND ABS(UNIX_TIMESTAMP(heizung_webdaten.timestamp) - UNIX_TIMESTAMP(strom_heizungsdaten.timestamp)) < 59
AND ABS(UNIX_TIMESTAMP(heizung_webdaten.timestamp) - UNIX_TIMESTAMP(strom_hausdaten.timestamp)) < 59
ORDER BY heizung_webdaten.timestamp";
echo $sql_query."<br>";
$result = $mysqli->query($sql_query);
echo $mysqli->error;
while($daten = $result->fetch_assoc()){
echo $daten['timestamp']." : ".$daten['wp_status']." => HZG-Strom_Daten: ".$daten['timestamp_strom']." -> ".$daten['WirkenergieSigmaL_imp']."kWh | akt. Verbrauch:".$daten['WirkleistungSigmaL']." kW<br>";
}
P.S.: die Ausgabe in der Schleife ist nur dazu da um zu sehen ob das Query ein Ergebnis bringt und hat nix mit der Auswertung zu tun.
Hoffe Ihr könnt mir hier ein wenig auf die Sprünge helfen
Grüße
Alex
|
21.05.2018, 23:28:40
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
AW: mehrere Tabellen über Timestamp abfragen
Ich hab natürlich auch weiter um eine Lösung gesucht und hab mein Query etwas umgebaut.
Muss dazu sagen, dass ich ein absoluter Laie bin und programmiere nur für mich als Hobby.
Hab zwei SELECT-Abfragen integriert die von den anderen Tabellen auch nur den benötigten Zeitraum liefern sollen um die Zeit für die Abarbeitung zu verkürzen.
Die Zeit hat sich auch auf ca. 50 Sekunden verkürzt - ist aber immer noch sehr lange wenn man vor dem Bildschirm sitzt und warten muss.
Hier die aktuelle Abfrage:
PHP-Code:
// Datum der Form dd.mm.yyyy umwandeln in YYYY-mm-dd
if($von !== NULL && $bis !== NULL){
$vondatum_split = explode(".",$von);
$vondatum = $vondatum_split[2]."-".$vondatum_split[1]."-".$vondatum_split[0];
$bisdatum_split = explode(".",$bis);
$bisdatum = $bisdatum_split[2]."-".$bisdatum_split[1]."-".$bisdatum_split[0];
$zeitraum = " BETWEEN '".$vondatum."' AND '".$bisdatum."'";
} elseif ($jahr !== NULL){
$zeitraum = " YEAR(heizung_webdaten.timestamp) = ".$jahr."";
}
$sql_query = "SELECT hzw.*, sthz.timestamp AS timestamp_strom, sthz.WirkenergieSigmaL_imp, sths.WirkleistungSigmaL
FROM heizung_webdaten hzw,
(SELECT * FROM strom_heizungsdaten WHERE timestamp ".$zeitraum.") sthz,
(SELECT * FROM strom_hausdaten WHERE timestamp ".$zeitraum.") sths
WHERE hzw.timestamp ".$zeitraum."
AND hzw.wp_status = 12
AND ABS(UNIX_TIMESTAMP(hzw.timestamp) - UNIX_TIMESTAMP(sthz.timestamp)) < 59
AND ABS(UNIX_TIMESTAMP(hzw.timestamp) - UNIX_TIMESTAMP(sths.timestamp)) < 59
ORDER BY hzw.timestamp";
echo $sql_query."<br>";
$result = $mysqli->query($sql_query);
echo $mysqli->error;
while($daten = $result->fetch_assoc()){
echo $daten['timestamp']." : ".$daten['wp_status']." => HZG-Strom_Daten: ".$daten['timestamp_strom']." -> ".$daten['WirkenergieSigmaL_imp']."kWh | akt. Verbrauch:".$daten['WirkleistungSigmaL']." kW<br>";
}
|
22.05.2018, 14:22:00
|
|
Administrator
|
|
Registriert seit: Jul 2004
Beiträge: 3.707
|
|
AW: mehrere Tabellen über Timestamp abfragen
Hinweis/Tipp/Frage: Alle vier Tabellen verfügen über die notwendigen (gleichen) Indizes?
__________________
Gruss vt1816
Erwarte nicht, dass sich jemand mehr Mühe mit der Antwort gibt als Du Dir mit der Frage.
. . . . . Feedback wäre wünschenswert
Ich werde keinen privaten 1:1 Support leisten, außer ich biete ihn ausdrücklich an.
Ansosnten gilt: Hilfe ausserhalb dieses Thread (PN, WhatsApp, Skype, Mail, ICQ, etc...) nur per Barzahlung oder Vorauskasse!
Wenn man sich selbst als "Noob" bezeichnet, sollte man die Finger davon lassen.
Wenn man gewillt ist daran etwas zu ändern, lernt man Grundlagen!
|
23.05.2018, 15:08:04
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
AW: mehrere Tabellen über Timestamp abfragen
Nein, hatte keinerlei Indizes angelegt.
Hab nun einen Unique Index über `timestamp`in jeder Tabelle gelegt.
Auch die Abfrage hab ich nochmals verändert so das ich aktuell auf ca 40 Sekunden (Zeitfraum 01.05.2018 bis 14.05.2018) für die Abfrage komme.
Hab den Zeitraum mal geändert, da ich ja ggf. die Daten eines Jahres auswerten möchte also (01.01.2018 - 14.05.2018)... läuft sich das Script wohl tot, nach 15 Min. noch kein Ergebnis.
PHP-Code:
// Datum der Form dd.mm.yyyy umwandeln in YYYY-mm-dd
if($von !== NULL && $bis !== NULL){
$vondatum_split = explode(".",$von);
$vondatum = $vondatum_split[2]."-".$vondatum_split[1]."-".$vondatum_split[0];
$bisdatum_split = explode(".",$bis);
$bisdatum = $bisdatum_split[2]."-".$bisdatum_split[1]."-".$bisdatum_split[0];
$zeitraum = " BETWEEN '".$vondatum."' AND '".$bisdatum."'";
} elseif ($jahr !== NULL){
$zeitraum = " YEAR(heizung_webdaten.timestamp) = ".$jahr."";
}
$sql_query = "SELECT hzw.*, sthz.timestamp AS timestamp_strom, sthz.WirkenergieSigmaL_imp, sths.WirkleistungSigmaL
FROM (SELECT *, CONVERT(DATE_FORMAT(timestamp,'%Y-%m-%d-%H:%i:00'), DATETIME) AS w_timestamp FROM heizung_webdaten WHERE timestamp ".$zeitraum.") hzw,
(SELECT * FROM strom_heizungsdaten WHERE timestamp ".$zeitraum.") sthz,
(SELECT * FROM strom_hausdaten WHERE timestamp ".$zeitraum.") sths
WHERE hzw.wp_status = 12
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sthz.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sths.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
ORDER BY hzw.w_timestamp";
echo $sql_query."<br>";
$result = $mysqli->query($sql_query);
echo $mysqli->error;
while($daten = $result->fetch_assoc()){
echo $daten['timestamp']." : ".$daten['wp_status']." => HZG-Strom_Daten: ".$daten['timestamp_strom']." -> ".$daten['WirkenergieSigmaL_imp']."kWh | akt. Verbrauch:".$daten['WirkleistungSigmaL']." kW<br>";
}
Geändert von AlexD (23.05.2018 um 15:24:26 Uhr)
|
23.05.2018, 15:11:56
|
|
Administrator
|
|
Registriert seit: Jul 2004
Beiträge: 3.707
|
|
AW: mehrere Tabellen über Timestamp abfragen
Nun die neue Query mit EXPLAIN testen ob es noch Reserven gibt.
__________________
Gruss vt1816
Erwarte nicht, dass sich jemand mehr Mühe mit der Antwort gibt als Du Dir mit der Frage.
. . . . . Feedback wäre wünschenswert
Ich werde keinen privaten 1:1 Support leisten, außer ich biete ihn ausdrücklich an.
Ansosnten gilt: Hilfe ausserhalb dieses Thread (PN, WhatsApp, Skype, Mail, ICQ, etc...) nur per Barzahlung oder Vorauskasse!
Wenn man sich selbst als "Noob" bezeichnet, sollte man die Finger davon lassen.
Wenn man gewillt ist daran etwas zu ändern, lernt man Grundlagen!
|
23.05.2018, 16:00:32
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
AW: mehrere Tabellen über Timestamp abfragen
Das Ergebnis von EXPLAIN, ausgeführt in PHPmyAdmin.
Hoffe es ist von der Formatierung lesbar, ansonsten bearbeit ich es noch.
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 225608 Using temporary; Using filesort
1 PRIMARY <derived2> ref <auto_key0> <auto_key0> 8 const,func 10 Using where
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 227349 Using where; Using join buffer (Block Nested Loop)
4 DERIVED strom_hausdaten ALL timestamp NULL NULL NULL 454699 Using where
3 DERIVED strom_heizungsdaten ALL timestamp NULL NULL NULL 451217 Using where
2 DERIVED heizung_webdaten ALL timestamp NULL NULL NULL 91649 Using where
|
23.05.2018, 16:08:12
|
|
Administrator
|
|
Registriert seit: Jul 2004
Beiträge: 3.707
|
|
AW: mehrere Tabellen über Timestamp abfragen
Noch ein Tipp: Benutze beim SELECT nicht '*', sondern selektiere wirklich nur die Felder die Du benötigst. Denn es werden ja nicht alle sein aus jeder Tabelle sein.
__________________
Gruss vt1816
Erwarte nicht, dass sich jemand mehr Mühe mit der Antwort gibt als Du Dir mit der Frage.
. . . . . Feedback wäre wünschenswert
Ich werde keinen privaten 1:1 Support leisten, außer ich biete ihn ausdrücklich an.
Ansosnten gilt: Hilfe ausserhalb dieses Thread (PN, WhatsApp, Skype, Mail, ICQ, etc...) nur per Barzahlung oder Vorauskasse!
Wenn man sich selbst als "Noob" bezeichnet, sollte man die Finger davon lassen.
Wenn man gewillt ist daran etwas zu ändern, lernt man Grundlagen!
|
23.05.2018, 16:56:01
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
AW: mehrere Tabellen über Timestamp abfragen
Hmm... die Abfrage mit dem '*' war aus Bequemlichkeit, hab jetzt nur 1 bis 2 Spalten die ich benötige angegeben.
Wenn ich nun den Zeitraum vom 01.05.2018 bis 14.05.2018 abfrage dauert es noch ca. 25 Sekunden bis mir das Ergebnis angezeigt wird.
vom Script gemessen: Abfrage dauerte: 23 Sek. | 977 Datensätze gefunden
Erhöhe ich den Zeitraum z.B. 01.04.2018 bis 14.05.2018 so dauert die Abfrage schon 4:47 Min.
vom Script gemessen: Abfrage dauerte: 294 Sek. | 3851 Datensätze gefunden
Vielleicht zum Server... ist ein kleiner privater Server.
Windows 7 64Bit als VirtualMachine mit MySQL-CommunityServer 5.6.26
8 GB RAM ECC und Intel XEON E5-2620 v2 mit 4 Kernen (zugewiesen)
Hypervisor ist Windows Server 2012 R2 Hyper-V Core.
Geändert von AlexD (23.05.2018 um 17:06:26 Uhr)
|
23.05.2018, 16:59:02
|
|
Administrator
|
|
Registriert seit: Jul 2004
Beiträge: 3.707
|
|
AW: mehrere Tabellen über Timestamp abfragen
Prüfe mal beide Varianten mit EXPLAIN.
Der Unterschied mit oder ohne '*' kann/sollte nicht solch eine Auswirkung haben.
__________________
Gruss vt1816
Erwarte nicht, dass sich jemand mehr Mühe mit der Antwort gibt als Du Dir mit der Frage.
. . . . . Feedback wäre wünschenswert
Ich werde keinen privaten 1:1 Support leisten, außer ich biete ihn ausdrücklich an.
Ansosnten gilt: Hilfe ausserhalb dieses Thread (PN, WhatsApp, Skype, Mail, ICQ, etc...) nur per Barzahlung oder Vorauskasse!
Wenn man sich selbst als "Noob" bezeichnet, sollte man die Finger davon lassen.
Wenn man gewillt ist daran etwas zu ändern, lernt man Grundlagen!
|
23.05.2018, 18:27:23
|
Junior Member
|
|
Registriert seit: Jun 2003
Beiträge: 186
|
|
AW: mehrere Tabellen über Timestamp abfragen
Die Ausgabe von EXPLAIN mit '*' und Ohne zeigte so gut wie Identische Werte an.
Nur die Abarbeitungszeit bei definierten Spalten ist nur halb so lang aber leider eben immer noch sehr lange.
Extrem wenn ich mehr bzw. den Zeitraum erhöhe wie im Beispiel unten (01.04.2018-14-05.2018).
Geplant wäre ja die Daten eines Jahres auszuwerten um zu Berechnen wie/wenn die Wärmepumpe mit PV-Strom lief usw...
Mit '*'. Zeitraum 01.05.2018-14.05.2018
SELECT hzw.timestamp, hzw.wp_status, sthz.timestamp AS timestamp_strom, sthz.WirkenergieSigmaL_imp, sths.WirkleistungSigmaL
FROM
(SELECT *, CONVERT(DATE_FORMAT(timestamp,'%Y-%m-%d-%H:%i:00'), DATETIME) AS w_timestamp FROM heizung_webdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') hzw,
(SELECT * FROM strom_heizungsdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') sthz,
(SELECT * FROM strom_hausdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') sths
WHERE hzw.wp_status = 12
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sthz.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sths.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
ORDER BY hzw.w_timestamp
Ergebnis:
Abfrage dauerte: 44 Sek. | 977 Datensätze gefunden
==========================================================
Ohne '*' mit definierten Spalten: Zeitraum 01.05.2018-14.05.2018
SELECT hzw.timestamp, hzw.wp_status, sthz.timestamp AS timestamp_strom, sthz.WirkenergieSigmaL_imp, sths.WirkleistungSigmaL
FROM
(SELECT timestamp, wp_status, CONVERT(DATE_FORMAT(timestamp,'%Y-%m-%d-%H:%i:00'), DATETIME) AS w_timestamp FROM heizung_webdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') hzw,
(SELECT timestamp, WirkenergieSigmaL_imp FROM strom_heizungsdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') sthz,
(SELECT timestamp, WirkleistungSigmaL FROM strom_hausdaten WHERE timestamp BETWEEN '2018-05-01' AND '2018-05-14') sths
WHERE hzw.wp_status = 12
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sthz.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sths.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
ORDER BY hzw.w_timestamp
Abfrage dauerte: 23 Sek. | 977 Datensätze gefunden
********************************************************************** ****
Abfragezeitraum 01.04.2018-14.05.2018
Mit definierte Spalten zum Afragen:
SELECT hzw.timestamp, hzw.wp_status, sthz.timestamp AS timestamp_strom, sthz.WirkenergieSigmaL_imp, sths.WirkleistungSigmaL
FROM
(SELECT timestamp, wp_status, CONVERT(DATE_FORMAT(timestamp,'%Y-%m-%d-%H:%i:00'), DATETIME) AS w_timestamp FROM heizung_webdaten WHERE timestamp BETWEEN '2018-04-01' AND '2018-05-14') hzw,
(SELECT timestamp, WirkenergieSigmaL_imp FROM strom_heizungsdaten WHERE timestamp BETWEEN '2018-04-01' AND '2018-05-14') sthz,
(SELECT timestamp, WirkleistungSigmaL FROM strom_hausdaten WHERE timestamp BETWEEN '2018-04-01' AND '2018-05-14') sths
WHERE hzw.wp_status = 12
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sthz.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
AND hzw.w_timestamp = CONVERT(DATE_FORMAT(sths.timestamp,'%Y-%m-%d-%H:%i:00'),DATETIME)
ORDER BY hzw.w_timestamp
Abfrage dauerte: 298 Sek. | 3851 Datensätze gefunden
----------------------------------------
Mit '*' in den Tabellen
Abfrage dauerte: 519 Sek. | 3851 Datensätze gefunden
|
Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
|
|
Themen-Optionen |
|
Ansicht |
Linear-Darstellung
|
Forumregeln
|
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.
HTML-Code ist aus.
|
|
|
Alle Zeitangaben in WEZ +2. Es ist jetzt 01:22:11 Uhr.
|