Zeitspannen ausrechnen

[stefaan]

Servus!

Es geht nicht um einfache Zeitspannen, sondern es ist ein bisschen gefinkelter:
Ich habe eine Tabelle mit 2 Spalten, datum (zeitstempel) und Temperatur.
In dieser Tabelle sind ca. 5000 Einträge eines Tages (ca 3-4 Einträge pro Minute) enthalten. Die Temperatur bewegt sich so normal zwischen 50°C und 60°X, kann aber auch nach oben ausbrechen (ist nicht so schlimm).

Ich suche nun eine Möglichkeit, Zeitspannen herauszufinden, in denen die Temperatur über 60°C liegt.
Mir fällt nur die Idee ein, dass ich mir den ersten Zeitpunkt heraussuche und dann jeden Datensatz durchschaue, ob die darauf folgende Minute auch noch einen Wert über 60°C hat. Dann habe ich einen Anfangs- und Endwert und und kann mir daraus bequem die Zeitspanne ausrechnen.

Nun die Frage: Geht das auch irgendwie leichter? Ev. direkt in der DB?

Datenbank: Sqlite, Programm in Lazarus (Delphi).

Danke!

Grüße, Stefan

[Beatkiller]

Da du dich über die genaue Struktur (Tabellen- und Spaltennamen, Datentypen) der Tabelle ausschweigst (von welchem DBMS sprechen wir?), gehe ich mal von folgender Struktur aus:

Tabelle temperaturen
Spalte 1: zeitstempel (TIMESTAMP)
Spalte 2: temperatur (INT)


SELECT zeitstempel FROM temperaturen WHERE temperatur > 60;

[stefaan]

Servus!

Die Abfrage, wie ich alle Temperaturen über 60°C rausbekomme, ist nicht das Problem.

Nochmals klarer, die Tabelle schaut so aus


zeit | temperatur
23.11.3008 12:34:34 | 50
23.11.3008 12:34:53 | 55
23.11.3008 12:35:12 | 65
23.11.3008 12:35:32 | 65
...
23.11.3008 12:39:45 | 65
23.11.3008 12:40:18 | 50


Herausholen will ich die Zeitspanne, wo die Temperatur über 60°C ist:
23.11.3008 12:35 - 23.11.3008 12:39

Die einzige Möglichkeit, die mir einfällt, ist Zeile für Zeile der Abfrage temperatur > 60 durchzugehen und zu prüfen, ob der Eintrag noch dieselbe Minute oder eine Minute später ist.
Ist er mehr als eine Minute später, habe ich die Endzeit dieser Zeitspanne.

Gibts da bessere Lösungen?

Danke!

Grüße, Stefan

[Beatkiller]

Ah, jetzt habe ich verstanden, was du machen willst. Ich denke nicht, das du das mit reinem SQL hinbekommen wirst. Du hast ja dann auch das nächste Problem, das du damit keine wirklichen Bereiche raus holst, sondern nur bestimmte Datensätze nach bestimmten Kriterien. Ich kenne keine Möglichkeit, eine derartige Logik in SQL ab zu bilden.

Du müsstest dafür eine Hochsprache hernehmen und über alle Datensätze iterieren. Damit wären wir dann aber Offtopic.

[BLUESCREEN3D]

Am besten wäre es, wenn du die Zeitspannen schon beim Einfügen der Temperaturen zusammenstellst. Ist das möglich?

Wie viele Messwerte sind/werden das denn und wie lange dauert es bisher, die Zeitspannen wie von dir beschrieben zusammenzustellen (das ist immerhin eine sehr einfache Lösung, die in O(n) arbeitet)?

Ansonsten wird es etwas umständlich, da die Messwerte unregelmäßig reinkommen. Kann es vorkommen, dass in einer Minute auch mal kein Messwert vorhanden ist? Z.B. weil der Server abgeschaltet war?
Dich würden vor allem die Messwerte über 60°C interessieren, deren Vorgänger der letzen Minute alle unter 60°C sind und die, deren Nachfolger der nächsten Minute alle unter 60°C sind. Das sind jeweils die Anfänge und Enden der Zeitspannen, die du suchst.

[jan61]

Moin,

2 "ABER":
1. Ich glaube nicht, dass der unten genannte SQL von SQLite unterstützt wird, ich habs in einer "richtigen" Datenbank (PostgreSQL) ausprobiert.
2. Ohne DB-Redesign geht es meiner Meinung nach nicht.

Das Problem an der derzeitigen Struktur ist nämlich, dass Du mit SQL-Mitteln nicht rausfinden kannst, ob nicht zwischen 2 Werten > 60 Grad einer <= 60 Grad steckt, damit wäre eine Differenzmessung hinfällig.

Ich habe mal die Tabelle um eine ID (serial) erweitert, damit ich bei der Auswertung weiß, welche Meßwerte aufeinanderfolgen. So sieht dann die Tabelle mit gefüllten Werten aus:

id | temp | zeit
----+------+---------------------
1 | 53 | 2008-11-23 15:30:02
2 | 52 | 2008-11-23 15:30:43
3 | 61 | 2008-11-23 15:31:05
4 | 58 | 2008-11-23 15:31:36
5 | 63 | 2008-11-23 15:31:58
6 | 62 | 2008-11-23 15:32:18
7 | 64 | 2008-11-23 15:32:51
8 | 57 | 2008-11-23 15:33:14
9 | 63 | 2008-11-23 15:33:58
10 | 62 | 2008-11-23 15:34:18
11 | 57 | 2008-11-23 15:34:46
So der SQL:

select max(t2.zeit) - min(t1.zeit) as zeitraum, t1.zeit as startzeit
from temps as t1, temps as t2
where t1.id<t2.id
and t1.temp>60
and t2.temp>60
and t2.id+1=(select min(id) from temps where temp<61 and id>t1.id)
and t1.id-1=(select max(id) from temps where temp<61 and id<t1.id)
group by t1.zeit;und so das Ergebnis:

zeitraum | startzeit
----------+---------------------
00:00:53 | 2008-11-23 15:31:58
00:00:20 | 2008-11-23 15:33:58
Ich habe hier also alle Zeiträume ausgespuckt, wo mindestens 2 Messwerte über 60 lagen. Ein 3. "ABER" gibts auch noch: das funktioniert nicht für den aktuellen Zeitraum, wenn der letzte Meßwert nicht < 61 war.

Jan

EDIT: Das 2. Aber nehme ich zurück, es geht auch ohne ID, allerdings verstärken sich dafür meine Zweifel, ob SQLite das packt ;-) So sollte es auch klappen:

select max(t2.zeit) - min(t1.zeit) as zeitraum, t1.zeit as startzeit
from temps as t1, temps as t2
where t1.zeit<t2.zeit
and t1.temp>60
and t2.temp>60
and t2.zeit<(select min(zeit) from temps where temp<61 and zeit>t1.zeit)
and t1.zeit=(select min(zeit) from temps where temp>60 and zeit<t2.zeit
and zeit>
(select max(zeit) from temps where temp<61 and zeit<t1.zeit))
group by t1.zeit;

[stefaan]

Servus Jan!

Danke für deine Antwort, ich habs inzwischen "herkömmlich" gelöst.
Die Daten kommen aus CSV-Dateien (jede Minute 2-3 Werte) und der User kann mehrere dieser CSV-Dateien zur Auswertung auswählen (z.B. alle Dateien einer Woche oder eines Monats).
Die CSV-Dateien werden dann eingelesen und sortiert, in die DB werden nur die Werte geschrieben, die die Grenzwerte überschreiten. In einem weiteren Durchlauf wird dann die Auswertung aus der DB durchgeführt. Am längesten dauert das Schreiben in die DB, die paar überschrittenen Grenzwerte durchzuchecken dauert nicht wirklich lange.

Danke für eure Unterstützung!

Grüße, Stefan

[jan61]

Moin,

da interessiert mich aber doch, wie Du (ohne die "unter 60 Grad"-Werte) rauskriegst, ob die Temperatur tatsächlich durchgängig über 60 war - die Messwerte scheinen ja nicht regelmäßig zu kommen und Werte unter 60 hast Du ja nicht mehr.

Jan

[stefaan]

Servus!

Ineffizient, aber funktionabel:
-) Der 1. Wert der gefilterten Datensätze ist Startwert.
-) Ich vergleiche die Differenz zwischen den zwei aufeinander folgenden Datensätzen (im Unix-Timestamp), also < 60 Sekunden (in Delphi). Ist dies gegeben, gehören diese Datensätze zusammen
-) Ist eine Periode zu Ende, wird der nächste Datensatz wieder automatisch als Anfang genommen.

Dies schreibe ich in eine neue DB mit den Spalten start und ende, dann kann ich mir daraus die Zeitspannen ausrechnen.

Die Abfragen gehen eh sehr schnell im Verhältnis zu den inserts.

Grüße, Stefan

[msi]

ich würds auf diese art machen:



select temperatur,zeit, (select min(zeit_prev) from t where temperatur<60 and zeit>outer_table.zeit )
from t as outer_table
where (temperatur_prev<60 or temperatur_prev is null) and temperatur > 60


termperatur_prev und zeit_prev sind jeweils die werte des datensatzes davor.
entweder schema abändern um diesen in jedme dazensatz zu haben oder
mit einem join anfügen, was jedoch ohne auto_increment primary key nicht so leicht ist.