Verdichtungstabellen für Messwertarchiv erstellen

[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 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 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 von jobo:

Zitat von Uwe Raabe:

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:
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 von Medium:

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