• Sie befinden sich hier: Startseite > SQL-Backstube > Zeitliche Reservierungen

Die SQL-Backstube

Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.

03.07.2010: Zeitliche Reservierungen prüfen

Problemstellung

Es sollen Tischreservierungen geprüft werden. Der Anwender kann einen Zeitraum (von/bis Uhrzeit) eingeben und prüfen lassen, ob in dieser Zeit ein Tisch frei ist. Die SQL-Abfrage muß sicherstellen, dass es zu keinen zeitlichen Überschneidungen kommt.

Getestet mit folgenden Datenbanken:

* MySQL 5.1.30
* IBM DB2 9.1

Datenbank-Tabellen

Die Datenbank-Tabellen hierfür sehen wie folgt aus.

Listing 1:

1. /**
2. * Stammdaten Tische
3. */
4. CREATE TABLE tisch (
5. tisch_id INT NOT NULL,
6. PRIMARY KEY (tisch_id)
7. );
9. /**
10. * Tisch-Reservierungen
11. */
12. CREATE TABLE tisch_reservierung (
13. id INT NOT NULL,
14. tisch_id INT NOT NULL,
15. von TIMESTAMP NOT NULL,
16. bis TIMESTAMP NOT NULL,
17. PRIMARY KEY (id),
18. CONSTRAINT fk_tisch_reserv_01
19. FOREIGN KEY (tisch_id)
20. REFERENCES tisch (tisch_id)
21. );

Testdaten

Nachdem die Tabellen erzeugt sind, können die Stammdaten, zum Beispiel diese vier Testdatensätze für die Tische, eingefügt werden.

Listing 2:

1. /**
2. * Einfügen von vier Testdatensätzen
3. */
4. INSERT INTO tisch VALUES
5. ( 1 ), ( 2 ), ( 3 ), ( 4 );

Im nächsten Schritt werden ein paar Tischreservierungen eingefügt. Auf Basis dieser Testdaten versuchen wir später die Abfragen zu erstellen.

Listing 3:

1. /**
2. * Einfügen von sechs Testreservierungen als Multi-Row-Insert
3. */
4. INSERT INTO tisch_reservierung
5. ( id, tisch_id, von, bis ) VALUES
6. ( 1, 1, '2010-06-13 09:00:00', '2010-06-13 11:00:00'),
7. ( 2, 2, '2010-06-13 09:00:00', '2010-06-13 11:00:00'),
8. ( 3, 3, '2010-06-13 09:00:00', '2010-06-13 11:00:00'),
9. ( 4, 1, '2010-06-13 12:00:00', '2010-06-13 15:00:00'),
10. ( 5, 2, '2010-06-13 13:00:00', '2010-06-13 16:00:00'),
11. ( 6, 4, '2010-06-13 09:00:00', '2010-06-13 13:00:00');

SQL - Abfrage

Nun kommen wir zum eigentlichen Thema und zwar zu der SQL-Abfrage. Wie muss die SQL-Abfrage aussehen, die die Tische liefert, die am 13.06.2010 zwischen 11:15 und 12:45 frei sind. Hört sich im ersten Moment leichter an, als es ist. Aber fangen wir einfach mal an.

Wenn wir die schon vorhandenen Reservierungen grafisch aufbereiten, läßt sich ziemlich schnell erkennen, dass zwischen 11:15 und 12:45 nur zwei Tische frei wären. Die Tische zwei oder drei wären verfügbar.



1.) Unsere SQL-Abfrage soll sicher stellen, dass kein Reservierungsstart (von) und kein Reservierungsende (bis) im gewünschen Zeitbereich 11:15 und 12:45 liegt (Listing 4:).

Listing 4:

1. SELECT t.tisch_id
2. FROM tisch t
3. WHERE t.tisch_id NOT IN
4. ( SELECT tisch_id FROM tisch_reservierung
5. WHERE von BETWEEN '2010-06-13 11:15:00' AND '2010-06-13 12:45:00' )
6. AND t.tisch_id NOT IN
7. ( SELECT tisch_id FROM tisch_reservierung
8. WHERE bis BETWEEN '2010-06-13 11:15:00' AND '2010-06-13 12:45:00' )
9. ORDER BY t.tisch_id;
11. +----------+
12. | tisch_id |
13. +----------+
14. | 2 |
15. | 3 |
16. | 4 |
17. +----------+
18. 3 rows in set (0.00 sec)
20. mysql>

Überraschenderweise liefert die Abfrage (Listing 4:) neben den erwarteten Tischen zwei und drei auch der Tisch vier. Der erste Tisch wird korrekt als belegt erkannt. Aber leider wird auch den Tisch vier als frei gemeldet, obwohl doch klar ist, dass nur die Tische zwei und drei frei sind. Was ist passiert? Die Reservierung des Tisches vier überlappt den gewünschten Bereich komplett und kein Start-/Endewert liegt innerhalb des abgefragten Zeitbereichs! Unser erste Abfrage greift also zu kurz.

2.) Starten wir also einen zweiten Versuch und berücksichtigen nun zusätzlich, ob die abgefragten Start-/Endezeiten innerhalb eines bereits reservierten Zeitraumes liegen.

Listing 5:

1. SELECT t.tisch_id
2. FROM tisch t
3. WHERE t.tisch_id NOT IN
4. ( SELECT tisch_id FROM tisch_reservierung
5. WHERE von BETWEEN '2010-06-13 11:15:00' AND '2010-06-13 12:45:00' )
6. AND t.tisch_id NOT IN
7. ( SELECT tisch_id FROM tisch_reservierung
8. WHERE bis BETWEEN '2010-06-13 11:15:00' AND '2010-06-13 12:45:00' )
9. AND t.tisch_id NOT IN
10. ( SELECT tisch_id FROM tisch_reservierung
11. WHERE '2010-06-13 11:15:00' BETWEEN von AND bis )
12. AND t.tisch_id NOT IN
13. ( SELECT tisch_id FROM tisch_reservierung
14. WHERE '2010-06-13 12:45:00' BETWEEN von AND bis )
15. ORDER BY t.tisch_id;
17. +----------+
18. | tisch_id |
19. +----------+
20. | 2 |
21. | 3 |
22. +----------+
23. 2 rows in set (0.13 sec)
25. mysql>

Mit unserer zweiten Abfrage kommen wir ans Ziel. Diese Abfrage prüft mit Hilfe von BETWEEN alle vier "Zeitecken" auf Überschneidungen mit anderen Reservierungen ab. So läßt sich sicherstellen, dass es keine "doppelten" Reservierungen gibt.

https://twiedmann.de