Problem mit MySQL-Abfrage

[Marvin Esse]

Hallo,

ich brauche mal Eure Hilfe wie ich eine bestimmte Abfrage auf eine Tabelle lösen kann:

Die Tabelle hat drei Felder "jahr", "monat" und "dauer", sowie weitere Felder, die aber für das Problem nicht relevant sind. Die Felder sind vom Typ "VARCHAR", was die Sache womöglich noch etwas erschwert. "dauer" gibt die Anzahl in Monaten an.

Die Abfrage soll jetzt alle Einträge finden, die innerhalb eines vorgegebenen Zeitraums liegen.

Das klappt soweit mit dem folgenden Select: (In $beginn und $ende steht dann sowas wie "2016-02" bzw. "2018-05")

select * from tabelle where concat_ws('-',jahr,monat) >= '$beginn' AND concat_ws('-',jahr,monat) <= '$ende'"

Meinm Problem ist, ich möchte aber auch die Einträge finden, die sich überschneiden, also wo zwar Jahr und Monat vor dem Bereich liegen, aber durch die Dauer in den Bereich hineinragen. Also soll die Abfrage auch den Eintrag jahr=2015, monat=11, dauer=6 finden.

Wie müsste der Select aussehen?

LG Marvin

[dedlfix]

Tach!

Die Tabelle hat drei Felder "jahr", "monat" und "dauer", [...] Die Felder sind vom Typ "VARCHAR", was die Sache womöglich noch etwas erschwert. "dauer" gibt die Anzahl in Monaten an.

Ja, Varchar ist Mist, weil du die Dauer zum Monat hinzurechnen must und beim Jahreswechsel ein Problem bekommst. Mit Datumswerten wäre das kein Problem, weil MySQL Intervalle hinzurechnen kann.

Meinm Problem ist, ich möchte aber auch die Einträge finden, die sich überschneiden, also wo zwar Jahr und Monat vor dem Bereich liegen, aber durch die Dauer in den Bereich hineinragen. Also soll die Abfrage auch den Eintrag jahr=2015, monat=11, dauer=6 finden.

Wie müsste der Select aussehen?

Das ist erst die letzte zu klärende Frage. Davor kommt noch, wie du das Ende auf das Nicht-Datum addieren möchtest. Du kannst zwar temporär ein Datum erzeugen, da das Intervall hinzufügen und dann wieder einen String draus machen, um mit deinen Varchars zu vergleichen, aber schön geht anders. Der Vergleich ist daran noch das einfachste, es muss Anfang oder Ende (oder beides => normales OR kein XOR) im Bereich von $beginn und $ende liegen.

dedlfix.

[Marvin Esse]

Hallo und vielen Dank für die superschnelle Antwort.

Vielleicht habe ich doch bereits eine Lösung gefunden...

SELECT *
FROM tabelle
WHERE (concat(jahr,monat) between '$beginn' AND '$ende') OR (period_add(concat(jahr,monat),dauer) between '$beginn' AND '$ende')

LG Marvin

[dedlfix]

Tach!

Vielleicht habe ich doch bereits eine Lösung gefunden...

Sieht so aus.

SELECT *
FROM tabelle
WHERE (concat(jahr,monat) between '$beginn' AND '$ende') OR (period_add(concat(jahr,monat),dauer) between '$beginn' AND '$ende')

PERIOD_ADD() ... was es nicht alles gibt in MySQL.

dedlfix.

[Rolf b]

Hallo,

bin auch perplex. Ich arbeite hauptsächlich mit MS SQL, das ist dagegen ja das reinste Sparta. Und PERIOD_ADD ist dazu noch recht gutmütig, es akzeptiert Zahlen wie Strings und für die Dauer wird 5, '05' und ' 5' akzeptiert. PERIOD_ADD(201613, 5) ergibt 201706, das ist ein guter Fallback für einen 'falschen' Monat. Diese Gutmütigkeit geht natürlich auf Kosten der Geschwindigkeit.

Die Gutmütigkeit endet aber, wenn du für den 1. Parameter von PERIOD_ADD nicht exakt das Format einhältst. PERIOD_ADD(20161, 5) oder PERIOD_ADD('20161', 5) ergibt 20606, und PERIOD_ADD('2016 1', 5) ergibt 202109. Hier musst Du also auf deine DB achten, dass sie korrekte Werte enthält.

So. Und nun zu den echten Problemen.

1. Je nachdem, was Du haben willst, hat Deine Abfrage das klassische "einen daneben" Problem. Meint: Wenn die Periode in 201604 beginnt und 2 Monate dauert, dann liefert PERIOD_ADD 201606. April bis Juni sind 3 Monate! Du musst also das Ergebnis des PERION_ADD als "erster Monat der nicht mehr im Intervall ist" interpretieren. Das passt aber nicht mit dem between-Test zusammen.

2. Du findest nicht alles.

   Annahme: in der Datenbank steht: Jahr=2016, Monat=01, Dauer=12. Dein Beginn ist 201604, dein Ende ist 201609. D.h. dein Intervall in der Datenbank beginnt vor $beginn und endet nach $ende. Deine Query wird das nicht finden. Wenn man deine Anforderung wörtlich nimmt: "Innerhalb eines Zeitraums", dann ist das ok. Aber ich befürchte, dass Du diesen Fall als Treffer haben willst.

   Willst Du ihn haben, dann verwende die unten gezeigte Abfrage. Ob Du mit < oder <= arbeiten musst, bzw. mit > oder >=, hängt von deinen Anforderungen ab. Stelle Dir diese Fragen:

   • Links vom AND: Ist ein Datenbanksatz 201604 (mit beliebiger Dauer) noch ein Treffer, wenn $ende = 201604? Ja: verwende <=, nein: verwende <
   • Rechts vom AND: Ist ein Datenbanksatz 201604 mit Dauer 01 ein Treffer, wenn $beginn = 201605? Ja: verwende >=, Nein: verwende >

SELECT *
FROM tabelle
WHERE (concat(jahr,monat) < '$ende') AND (period_add(concat(jahr,monat),dauer) > '$beginn')

Die Entscheidungen zu </<= und >/>= lösen automatisch auch das mögliche "eins daneben" Problem.

Gruß Rolf

[MudGuard]

Hi,

PERIOD_ADD(201613, 5) ergibt 201706, das ist ein guter Fallback für einen 'falschen' Monat. PERIOD_ADD(20161, 5) oder PERIOD_ADD('20161', 5) ergibt 20606,

ist doch konsistent. 20161 ist der 61. Monat im Jahr 201, bzw. der 1. Monat im Jahre 206. 5 dazu ergibt Jahr 206 Monat 06.

cu,
Andreas a/k/a MudGuard

[Rolf b]

Lol, hast recht. Computer tun eben immer nur, was man ihnen sagt, und nicht das, was sie sollen.