Datum: Bereich in Bereich prüfen

[Jens Müller]

Hallo,

ich habe einen Datumbereich (tt.mm.jjjj) bis (tt.mm.jjjj) und möchte diesen mit einem Bereich in mysql abgleichen, der in 2 Spalten vorliegt.

Hiervon ist spalte1 das "von-Datum" (Spaltentyp: date) und spalte2 das "bis-Datum" (Spaltentyp: date).

Mir ist hierbei als Ergebnis lediglich wichtig, ob der aktuelle Datumsbereich und der Datumsbereich einer Tabellenzeile eine Überschneidung hat.

Wie mache ich das am effektivsten?

Ich habe das sozusagen bisher "zu Fuß" geprüft, würde aber gerne wissen, ob das auch in einer Query geht?

Tabelle:
ID----------von----------bis
1------2011-11-28-----2012-02-26
2------2011-11-10-----2012-02-11
3------2011-10-15-----2012-01-16
4------2012-01-28-----2012-02-26

Der zu prüfende Datumsbereich:
11.11.2011 bis 20.11.2011

Gewünschtes Ergebnis (ID):
2,3

Viele Grüße, Jens

[Jens Müller]

Hatte ich vergessen, anzugeben:

mysql5

[dedlfix]

Tach!

ich habe einen Datumbereich (tt.mm.jjjj) bis (tt.mm.jjjj) und möchte diesen mit einem Bereich in mysql abgleichen, der in 2 Spalten vorliegt.
Mir ist hierbei als Ergebnis lediglich wichtig, ob der aktuelle Datumsbereich und der Datumsbereich einer Tabellenzeile eine Überschneidung hat.
Wie mache ich das am effektivsten?

Zum Beispiel nimmst du dir ein Blatt Papier oder eine Datei (sowas karriertes wie Excel vielleicht) und zeichnest alle möglichen Konstellationen ein

DBvon          DBbis
von  bis
von       bis
von                bis
von                      bis
von                                bis
          von      bis
          von            bis

und so weiter. DBvon und DBbis sind deine beiden Werte aus dem DBMS, 'von' und 'bis' die beiden Daten des Prüflings. In der ersten Zeile sind beide Daten vor dem DBvon-Datum, in der zweiten fällt das 'bis' mit dem DBvon zusammen, in der dritten liegt das 'bis' zwischen DBvon und DBbis. Soweit hoffe ich, sollte das klar sein. Nun formulierst du die Bedingungen und das gewünschte Ergebnis je Zeile: Wenn 'von' < DBvon und 'bis' < DBvon dann false. Wenn 'von' immer nur vor 'bis' sein kann, kannst du das abkürzen in: Wenn 'bis' < DBvon dann false. Wenn der zweite Fall dich auch nicht interessiert, dann kannst du die ersten beiden Bedingungen zusammenfassen zu: Wenn 'bis' <= DBvon dann false.

Wenn du all diese Regeln definiert hast, kannst du Vereinfachungen suchen und die einzelnen Regeln zu einer zusammenfassen. Anschließend solltest du dir ein Testszenario mit allen Fällen bauen und prüfen, ob deine Formel allen Erwartungen entspricht.

dedlfix.

[Jens Müller]

Hi,

DBvon          DBbis
von  bis
von       bis
von                bis
von                      bis
von                                bis
          von      bis
          von            bis

und so weiter. DBvon und DBbis sind deine beiden Werte aus dem DBMS, 'von' und 'bis' die beiden Daten des Prüflings.

Ist es lt. Deiner Tabelle nicht genau umgekehrt?

von bis sind die Tabellenzeilen und DBvon DBbis ist der zu prüfende Datumszeitraum.

In der ersten Zeile sind beide Daten vor dem DBvon-Datum, in der zweiten fällt das 'bis' mit dem DBvon zusammen, in der dritten liegt das 'bis' zwischen DBvon und DBbis. Soweit hoffe ich, sollte das klar sein.

Ja. Und zwar, egal, wie herum ich das betrachte.

Nun formulierst du die Bedingungen und das gewünschte Ergebnis je Zeile

So weit, so gut.

Ich habe folgende Tabelle:

  
CREATE TABLE liste (  
  ID int(6) NOT NULL AUTO_INCREMENT,  
  von_Datum date NOT NULL,  
  bis_Datum date NOT NULL,  
  PRIMARY KEY (ID)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  
  
  
INSERT INTO liste (ID, von_Datum, bis_Datum) VALUES  
(1, '2011-11-28', '2012-02-26'),  
(2, '2011-11-28', '2012-02-12'),  
(3, '2011-11-29', '2012-02-20'),  
(4, '2011-11-28', '2012-02-26'),  
(5, '2011-11-28', '2012-02-26'),  
(6, '2011-11-30', '2012-02-26'),  
(7, '2012-01-02', '2012-02-28'),  
(8, '2012-01-01', '2012-02-28'),  
(9, '2012-01-03', '2012-02-28'),  
(10, '2011-12-01', '2012-02-29'),  
(11, '2011-12-05', '2012-02-29'),  
(12, '2011-12-05', '2012-02-29'),  
(13, '2011-12-10', '2012-03-09'),  
(14, '2011-12-10', '2012-03-09'),  
(15, '2011-12-10', '2012-03-09'),  
(16, '2011-11-27', '2012-03-09'),  
(17, '2011-12-12', '2012-03-09'),  
(18, '2011-12-12', '2012-03-09'),  
(19, '2011-12-10', '2012-03-09');  

Mit dieser Query, die den Zeitraum 30.11.2011 bis 02.12.2011 prüfen soll, erhalte ich nun genau das Gegenteil dessen, was ich möchte:

  
SELECT ID  
FROM liste  
WHERE (  
CASE  
WHEN bis_Datum < '2011-11-30'  
THEN 1  
WHEN von_Datum > '2011-12-02'  
THEN 1  
END  
)  

Ergebniss:

ID
7
8
9
11
12
13
14
15
17
18
19

Wie formuliere ich das denn richtig? Ich möchte tatsächlich alle Zeilen, die irgendeine Schnittmenge mit dem Prüfzeitraum aufweisen.

Jens

[Jens Müller]

Wie formuliere ich das denn richtig? Ich möchte tatsächlich alle Zeilen, die irgendeine Schnittmenge mit dem Prüfzeitraum aufweisen.

In jedem Fall scheint mir folgende Query die richtigen IDs in die Ergebnissmenge zu packen:

  
SELECT ID  
FROM liste  
WHERE (  
CASE  
WHEN bis_Datum < '2011-11-30'  
THEN TRUE  
WHEN von_Datum > '2011-12-02'  
THEN TRUE  
END  
) IS NULL  

Was hältst Du von der?

Jens

[dedlfix]

Tach!

Was hältst Du von der?

Was ich von ihr halte ist nebensächlich. Arbeitet sie denn in allen gewünschten und ungewünschten Fällen wie erwartet?

dedlfix.

[Jens Müller]

Was ich von ihr halte ist nebensächlich. Arbeitet sie denn in allen gewünschten und ungewünschten Fällen wie erwartet?

Finde ich nicht. Es könnte doch sein, dass Du die Lösung für korrekt arbeitend hältst, aber eine wesentlich performantere Lösung kennst. Dann wäre es schon wichtig, was Du als der in mysql Kompetentere von uns beiden davon hältst.

Ja, bisher scheint sie die Fälle wunschgemäß abzuarbeiten.

Gruß und Dank, Jens

[dedlfix]

Tach!

Was ich von ihr halte ist nebensächlich. Arbeitet sie denn in allen gewünschten und ungewünschten Fällen wie erwartet?
Finde ich nicht. Es könnte doch sein, dass Du die Lösung für korrekt arbeitend hältst, aber eine wesentlich performantere Lösung kennst. Dann wäre es schon wichtig, was Du als der in mysql Kompetentere von uns beiden davon hältst.

Ja, nebensächlich im Sinne von erst kommt die Funktion (nachweislich und am besten jederzeit wiederholbar) dann das Optimieren (unter Anwendung der Tests, damit kein falsches Funktionieren hineinoptimiert wird).

Ja, bisher scheint sie die Fälle wunschgemäß abzuarbeiten.

Nachdem das geklärt ist, kann ich meine Meinung dazu sagen. Du arbeitest bei deiner Variante mit Negationen. Das CASE ergibt TRUE, wenn das Datum außerhalb ist, ansonsten nichts (= NULL). Dieses NULL ist bei dir positiv, also innerhalb, und darauf testest du dann. Es reichen zwei einfache Vergleiche mit einem AND dazwischen: wenn PrüflingBis >= DBvon und PrüflingVon <= DBbis dann gibt es eine Überschneidung. Eine ähnliche Lösung hatte Tim, aber mit einem OR, was in einem Fall, bei dem beide Daten ober- oder unterhalb der DB-Einträge liegen, schon eine Teilbedingung wahr werden lässt und damit das Ergebnis wahr ist.

dedlfix.

[Tim]

Hallo,

Tabelle:
ID----------von----------bis
1------2011-11-28-----2012-02-26
2------2011-11-10-----2012-02-11
3------2011-10-15-----2012-01-16
4------2012-01-28-----2012-02-26

Der zu prüfende Datumsbereich:
11.11.2011 bis 20.11.2011

Gewünschtes Ergebnis (ID):
2,3

SELECT ID from tbldaten WHERE prüfdatum_von<bis OR prüfdatum_bis>von
sollte den Zweck doch erfüllen oder?
du musst dann nur die Prüfdaten entsprechend ins MySQL Format konvertieren aber das sollte ja kein Problem sein.

viele Grüße,
Tim

[Vinzenz Mai]

Hallo,

Tabelle:
ID----------von----------bis
1------2011-11-28-----2012-02-26
2------2011-11-10-----2012-02-11
3------2011-10-15-----2012-01-16
4------2012-01-28-----2012-02-26

Der zu prüfende Datumsbereich:
11.11.2011 bis 20.11.2011

Gewünschtes Ergebnis (ID):
2,3

SELECT ID from tbldaten WHERE prüfdatum_von<bis OR prüfdatum_bis>von
sollte den Zweck doch erfüllen oder?

nein, natürlich nicht - noch nicht einmal in diesem speziellen Fall:
alle Datensätze erfüllen Deine erste Bedingung

2011-11-11 < 2012-02-26

Somit wäre das Ergebnis mit Deiner Abfrage:

ID
--
 1
 2
 3
 4

was nicht gewünscht ist. Schau' Dir dedlfix' Beitrag an.

Freundliche Grüße

Vinzenz