Select mit Zeitdifferenz

Hallo,
im Moment bin ich total überfordert mit dem Erstellen einer (für mich) zu
komplizierten Abfrage.
Verschiedene Geräte schreiben Gerätestände in die Tabelle Events. Meine
Probelm ist folgendes. Ich möchte nun eine Liste aller Geräte erhalten, die
zwischen einem Bestimmten Zeitraum mindestens 5 Minuten den Zustand 1
(ID_evType) hatten


Ich habe drei relevante Tabellen

Device
=====
ID integer
name varchar

EvType
======
ID Integer
name varchar

Events
=====
ID integer
ID_Device
ID_EvType
name varchar
timestamp timestamp


Beispiel Einträge Events
================
ID | Gerät | Event | Timestamp
1| 1 | 1 | 2007-08-26 08:00:34
2| 2 | 2 | 2007-08-26 08:00:45
3| 3 | 1 | 2007-08-26 08:00:51
4| 1 | 2 | 2007-08-26 09:00:12

Danke im Voraus für jede Hilfe oder Anregung

Re: Select mit Zeitdifferenz

markus berger wrote:
> im Moment bin ich total überfordert mit dem Erstellen einer (für mich)
> zu komplizierten Abfrage.
> Verschiedene Geräte schreiben Gerätestände in die Tabelle Events. Meine
> Probelm ist folgendes. Ich möchte nun eine Liste aller Geräte erhalten,
> die zwischen einem Bestimmten Zeitraum mindestens 5 Minuten den Zustand
> 1 (ID_evType) hatten

Definiere was "5 Minuten ... haben" bedeutet. Ohne Unterbrechung am
Stück, oder insgesamt? Gibt es dazwischen andere Events, oder keine
Events? Gäbe es sonst in einem (bekannten) Intervall Events?

Das vorgehen hängt sehr davon ab was die Daten bedeuten. Also von der
Semantik deiner Datenbank.

> Ich habe drei relevante Tabellen
>
[...]
> Beispiel Einträge Events
> ================
> ID | Gerät | Event | Timestamp
> 1| 1 | 1 | 2007-08-26 08:00:34
> 2| 2 | 2 | 2007-08-26 08:00:45
> 3| 3 | 1 | 2007-08-26 08:00:51
> 4| 1 | 2 | 2007-08-26 09:00:12

Ich denke für den Anfang reicht es die Tabelle Events zu betrachten.
Wie würdest Du vorgehen wenn Du die Tabelle als Ausdruck vor Dir liegen
hättest.

Gruß

Heiko
--
http://portal.richler.de/ Namensportal zu Richler
http://www.richler.de/ Heiko Richler: Computer - Know How!
http://www.richler.info/ private Homepage

Re: Select mit Zeitdifferenz

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)

Re: Select mit Zeitdifferenz

> Definiere was "5 Minuten ... haben" bedeutet.
5 Miuten bedeutet, dass der Zustand mindestes 5 Minuten an einem Stück
gedauert hat.

> Wie würdest Du vorgehen wenn Du die Tabelle als Ausdruck vor Dir liegen
> hättest.

händisch bzw mit einer Programmiersprache würde ich folgendermassen
vorgehen:

1. Abfrage erstellen mit allen Eintrag die den Statur 1 für den angegebenen
Zeitraum für das ausgewählte Gerät haben also so was ähliches:

Select * from Events where timestamp >= $[DateTimeFrom] and timestamp
<=$[DateTimeTo] and Event = 1 and Device = $[id]

2. anschliesend würde ich mit einer Schleife überprüfen
wähle den nächsten Datensatz dieses Gerätes, der nicht den Status 1 hat,
Subtrahiere die Zeit und überprüfe ob es mehr als 5 Minuten sind

wenn kein nächster zutreffender Datensatz gefunden wurde, Subtrahiere
die Zeit von der aktuellen Zeit
ende Schleife

Gruss
..
>
> Gruß
>
> Heiko
> --
> http://portal.richler.de/ Namensportal zu Richler
> http://www.richler.de/ Heiko Richler: Computer - Know How!
> http://www.richler.info/ private Homepage
>

Re: Select mit Zeitdifferenz

markus berger wrote:
>> Definiere was "5 Minuten ... haben" bedeutet.
> 5 Miuten bedeutet, dass der Zustand mindestes 5 Minuten an einem Stück
> gedauert hat.

Ja, aber woran ist das bei den Events zu erkennen. Dort gibt es doch
keine Dauer.

>> Wie würdest Du vorgehen wenn Du die Tabelle als Ausdruck vor Dir liegen
>> hättest.
>
> händisch bzw mit einer Programmiersprache würde ich folgendermassen
> vorgehen:
>
> 1. Abfrage erstellen mit allen Eintrag die den Statur 1 für den
> angegebenen Zeitraum für das ausgewählte Gerät haben also so was ähliches:
>
> Select * from Events where timestamp >= $[DateTimeFrom] and timestamp
> <=$[DateTimeTo] and Event = 1 and Device = $[id]

Also jeden Datensatz der in das Muster passt als Anfangszeitpunkt
annehmen, oder?

> 2. anschliesend würde ich mit einer Schleife überprüfen
> wähle den nächsten Datensatz dieses Gerätes, der nicht den Status 1 hat,
> Subtrahiere die Zeit und überprüfe ob es mehr als 5 Minuten sind

Formaler ausgedrückt suchst Du die kleinste obere Schranke:

select min(timestamp) -- kleinste
from Events
where Event<>1 and Device = $[id]
and timestamp > Treffer.timestamp -- obere Schranke

Wenn Du eine aktuelle Version von MySql hast kannst Du im ersten Select
die zweite Anfrage als Subselect einbetten.

> wenn kein nächster zutreffender Datensatz gefunden wurde, Subtrahiere
> die Zeit von der aktuellen Zeit
> ende Schleife

Wenn es keine Daten, also keine obere Schranke, gibt, dann bekommst Du null.
Den gewünschten Vergleich mit der aktuellen Zeit würde ich dann mit
einer zweiten Abfrage holen und mit union anhängen.

Beachten musst Du noch, dass Du eventuell mehrere Treffer zum selben
Zeitraum findest. Möchtest Du das? Ich nehme an Du möchtest nur den
Anfang des Zeitraums. Also einen dessen größte untere Schranke einen
anderen Status hat ;)

So eine Abfrage mit Unterabfragen kann sehr aufwendig sein. Wenn Du die
Möglichkait hast das Model anzupassen, überlegen wie Du das Datenmodell
verbessern kannst.

Der Hinweis von Andreas Kretschmer einen Trigger einzusetzen ist eine
Möglichkeit dazu. Du würdest z.B. bei jedem neuen Datensatz eine Dauer
ausrechnen, wie lange der selbe Status schon gekommen ist. Diese
Auswertung ist etwas weniger aufwendig und die Abfragen sehr viel einfacher.

Heiko

--
http://portal.richler.de/ Namensportal zu Richler
http://www.richler.de/ Heiko Richler: Computer - Know How!
http://www.richler.info/ private Homepage

Re: Select mit Zeitdifferenz

Andreas
--
q: why do so many people take an instant dislike to mysql?
a: it saves time (oicu in #postgresql)
Explaining the concept of referential integrity to a mysql user is like
explaining condoms to a catholic (Shadda in #postgresql)