Prüfen ob vermietbares Gerät zu Zeitpunkt/-Raum frei

Rootbob91

Aktiver Benutzer
Beiträge
27
Hallo zusammen!

Ich habe folgende DB Struktur:

Tabelle Termine, welchen man ein Gerät, was über einen Zeitraum (von Start und Ende) vermietet wird, zuordnen kann.
- ID (int, autoinc, primary)
- Start (datetime)
- Ende (datetime)
- fkGeraet (int)

Und die Tabelle Geräte wo die einzelnen zu vermietetenden Geräte drinstehen.
- ID (int, autoinc, primary)
- Name (tinytext)
- Sonstige irrelevante Daten

Nun wäre es für mich interessant zu erfahren, welche Geräte bei Eingabe eines Zeitraumes von Start- und End-Datum noch frei wären?

Leider komme ich dort nicht weiter :/.

Bisherige Ansätze und Google-Ergebnisse verliefen leider im Nichts.
Mein bisher überlegter Pseudo-Code:
Hole mir alle Geräte aus Tabelle Geräte, die nicht einem Event innerhalb des ausgewählten Zeitraumes zugeordnet sind.

Als SQL dann angefangen so:
Code:
SELECT g.* FROM Geraete g LEFT JOIN Termine t ON g.ID=t.fkGeraet WHERE (t.Start NOT BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum') AND (t.Ende NOT BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum')

Wäre sehr dankbar, wenn jemand seinen Senf dazugeben könnte :)!
 
Werbung:
Bist Du auf MySQL festgelegt?

Falls nein: PostgreSQL hat Range-Datentypen (Timestamprange, Daterange) und sog. Exclusion-Constraints und außerdem GiST-Indexe, die hier extrem hilfreich sind.

Um das mal kurz zu zeigen:

Code:
test=# create table rootbob(geraet int, termine daterange, exclude using gist(geraet with =, termine with &&));
CREATE TABLE
test=*# insert into rootbob values (1, '[2016-08-01,2016-08-31)');
INSERT 0 1
test=*# insert into rootbob values (1, '[2016-09-10,2016-09-30)');
INSERT 0 1
test=*# insert into rootbob values (2, '[2016-09-01,2016-09-30)');
INSERT 0 1
test=*# select * from rootbob ;
 geraet |  termine   
--------+-------------------------
  1 | [2016-08-01,2016-08-31)
  1 | [2016-09-10,2016-09-30)
  2 | [2016-09-01,2016-09-30)
(3 Zeilen)

Ich lasse das 'Beiwerk' mit den FK für die Geräte hier mal weg.


Wenn Du wissen willst, welche Geräte im Zeitraum belegt sind, z.B. 1. bis 5. 9., kannst Du das mit dem Contains-Operator prüfen

Code:
test=*# select * from rootbob where termine  @> '[2016-09-01,2016-09-05)';
 geraet |  termine   
--------+-------------------------
  2 | [2016-09-01,2016-09-30)
(1 Zeile)

Oder mit dem Overlapp-Operator:

Code:
test=*# select * from rootbob where termine && '[2016-09-01,2016-09-05)';
 geraet |  termine   
--------+-------------------------
  2 | [2016-09-01,2016-09-30)
(1 Zeile)

Du kannst prüfen, ob ein spezifisches Gerät belegt ist:

Code:
test=*# select * from rootbob where geraet = 1 and termine && '[2016-09-01,2016-09-05)';
 geraet | termine
--------+---------
(0 Zeilen)

test=*# select * from rootbob where geraet = 2 and termine && '[2016-09-01,2016-09-05)';
 geraet |  termine   
--------+-------------------------
  2 | [2016-09-01,2016-09-30)
(1 Zeile)

und Du kannst kein Gerät doppelt belegen:

Code:
test=*# insert into rootbob values (1, '[2016-08-20,2016-09-05)');
FEHLER:  kollidierender Schlüsselwert verletzt Exclusion-Constraint „rootbob_geraet_termine_excl“
DETAIL:  Schlüssel (geraet, termine)=(1, [2016-08-20,2016-09-05)) kollidiert mit vorhandenem Schlüssel (geraet, termine)=(1, [2016-08-01,2016-08-31)).
test=*#

All das stellt also die DB sicher, und zwar Index-basiert und damit auch bei großen Datenmengen extrem schnell.
 
Eigentlich warst du schon sehr nah an der Lösung, wenn ich mich nicht täusche musst du nur AND mit OR tauschen:
Code:
SELECT   g.*
FROM   Geraete g
LEFT JOIN Termine t
ON     g.ID = t.fkGeraet
WHERE   t.Start NOT BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum'
OR     t.Ende NOT BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum'
Mit AND würdest du nur Überschneidungen abfangen wo t.Start und t.Ende beide in deinem Zeitraum liegen. Mit OR reicht es schon, wenn sich die Zeiträume nur an einem Tag schneiden um nicht ausgegeben zu werden.

Es gibt aber noch andere Wege, z.B. mit NOT IN oder NOT EXISTS.
Code:
SELECT   g.*
FROM   Geraete g
WHERE   g.ID NOT IN (   SELECT   t.fkGeraet
             FROM   Termine t
             WHERE   t.Start BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum'
             OR     t.Ende BETWEEN 'EingabeStartDatum' AND 'EingabeEndDatum' )
 
Werbung:
Zurück
Oben