Select Table mit Datumsbereichen

[Ben2003]

Hallo,

in einer Tabelle sind verschiedene Datumsangaben enthalten. z.B.

CREATE TABLE TMP_Datum (
ID int NOT NULL AUTO_INCREMENT,
ID_NBKst int DEFAULT NULL,
DatumStart date DEFAULT NULL,
DatumEnd date DEFAULT NULL,
PRIMARY KEY (ID)
)

Folgende Beispieldaten sind vorhanden:


INSERT INTO TMP_Datum(ID, ID_NBKst, DatumStart, DatumEnd) VALUES
(1, 1, '2019-09-01', '2019-10-31'),
(2, 1, '2019-06-01', '2019-06-30');

Wie man sehe kann, sind zwischen den einzelnen Datums-Bereichen Lücken (z.B. vom 2019-07-01 - 2019-08-31)

Wenn als zu testender Bereich der 2019-01-01 bis 2019-12-31 angegeben wird, soll herauskommen:

ID, ID_NbKst, DatumStart, DatumEnd
------------------------------------------------
0, 1, '2019-01-01', '2019-05-31'
2, 1, '2019-06-01', '2019-06-30'
0, 1, '2019-07-01', '2019-08-31'
1, 1, '2019-09-01', '2019-10-31'
0,1,'2019-11-01', '2019-12-31'

Diese Tabelle wird benötigt, um weitergehende Abfragen und Berechnungen ausführen zu können.

Mir fällt nur folgende Lösung ein:

1. Neue Tabelle erstellen z.B. Tab_BerechnetDatum
2. Insert- Update-Trigger auf die Tabelle TMP_Datum erstellen mit der Funktion mit Insert bei jedem Anlegen bzw. Aktualisieren von Einträgen (in der Tabelle TMP_Datum) die komplette Tabelle Tab_BerechnetDatum neu zu füllen

Das Füllen der neuen Tabelle könnte mit Hilfe einer For-Next-Schleife o.ä. erfolgen.

Gibt es noch einen anderen Lösungsansatz ohne Einsatz einer zusätzlichen Tabelle? Das Füllen der Tabelle Tab_BerechnetDatum ist je nach Umfang der vorhandenen Einträge entsprechend aufwendig.

Da MySql keine Tabelle als Rückgabewert von einer Funktion akzeptiert kann auch keine solche in einer Funktion generiert und als Return zurückgegeben werden.

 

[akretschmer]

also, ich glaube, Dein Ergebniss paßt nicht ganz zu Deinen Daten. Du suchst doch die Lücken, oder? Laß uns mal sehen:

test=*# select * from d;
         von_bis         
-------------------------
 [2019-09-01,2019-10-31)
 [2019-06-01,2019-06-30)
(2 rows)

Das sind Deine Daten. Hier fehlt auch die Info, ob die Grenzen inclusive oder exclusive sein sollen. Du suchst nun zusammenhängende Bereiche innerhalb 2019, die davon nicht abgedeckt sind:

test=*# with x as (select * from generate_series('2019-01-01'::date, '2019-12-31'::date, '1day'::interval) as d), y as (select *, (d::date - lag(d::date,1) over (order by d)) as diff from x left join d on d.von_bis @> x.d::date where von_bis is null), z as (select *, sum (case when diff is null or diff > 1 then 1 else null end) over (order by d) as period from y) select min(d), max(d), period from z group by period;;
          min           |          max           | period
------------------------+------------------------+--------
 2019-01-01 00:00:00+01 | 2019-05-31 00:00:00+02 |      1
 2019-06-30 00:00:00+02 | 2019-08-31 00:00:00+02 |      2
 2019-10-31 00:00:00+01 | 2019-12-31 00:00:00+01 |      3
(3 rows)

Tabelle x stellt alle Tage des Jahres bereit
Tabelle y die Tage, die davon nicht in Deinem Bereich sind
Tabelle z berechnet daraus die Perioden

und zum Schluß ermittle ich aus den einzelnen Perioden min und max. Das sind dann die Bereiche aus 2019, die nicht in Deiner Tabelle erfaßt sind.

Und ja: PostgreSQL und RANGE-Typen, weil das das alles vereinfacht ...

 

[Ben2003]

Hallo akretschmer,

auch wenn der Code nicht für MySQL Server geeignet ist, bin ich dadurch auf eine Idee gekommen, wie man es auch in MySQL lösen kann:

Auf dieser Webseite wird der fehlende Befehl "generate_series" vorgestellt:
generate_series() equivalent in MySQL

 

[akretschmer]

Na prima, Danke für das Feedback.

 

[Ben2003]

Nachtrag:

Nachteilig für die Ersatz-Lösung in MySQL ist, dass immer eine View mit einer ausreichenden Zahlenkolonne existieren muss, die als Quelle herangezogen werden kann.

z.B.:

create view generator as
Select 0 union Select 1 union Select 2 ....

Anschließend kann man mit Hilfe dieser Liste alle weiteren Datums-Listen generieren und diese beliebig ausfiltern.

 

[akretschmer]

Tja, kann ja nicht alles so gut sein wie PostgreSQL, oder?

 

[dabadepdu]

Nachtrag:

Nachteilig für die Ersatz-Lösung in MySQL ist, dass immer eine View mit einer ausreichenden Zahlenkolonne existieren muss, die als Quelle herangezogen werden kann.

z.B.:

create view generator as
Select 0 union Select 1 union Select 2 ....

Das Beispiel mit Select 0 union .. ist zwar eine mögliche Lösung, aber es geht mit Hilfe von Variablen auch ohne "ausreichend Zahlenkolonnen" oder das Union Gebastel. Im Link wird ja gezeigt wie.