Verdichtungstabellen für Messwertarchiv erstellen

[Medium]

Huhu DP!

Ich habe gerade ein recht spannendes Problem. Ich erstelle ein Messwertarchiv, in dem diverse Werte mit ihrem Messzeitpunkt gespeichert werden. Jeder Wert gehört zu einer eindeutigen Messstelle, und jede Messstelle kann ein anderes Messintervall aufweisen. (Heisst: In der Tabelle sind wild sekündliche, minütliche und andere Intervalle vorhanden. Aber alle mit vollem Datum+Uhrzeit.)
Alle Messwerte landen zunächst in dieser großen "history" Tabelle mit folgendem Aufbau:

Code:

messID, messDatum, messWertAVG, messWertMAX

(messWertMAX macht hier noch keinen Sinn, gleich aber!)
Ein Index ist auf "messDatum, messID". messID ist ein Fremdschlüssel in meine Messstellenliste, die in einer anderen Tabelle liegt. Aufbau:

Code:

ID, messName, gatewayID, adresse

(die letzten zwei Felder sind für meine Modbus-Gateways, die die Werte liefern. Für das Problem hier unerheblich.)

Jetzt möchte ich zusätzliche Tabellen mit genau demselben Aufbau wie die "history" (wichtig!) haben, in denen aber dann die Durchschnittswerte und Maxima von Zeiträumen stehen. Also eine Tabelle für alle Werte minütlich, eine stündlich, eine tageweise - hier möchte ich auch gerne die Flexibilität haben beliebig viele Verdichtungstabellen mit frei wählbaren Intervallen erstellen zu können.
Die Tabellen sollen live, während neue Messungen kommen mitgeführt werden. Ein nachträglicher Pflegelauf ist keine Option!!

Die Krux dabei ist: Ich will, dass in den Verdichtungstabellen die Messzeiten genau auf glatte Grenzen fallen. Wenn ich also eine stündliche Tabelle habe, sollen dort nur 07:00:00, 13:00:00, usw. stehen. Bei Minütlich entsprechend 07:14:00, 13:41:00, usw.
Im Grunde also so, dass das Intervall immer ab einer Tagesgrenze gezählt wird. (Tagesintervall ist gleichzeitig auch das längste, das ich haben muss. Sekundenintervall ist das kleinste, welches in der Basistabelle vorkommt.)

Bisher mache ich das so, dass ich nach jedem Mal einen Messwert eintragen für alle Verdichtungen dies Mache:
a) Einen Zeitraum der Länge des gewünschten Intervalls suchen, für den "jetzt" so gerade in der Zukunft liegt, und dessen Startzeitpunkt dem o.g. Raster gehorcht. (Ich warte also immer auf fertige Invervall-Päckchen relativ zum Intervall in der Zieltabelle - pro messID.)
b) Ist kein Eintrag für diesen Zeitraum und messID vorhanden?
c) Gibt es für den Zeitraum und diese messID Werte in der Basistabelle?
Wenn b und c zutreffen, wird per SQL Statement ein entsprechend aggregierter Datensatz in meine Zieltabelle geschrieben, wo der Durchschnitt und das Maximum aus dem jeweiligen Messzeitraum der Basistabelle steht.

Leider ist dies VIEL zu langsam wenn man bedenkt, dass ich am Ende gut 400 Messstellen, größtenteils sekündlich erfasse. Zu viele Abfragen über potenziell riesige Datenmengen.

Ich bin hier offen für beides: Grundlegende Vorgehensweisen, aber auch konkrete Vorschläge.

Wem das Hirn nach Lesen meiner Ausführungen genau so verknotet ist wie meines, der soll bitte unbedingt noch Mal nachfragen! Ich habe mir zwar Mühe gegeben das so kompakt aber dennoch allgemeinverständlich zu schreiben wie es mir gerade möglich war, aber das ist ja noch lange keine Garantie :)

Danke euch schon mal!

[Sir Rufo]

Dieses "beliebig viele" kann natürlich den Server zum Kochen bringen.

Grundsätzlich ist das aber vom Prinzip kein Problem. Aus dem Zeitpunkt erstellst du dir den Intervall-Zeitpunkt.

Code:

2015-08-08 13:16:54
s 2015-08-08 13:16:54
m 2015-08-08 13:16:00
15m 2015-08-08 13:15:00
h 2015-08-08 13:00:00
d 2015-08-08 00:00:00

und trägst den Wert (am Besten per SP) in die Tabelle ein. Die Tabelle benötigt noch einen zusätzlichen Wert, mit der Anzahl der kumulierten Datansätze.

Den neuen Durchschnittswert errechnest du mit

Code:

( TabAnzahl * TabDurchschnitt + NeuDurchschnitt ) / ( TabAnzahl + 1 )

[Medium]

Hmmmmmm. Ich muss gestehen: Einfach aber gar nicht so übel. Ich war bisher in dem Gedanken verhaftet, dass es viel besser sein muss das "in bulk" zu erledigen, so dass z.B. alle messIDs gleich in einem Statement verarbeitet werden. Aber wenn man das so aufzieht, kann das sogar ganz gut klappen! Ich werde das morgen mal probieren. Vor allem gefällt mir, dass dieser Weg die Komplexität aus den SQL Statements raus nimmt.

Muss mir nur noch überlegen, wie ich eine SP schreibe (selten gemacht bisher), die entweder einen Kummulier-Datensatz anlegt wenn es für den Zeitpunkt noch keinen gibt, oder eben kummuliert wenn er schon da ist. Die Abfrage nach der Existenz seitens meines Programms würde ich mir nämlich gerne sparen. Dann ist das pro Wert, pro Verdichtungstabelle, nur ein Statement. Das wäre fein.

Danke dir! Manchmal muss man in die Eleganz der Simplizität zurück gerufen werden :D


Edit: "Beliebig viele" heisst in dem Fall übrigens das, was du im Grunde schon gezeigt hast. Mehr als 6-8 werden das nicht. Ich möchte ganz gerne mit verschiedenen Granularitäten herumspielen können, da das auswertende Gegenstück eine eigene Chart-Komponente ist, die dynamisch abhängig vom "Zoom" eine geeignete Verdichtungstabelle als Basis wählen soll. Ich will damit erreichen, dass man flotti Übersichten über Monate und Jahre haben kann, aber dennoch übergangsfrei von da aus bis auf sekundengenau hineinzoomen kann. Das ist die eigentliche Motivation die dahinter steckt.
Derzeit scheint sich das hier ganz gut anzustellen: 1s, 10s, 30s, 1min, 1h, 1t, 1mon. Feiner oder mehr muss nicht. Monatlich ist eigentlich schon fast über, keiner wird sich >5 Jahre am Stück angucken müssen, und selbst dann ist die Anzahl noch überschaubar bei täglich.

[Sir Rufo]

So sieht so ein Statement aus

SQL-Code:

CREATE TABLE mw
    (`messID` INT NOT NULL , 
     `messAggID` INT NOT NULL,
     `messDatum` DATETIME NOT NULL, 
     `messWertAVG` FLOAT , 
     `messWertMAX` FLOAT, 
     `messWertANZ` INT DEFAULT 1, 
     PRIMARY KEY (messID, messAggID, messDatum) );
    
INSERT INTO mw
    (`messID`, `messAggID`, `messDatum`, `messWertAVG`, `messWertMAX`)
VALUES
    (1,1,'2015-08-08 13:15:00',5,6)
ON DUPLICATE KEY UPDATE
  messWertAVG = ( messWertAVG * messWertANZ + 5 ) / ( messWertANZ + 1 ),
  messWertMAX = IF( messWertMAX > 6, messWertMAX, 6 ),
  messWertANZ = messWertANZ +1
;

INSERT INTO mw
    (`messID`, `messAggID`, `messDatum`, `messWertAVG`, `messWertMAX`)
VALUES
    (1,1,'2015-08-08 13:15:00',6,7)
ON DUPLICATE KEY UPDATE
  messWertAVG = ( messWertAVG * messWertANZ + 6 ) / ( messWertANZ + 1 ),
  messWertMAX = IF( messWertMAX > 7, messWertMAX, 7 ),
  messWertANZ = messWertANZ +1
;

Eigentlich brauchst du gar keine SP, die Statistik kannst du über ein Statement mit Parametern jagen. Alles in einer Transaktion erledigen und gut ist.

[Uwe Raabe]

Nur um sicher zu gehen, daß ich das richtig verstanden habe:

Die einzelnen Messwerte und die kumulierten Werte werden alle in dieselbe Tabelle eingetragen und die Abfrage für eine bestimmte Granularität erfolgt dann über die Selektion nach messAggID?

Wenn nicht, was ist dann messAggID?

[Medium]

Wow, danke! Wenn ich nicht so müde wäre, würde ich am liebsten direkt ins Büro rüber und das rund machen!

@Uwe: Ich vermute, dass Sir Rufo das so gemeint hat. Ginge rein theoretisch von meinen Randbedingungen her auch. Ich vermute aber, dass es am Ende etwas performanter kommt, wenn ich doch für alle Aggregat-Stufen separate Tabellen nehme, wie ich es jetzt schon mache. Vielleicht findet sich am Wochenende etwas Zeit das genauer zu messen.
Leider dauert das Erzeugen von Testdaten hier naturgemäß relativ lange. Sekündliche Werte für ein ganzes Jahr, für mind. 3 Messstellen eintragen - da jodelt die DB ganz schön. Da das aber nachher ein System für den Einsatz auf mind. 5-10 Jahre gedacht ist (in der Industrie gibt's sowas ja noch), lohnt sich jeder vorab gemachte Gedanke und Test. Zumal das ganze auch noch relevant für diverse Abrechnungen wird, und als Basis für Produktpreise dienen soll.

[Medium]

Eine andere Sache am Rande noch: Wenn ich einen Datumswert in mein Statement gebe, würde ich das natürlich am liebsten mit einem Parameter. Der wird als TDateTime (=Double) entgegengenommen. In der Datenbank kann ich zwar nur bis auf eine Sekunde genau, aber wie sicher ist es, dass mir ein Unique-Index mit dem Datumsfeld zuschlägt obgleich ich da mit einem wackeligen Float rein gehe? Sollte ich da doch lieber mit einem FormatDateTime() vorab in einen String umwandeln, oder ist das unnötig?

[Sir Rufo]

Du brauchst keine separaten Tabellen.

Ja diese messAggID ist für den AggregatTypen und für jeden dieser Typen erstellst du eine Partition. Von der Performance ist das genauso, als ob du unterschiedliche Tabellen pro AggregatTypen hättest, nur die Abfrage ist komfortabler, weil es eine logische Tabelle ist.

[Medium]

Partitionen wollte ich bereits für Zeitabschnitte einrichten. Vermutlich Jährlich. Ich vermute mal ganz stark, dass das nicht mischbar ist dann. Ansonsten natürlich völlig richtig.

[Sir Rufo]

Mit MySQL kannst du auch genauere Zeitwerte ablegen
http://dev.mysql.com/doc/refman/5.6/...l-seconds.html

Aber so eine Umwandlung ist nicht notwendig

[Sir Rufo]

Zitat:

Zitat von Medium (Beitrag 1311179)

Partitionen wollte ich bereits für Zeitabschnitte einrichten. Vermutlich Jährlich. Ich vermute mal ganz stark, dass das nicht mischbar ist dann. Ansonsten natürlich völlig richtig.

AFAIK geht das auch gemischt, bzw. so kombiniert

[Medium]

Genauer will ich ja gar nicht. Im Gegenteil - Auf Sekunden begrenzt zu sein kommt mir hier eher entgegen! Die Partitionierung probiere ich einfach mal aus. MySQL wird vermutlich schon was sagen, wenn das nicht geht. Danke dir für deine geniale Hilfe. Morgen wird mächtig getestet!

[Dejan Vu]

Wurde schon erwähnt, das Du deinen Interval-Wert durch eine einfache Rechnung hinbekommst? Dazu nimmst du die Zeit als Double/Float und rundest sie gemäß der Formel

Delphi-Quellcode:

(trunc(T/I)+0.5)*I

(I=Interval, T=Zeit).

Wenn Du dann dazu noch deine Intervalle in einer kleinen separaten Tabelle hast, kannst du (vermutlich) alle Intervalle in einem Rutsch ablegen. Leider kenne ich mich nicht mit mySQL aus, aber zumindest *anzeigen* würde so gehen. (Sei 'Interval' eine Tabelle mit einer Spalte 'Interval' in dem die Float-Werte für deine Intervalle stehen, also 1/86400 für '1 sec', '1/1440' für '1 min', '15/1440' für '15 min' etc. Du kannst auch noch eine 'Description'-Spalte mit aufnehmen.

SQL-Code:

select :messID as messID, 
       cast(cast(:messDatum as Float)/i.interval)+0.5)*i.interval) as DateTime) as messDatum,
       IF (h.messID is null, :messwert, (messWertAVG *messWertANZ + :messwert) / (messWertANZ + 1) as messWertAVG,
       IF (h.messID is null, :messwert, IF(messWertMAX > :messwert, messWertMAX, :messwert) as messWertMAX
       IF (h.messID is null, 1, messWertANZ + 1) as messWertANZ
  from interval i 
       full join history h
  where h.messDatum = cast(cast(:messDatum as Float)/i.interval)+0.5)*i.interval) as DateTime)
    and h.messID = :messID

So zumindest die Idee. Im o.g. Query sind bestimmt mindestens Tippfehler drin.

Für jeden Eintrag in der Interval-Tabelle erzeugst Du eine Zeile in dieser Anzeige (Tabelle).
1. Spalte: messID (Parameter aus deiner Software)
2. Spalte: Berechnetes Interval-Datum
3. Spalte: Wenn es den Eintrag in History schon gibt, dann den überarbeiteten AVG-Wert, sonst den Messwert (der ist da der erste im neuen Interval)
4. Spalte: Ditto, nur für MAX-Wert
5. Spalte: Ditto, nur für ANZ-Wert

Wenn Du es schaffst, diese Tabelle (also dieses SELECT) in einem MERGE bzw. INSERT OR UPDATE - Befehl in deine History-Tabelle zu speichern, dann reicht dieser eine Befehl aus, um beliebig viele Intervalle abzulegen. Vorteil: Du kannst die Interval-Tabelle beliebig erweitern.

Da nur mit den PK-Indexen gearbeitet wird, sollte das auch sehr schnell gehen.

Ich persönlich würde der Interval-Tabelle noch eine Spalte 'ID' als PK spendieren und diese ID in der History-Tabelle mit ablegen. So bekommst Du einfach z.B. alle '15 min' Intervalle in einem einfachen Statement (woher willst Du sonst wissen, zu welchem Intervall welcher Eintrag gehört?).

SQL-Code:

select h.* 
  from History h 
       join Intervalle I on h.IntervalID = I.ID 
 where I.Description = '15 min'

Ach, ich sehe gerade: Du willst pro Interval eine eigene Tabelle haben. Na gut. Wie Du willst. Dann brauchst Du natürlich pro Tabelle ein eigenes INSERT und kannst Dir das hier alles sparen...

Ich erwähne es trotzdem, denn ich finde das flexibler.

[jobo]

Schöne Lösung von Sir Rufo!

Noch ein paar Anmerkungen hierzu, eher aus Neugier:

Zitat:

Zitat von Medium (Beitrag 1311176)

5-10 Jahre gedacht ist (in der Industrie gibt's sowas ja noch), lohnt sich jeder vorab gemachte Gedanke und Test. Zumal das ganze auch noch relevant für diverse Abrechnungen wird, und als Basis für Produktpreise dienen soll.

Falls die verschiedenen AggrTypen in einer Tabelle landen sollten und Dritte auf die Messdaten zugreifen dürfen, empfiehlt sich vielleicht zum Auslesen eine Kapselung in versch. Views je AggTyp. (Wenn überhaupt direkt auf dem Prodsystem gelesen/ausgewertet werden darf)
Ansonsten eher zum Ablauf:
Was ist mit Messlücken? "Band steht" oder sowas.
Macht es Sinn, dann das Intervall fortzuschreiben mit 0 Werten? ("Nachträgliche Pflege ist keine Option"). Das erspart evtl. aufwendige Statements, falls chronologische Daten benötigt werden. Ist aber sicher mit Vorsicht zu genießen und hängt aber stark vom Einsatz/Weiterverwendung der Messdaten ab.
Was ist mit Messfehlern (Ausreißer, Sensorausfälle, ..)?
Falls das System irgendwie im Bereich MES, Traceability .. (und danach sieht es ja etwas aus) eingesetzt wird, wäre zu überlegen, wie man da anknüpft. Sollwerterfassung, Toleranzvorgaben, NOK Flag usw.
Naja, Aggregat Werte widersprechen dem eigentlich oder das wird sowieso separat abgefackelt oder ist unwichtig. Also das Ganze nur als Anmerkung.

[Medium]

Zitat:

Zitat von Dejan Vu (Beitrag 1311188)

Wurde schon erwähnt, das Du deinen Interval-Wert durch eine einfache Rechnung hinbekommst? Dazu nimmst du die Zeit als Double/Float und rundest sie gemäß der Formel

Delphi-Quellcode:

(trunc(T/I)+0.5)*I

(I=Interval, T=Zeit).

Mensch, da war ich gerade so stolz darauf, dass ich mir beim Zähneputzen vorhin eine Lösung mit Unix-Timestamps und Modulo dafür erdacht habe, und dann kommst du mit so einer schlanken Formel daher :D (Danke!)

Zitat:

Wenn Du dann dazu noch deine Intervalle in einer kleinen separaten Tabelle hast, kannst du (vermutlich) alle Intervalle in einem Rutsch ablegen.

Der Gedanke kam mir gestern Abend auch noch. Das hat den gewaltigen Vorteil, dass man zum Ändern der Granularitäten nichtmals mehr am Source basteln muss, und alles hübsch dynamisch über die DB passiert.
Die Idee alle Werte in eine Tabelle zu packen wird mir dank deiner eleganten Idee hier immer sympathischer. Ich werde gleich mal nachsehen, ob ich nach zwei Kriterien partitionieren kann. Wenn ja, dann ist die Sache geritzt.


@jobo:
Bisher werden Ausfälle schlicht zu Lücken in den Daten. Das werde ich wohl auch so beibehalten, da dann ganz klar ersichtlich ist, dass hier keine gültigen Werte vorliegen. Glücklicherweise steht keine Zertifizierung dahinter, so dass zumindest keine "harten" Richtlinien existieren.
Konkret geht es darum, dass ein großer Kunde von uns alle seine Betriebsbereiche (und teils auch einzelne Motoren) mit Strommessgeräten ausstatten will. Dadurch soll zum einen der Gesamtverbrauch im Werk so optimiert werden, dass teure Bezugsspitzen durch besseres "Scheduling" vermieden werden, und die Energiekosten pro Produkt pro Zeit sollen ermittelt werden um den Produkten diese entsprechend anteilig im Preis zuordnen zu können. Bisher wird das bloß geschätzt. Auch soll es dem Energielieferanten gegenüber ggf. als Indiz dienen, falls dieser stark abweichende Rechnungen stellt. Die Verbrauchssummen werden netterweise schon von den Messgeräten gebildet, so dass ich da nicht auf Vollständigkeit der Integrale unter meinen Daten achten muss.
Zugriff von aussen steht bisher nicht auf dem Plan, aber dafür dann entsprechende Views zu machen wäre sicherlich angebracht.


Danke euch beiden auch nochmal!

[jobo]

Zitat:

Zitat von Medium (Beitrag 1311201)

Bisher werden Ausfälle schlicht zu Lücken in den Daten. Das werde ich wohl auch so beibehalten, da dann ganz klar ersichtlich ist, dass hier keine gültigen Werte vorliegen.

Ok, es geht also eher um Prozessüberwachung im Ganzen bzw. interne Messgrößen, kein Fertigungsmesswerte. Ob die Lücken klar erkennbar sind, hängt dennoch aber m.E. stark von den Intervallgrößen und den Amplituden ab. Ob also letztlich die Straße beginnt Strom zu sparen oder mehr und mehr Messwerte ausfallen, ist glaub ich nicht immer so ganz klar ersichtlich (an diesen Daten). Aber wahrscheinlich gibt es da bezüglich der Sensoren auch separate Kanäle für Fehlerereignisse.

Ich würde aber mal prophezeien, dass wenn der Kunde Blut geleckt hat (und das kann ich mir hier sehr gut vorstellen), ganz viele Fragen zu Messintervallinhalten auftauchen, besonders wenn sie nicht nur zeitlich kumuliert sind, sondern auch unspezifisch bezogen auf den/die Verbraucher sind. Wie auch immer, das kommt dann im Upgrade. Finde ich spannend sowas (klar, Strom) :)

[jobo]

Zitat:

Zitat von Dejan Vu (Beitrag 1311188)

Wurde schon erwähnt,

Ich versteh nicht ganz, wo hier im Kern der Unterschied zu Sir Rufo sein soll. Oder ich verstehe den Vorschlag selbst nicht. Die Intervallberechnung hat er in seinem ersten Beitrag anhand der Ergebnisse dargestellt. Die, so hab ich das verstanden, erfolgt im Client, der dann gemäß Ergebnis zuschlägt, also on demand.

Den Intervalltyp hat er ebenfalls definiert. Kann also auch alles in eine Tabelle. Und wenn man I=14 sek. statt 15 benötigt, sagt man das halt dem Client.

Problem:
Wenn ich "fertige" Intervalle (nicht nur den Typ) in der DB ablege, muss ich das ja irgendwie kontinuierlich oder Bulkartig, jedenfalls rechtzeitig, genügend Werte für die Zukunft anlegen, oder? Der Client widerum muss dann immer (rechtzeitig) nach vordefinierten, ggF. sich ändernden Intervallen in der DB schauen.

[Uwe Raabe]

Zitat:

Zitat von jobo (Beitrag 1311208)

Wenn ich "fertige" Intervalle (nicht nur den Typ) in der DB ablege, muss ich das ja irgendwie kontinuierlich oder Bulkartig, jedenfalls rechtzeitig, genügend Werte für die Zukunft anlegen, oder?

Das ist mir noch nicht ganz klar. Du willst also schon Datensätze für alle Intervalle oder einen bestimmten Zeitraum im Voraus anlegen, in denen aber die Anzahl der verrechneten Messungen gleich 0 ist? Kannst du kurz erläutern, was du damit bezwecken willst?

[jobo]

Zitat:

Zitat von Uwe Raabe (Beitrag 1311231)

Kannst du kurz erläutern, was du damit bezwecken willst?

Uwe, meinst Du mich? Ich will das nicht, ich frage, ob ich das richtig verstanden habe.

[frankyboy1974]

Hallo,

das Problem ist

Zitat:

Konkret geht es darum, dass ein großer Kunde von uns alle seine Betriebsbereiche (und teils auch einzelne Motoren) mit Strommessgeräten ausstatten will. Dadurch soll zum einen der Gesamtverbrauch im Werk so optimiert werden, dass teure Bezugsspitzen durch besseres "Scheduling" vermieden werden, und die Energiekosten pro Produkt pro Zeit sollen ermittelt werden um den Produkten diese entsprechend anteilig im Preis zuordnen zu können.

Dafür nun eine Lösung pauschal anzubieten, übersteigt zumindestens meine Möglichkeiten.

Du optimierst das Scheduling nach t, damit opt(BK) =besser ist; ansonsten bestimmst du den Gradient und optimierst weiter.:P

mfg

frank

[Medium]

Die Optimierung (das Wort ist da durchaus hoch gegriffen, es sollte "Verbesserung" heißen) ist nicht Teil meines Projektes. Der Kunde will nachher lediglich sagen können: Aha, Betrieb 2 darf nicht gleichzeitig Produkt A herstellen wenn Betrieb 7 Produkt B fährt, weil sonst Verbrauchsspitzen möglich. Viel feiner und vollautomatisch gehts auch kaum, da man sonst ja komplett in die Prozesssteuerung mit eingreifen müsste.

Ich habe gerade ein anderes Problem! Die Lösung von DejanVu scheint mir mit DateTime Werten im Delphi-Stil zu rechnen. In MySQL gibt es aber nur DATETIME und TIMESTAMP, wobei beide beim Nutzen von arithmetischen Operationen folgendermaßen behandelt werden:
Ein Datumswert: '2015-08-06 12:55:43'
wird mit

SQL-Code:

SELECT (NOW() / 1.0)

zu 20150806125543.0000

Es wird also eher quasi "optisch" konvertiert, und der Vorkommaanteil ist so ja kaum auf das Format "Anzahl Tage seit X" zu bringen. Hat da wer eine Idee?

Edit: Okay, das hier scheint zu gehen

SQL-Code:

SELECT ((UNIX_TIMESTAMP(NOW()) / 86400.0) + 25569.0) as DelphiFormattedTime

Das werde ich dann aber wohl als Function anlgegen müssen, sonst wird das Statement ein wilder Wust.

[Sir Rufo]

Nein, so ist das dort nicht, denn schau dir die Zahl einmal genauer an:

Code:

20150806125543.0000
2015-08-06 12:55:43

;)

[Uwe Raabe]

Zitat:

Zitat von jobo (Beitrag 1311252)

Zitat:

Zitat von Uwe Raabe (Beitrag 1311231)

Kannst du kurz erläutern, was du damit bezwecken willst?

Uwe, meinst Du mich? Ich will das nicht, ich frage, ob ich das richtig verstanden habe.

Sorry, dann hatte ich das falsch zugeordnet.

[Sir Rufo]

Sehe ich eigentlich nicht so mit dem Wust

SQL-Code:

CREATE TABLE messInt ( IntID INT, IntSec INT );

INSERT INTO messInt ( IntID, IntSec )
VALUES
  (1,1),
  (2,60),
  (3,15*60),
  (4,60*60);
  
CREATE TABLE messDat ( messID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, messDate DATETIME );
INSERT INTO messDat ( messDate )
VALUES
  ( '2015-08-08 12:59:59' ),
  ( '2015-08-08 13:00:00' ),
  ( '2015-08-08 13:00:01' ),
  ( '2015-08-08 13:14:59' ),
  ( '2015-08-08 13:15:00' ),
  ( '2015-08-08 13:15:01' ),
  ( '2015-08-08 13:29:59' ),
  ( '2015-08-08 13:30:01' );

SELECT 
  messDat.*, messInt.*,
  FROM_UNIXTIME( ( UNIX_TIMESTAMP( messDat.messDate ) DIV messInt.IntSec ) * messInt.IntSec ) AS intDate
FROM
  messDat, messInt;

[Medium]

Den Wust würde es bei der Verwendung o.g. Formel geben, wenn ich das mit dem Statement von DejanVu einsetzen würde. Klammerwald.
Das mit dem Datum als Zahl hatte ich schon verstanden, deswegen ergab sich ja die Frage nach einer entsprechenden Umwandlung die arithmetisch arbeitet, und nicht stumpf die Ziffern ohne Delimiter in eine Zahl schmeißt. (Wer braucht so ein blödes Format überhaupt!? Da kann man doch nix mit machen! (Ausser vergleichen, okay.))

[Medium]

SirRufo, dein SELECT da schaut interessant aus! Eine komische Sache bekomme ich dabei aber bei täglichen Intervallen:

SQL-Code:

SELECT i.Interval, FROM_UNIXTIME( ( UNIX_TIMESTAMP( h.mDate ) DIV i.Interval ) * i.Interval ) AS intDate from history h join intervals i

liefert:

Interval	intDate
1	2015-08-06 14:15:34
10	2015-08-06 14:15:30
30	2015-08-06 14:15:30
60	2015-08-06 14:15:00
3600	2015-08-06 14:00:00
86400	2015-08-06 02:00:00

Man beachte, dass beim Raster von 86400 Sekunden die Uhrzeit 02:00:00 herauskommt, nicht wie ich gerne hätte 00:00:00. Woran liegt das?

[Sir Rufo]

Das riecht nach UTC -> MESZ Umwandlung.

[Medium]

Yessssssssssss!

Ich glaube mit dem folgenden INSERT ist genau das gewünschte erledigt! Ein Aufruf für alle Intervalle. Boah ist das hübsch!

SQL-Code:

INSERT INTO history (mID, mIntervalID, mDate, mAVG, mMAX, mMIN, mAVGCount) 
(
SELECT
  :messID as mID, 
  id AS IntervalID,
  FROM_UNIXTIME((UNIX_TIMESTAMP(:messDatum) DIV i.Interval) * i.Interval) AS messDatum,
  :messWert AS messWertAVG,
  :messWert AS messWertMAX,
  :messWert AS messWertMIN,
  1 AS mAVGCount
FROM
  intervals i 
)
ON DUPLICATE KEY UPDATE
  mAVG = (mAVG*mAVGCount + :messWert) / (mAVGCount + 1.0),
  mMAX = IF(mMAX > :messWert, mMAX, :messWert),
  mMin = IF(mMIN < :messWert, mMIN, :messWert),
  mAVGCount = mAVGCount + 1;

(Der PK liegt auf mID, mIntervalID, mDate.)

Die Spaltennamen im Sub-Select könnten natürlich entfallen, das habe ich nur für mich beim Ausprobieren gemacht.
Sehr super! Ich danke euch!! Das war Kollaboration wie sie Freude macht :)

SirRufo's Roter Kasten:
Wie könnte ich das am besten behandeln? Gibt es da entsprechende Funktionen die mir das automatisch ausgleichen könnten?

[jobo]

Zitat:

Zitat von Medium (Beitrag 1311293)

Man beachte, dass beim Raster von 86400 Sekunden die Uhrzeit 02:00:00 herauskommt, nicht wie ich gerne hätte 00:00:00. Woran liegt das?

Schau mal hier, da gibt es eine schöne Auflistung zum Beachten. Punkt 6 scheint mir in Deinem Fall interessant. Wichtig, was glaubt die DB, welche Timezone Deine Session hat.

http://stackoverflow.com/questions/1...one-set-to-utc

[Sir Rufo]

Tja ... das ist jetzt so eine Sache mit dem Zeitpunkt.

Ein Datensatz, der um 11:34:45 in Deutschland erstellt wurde und dann in Timbuktu eine Abfrage erstellt wird, was denn alles im Zeitraum 11:00:00-12:00:00 passiert ist ... der darf diesen Datensatz nicht sehen (bzw. es kommt eben darauf an, welches 11:00:00-12:00:00 er meint, lokale Zeit oder UTC-Zeit).

Die Lösung heisst, dass du alle Werte in der Datenbank als UTC speicherst und vor jeder Abfrage die Zeitpunkte immer von lokal nach UTC umwandelst

[Medium]

Das ist doch gar keine so doofe Lösung. Danke euch erneut. Ich glaube, das wird richtig gut!

[jobo]

Zitat:

Zitat von Sir Rufo (Beitrag 1311314)

Die Lösung heisst, dass du alle Werte in der Datenbank als UTC speicherst und vor jeder Abfrage die Zeitpunkte immer von lokal nach UTC umwandelst

Ich bin kein MySQL Spezialist, aber MySQL speichert intern sowieso UTC. Beim Schreiben und Lesen werden die Werte gemäß Sessioneinstellung des Clients geändert. Das kann mindestens konfigurationsabhängig (client) sein, aber auch per session/sql geändert werden.
An der Stelle würde ich jetzt mal ein paar Blicke in die Doku werfen, um unnötige oder fehlerhafte Konvertierung zu vermeiden.
Soetwas wird bei einer "vollwertigen" Clientinstallation ggF. auch bereits schon berücksichtigt. Wirft man da nur eine DLL rein, sieht es vielleicht anders aus.

[Dejan Vu]

Falls die Daten in 'Echtzeit' erfasst werden, könnte der Timestamp auch vom Server erstellt werden. Das ist dann sinnvoll, wenn die Daten über ihren Zeitstempel irgendwie korreliert werden sollen. Damit kann dann auch ein PC in Timbuktu mal total falsch laufen oder die Zeitzone verstellt sein.

Natürlich ist das etwas heikel und bringt nur etwas, wenn die Daten wirklich sofort zum Server geschickt werden.

[Sir Rufo]

Zitat:

Zitat von Dejan Vu (Beitrag 1311374)

Falls die Daten in 'Echtzeit' erfasst werden, könnte der Timestamp auch vom Server erstellt werden. Das ist dann sinnvoll, wenn die Daten über ihren Zeitstempel irgendwie korreliert werden sollen. Damit kann dann auch ein PC in Timbuktu mal total falsch laufen oder die Zeitzone verstellt sein.

Natürlich ist das etwas heikel und bringt nur etwas, wenn die Daten wirklich sofort zum Server geschickt werden.

Es kommt gar nicht mal so auf den PC an, denn der Zeitstempel wird ja von dem Messgerät geliefert. Und nun kommt es darauf an, wie genau die Uhr dort ist und welche Zeitzone dort eingestellt ist.

[Medium]

In diesem Fall liefern die Geräte nicht die Uhrzeit. Das wäre auch wirklich nicht schön, so um die 200-300 davon sekundengenau zu synchronisieren. Nein, federführend soll hier der messende PC sein. Das wird ein hübscher 19"er im klimatisierten Serverraum (inkl. Backup-Gerät - hoffentlich macht die MySQL Redundanz die hier besprochenen Dinge mit - noch nicht getestet). Insbesondere bei den Werten, die ich langsamer als sekündlich lese will ich so sicherstellen, dass ich brav auf den Intervallgrenzen lese und schreibe. In der Praxis werden hier wohl nur sekündlich, minütlich und stündlich relevant. Und wenn ein paar Werte ein paar Millisekunden nach 12:30:00 erst eintreffen, will ich die bitte auch noch auf der glatten Uhrzeit haben. So genau braucht das nicht. (Die sekündlichen Werte sind auch nur gefordert, damit kürzere Spitzen nicht im Sampling untergehen.)

[Dejan Vu]

Zitat:

Zitat von Sir Rufo (Beitrag 1311380)

Es kommt gar nicht mal so auf den PC an, denn der Zeitstempel wird ja von dem Messgerät geliefert. Und nun kommt es darauf an, wie genau die Uhr dort ist und welche Zeitzone dort eingestellt ist.

Also bei uns damals nicht. Das Messgerät hat die Messergebnisse geliefert und der PC (die SW) dann die Uhrzeit dazu gepackt und im Server abgelegt. Dann sind wir auf die Idee mit der Serverzeit gekommen, waren aber noch so dämlich, und haben die jeden PC 1x pro Minute abfragen lassen, damit sich der dann synchronisiert.

Zum Schluss haben wir den Server den Zeitstempel eintragen lassen.

PS: Wenn (und falls) mySQL mit sekundengenauen Uhrzeiten Probleme haben sollte (sofern es wirklich wichtig ist, ob eine Messung um 13:23:47,000 und nicht um 13:22:49,99997 erfolgt ist), würde ich die Uhrzeit als Ganzzahl speichern.

Du kannst Dir eine nette kleine 'Uhrzeit'-Tabelle bauen. Mit genau 86400 Einträgen. Dort stehen dann neben ID und der sekundengenauen Uhrzeit noch alle möglichen Informationen drin, z.B. die Stromkosten zudem Zeitpunkt, oder Frühschicht oder Spätschicht, oder, oder, oder. Alles, was einem zu einer Uhrzeit so einfallen kann. (Stichwort: Uhrzeit-Faktentabelle).

In deiner Messhistory-Tabelle steht dann nicht die Uhrzeit, sondern die ID (also eigentlich, wie viele Sekunden ab Mitternacht).

Wenn Du dann das Datum noch als Faktentabelle ablegst und anstatt dem Datum die Datums-ID verwendest, dann bist Du schon ziemlich nahe an einem winzig kleinen DWH.