Zeitspanne nur einmalig für eine Summe werten

[Nenaro]

Guten Morgen zusammen!

Aktuell plagt mich ein Problem, dessen Lösung ich noch nicht gefunden habe.
Im Grund geht es um eine Art Zeiterfassung, in der parallel mehrere Zeiten an einem Tag zu einem Nutzer eingetragen sein können. Die vergangenen Zeitspannen dieser Einträge dürfen aber in Summe nicht doppelt gewertet werden.

Ein Beispiel:
1. Eintrag: 16.10.2015 08:00 bis 12:00
2. Eintrag: 16.10.2015 09:00 bis 13:00
3. Eintrag: 16.10.2015 15:00 bis 17:00

Nun muss ich eine Summe der Dauer des 16.10.2015 ziehen, in der keine Zeitabschnitte doppelt gerechnet werden dürfen. Im Beispiel oben darf die Summe nicht 10 Stunden ergeben sondern 7 Stunden.

Die Felder in denen die Zeitangaben eingetragen werden sind DATETIME Format.

Ich wäre über Ideen und Gedankenanstöße sehr dankbar.
Beste Grüße, Nenaro

 

[ukulele]

Also zunächst käme mir nur ein Weg in den Sinn: Alle Tageszeiten in Intervalle aufbröseln und Intervalle, die von deinen Zeitspannen abgedreckt werden, addieren. Das ist nicht sehr elegant und vor allem mit MySQL ein Krampf. Vieleicht fällt mir noch was ein.

 

[Nenaro]

Diesen Ansatz bin ich schon angegangen. Da ein minütlicher Intervall sein muss habe ich 1440 Abschnitte pro Nutzer und Tag.
Insgesamt wird der ganze Vorgang mitunter sehr träge bis ich dann Werte erhalte. Auf einen Nutzer und einen Monat gesehen geht das noch recht schnell, aber sobald ich ein Ergebnis für 3 Monate und 5 Nutzer möchte kommt die Kaffeemaschine ins Spiel (;

 

[akretschmer]

Mit MySQL hast Du da bei der Wahl der DB in der Tat recht tief ins Klo gegriffen. Ich zeige das mal mit PostgreSQL und seinen Möglichkieten, hier Range-Typen und Window-Funktionen.

Du hast:

privat=*# select * from arbeitszeiten ;
 mitarbeiter_id |  von_bis
----------------+-----------------------------------------------
  1 | ["2015-10-16 08:00:00","2015-10-16 12:00:00")
  1 | ["2015-10-16 09:00:00","2015-10-16 13:00:00")
  1 | ["2015-10-16 15:00:00","2015-10-16 17:00:00")
(3 rows)

Und nun

privat=*# select *, von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis)) as vorgaenger, upper(von_bis)-lower(von_bis) as stunden, coalesce(upper(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis))) - lower(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis)))) from arbeitszeiten ;
 mitarbeiter_id |  von_bis  |  vorgaenger  | stunden  | coalesce
----------------+-----------------------------------------------+-----------------------------------------------+----------+----------
  1 | ["2015-10-16 08:00:00","2015-10-16 12:00:00") |  | 04:00:00 |
  1 | ["2015-10-16 09:00:00","2015-10-16 13:00:00") | ["2015-10-16 09:00:00","2015-10-16 12:00:00") | 04:00:00 | 03:00:00
  1 | ["2015-10-16 15:00:00","2015-10-16 17:00:00") | empty  | 02:00:00 |
(3 rows)

In vorgaenger hast die vorhergenden Arbeitszeit bzw. das was sich diese mit der gegenwärtigen überlappt, das macht hier der * - Operator. Er vergleicht zwei Ranges und gibt das zurück, was beide gemeinsam haben. In Stunden die aktuellen Stunden für die Zeile, in der coalesce-Spalte das, was aus der vorher berechneten Überlappung an Stunden abzuziehen ist.

Hier noch mal etwas besser:

privat=*# select *, von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis)) as vorgaenger, upper(von_bis)-lower(von_bis) as stunden, coalesce(upper(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis))) - lower(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis))),'0minutes'::interval) as abzug from arbeitszeiten ;
 mitarbeiter_id |  von_bis  |  vorgaenger  | stunden  |  abzug
----------------+-----------------------------------------------+-----------------------------------------------+----------+----------
  1 | ["2015-10-16 08:00:00","2015-10-16 12:00:00") |  | 04:00:00 | 00:00:00
  1 | ["2015-10-16 09:00:00","2015-10-16 13:00:00") | ["2015-10-16 09:00:00","2015-10-16 12:00:00") | 04:00:00 | 03:00:00
  1 | ["2015-10-16 15:00:00","2015-10-16 17:00:00") | empty  | 02:00:00 | 00:00:00
(3 rows)

Und nun summiert:

privat=*# select sum(stunden-abzug) from (select *, von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis)) as vorgaenger, upper(von_bis)-lower(von_bis) as stunden, coalesce(upper(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis))) - lower(von_bis * lag(von_bis) over (partition by mitarbeiter_id order by lower(von_bis))),'0minutes'::interval) as abzug from arbeitszeiten ) bar;
  sum
----------
 07:00:00
(1 row)

Fragen? Fragen!

 

[Nenaro]

Traumhafte Lösung akretschmer! Nur ist die Wahl der DB systembedingt auf MySQL bereits seit einigen Jahren festgelegt, weil darin die Informationen von unterschiedlichen Systemen zusammengetragen werden.

 

[akretschmer]

Danke ;-)

Nun ja, ist halt jetzt mehr oder weniger Dein Problem. MySQL kann nur sehr, sehr wenig im Vergleich zu 'richtigen' Datenbanken. Das galt übrigens schon vor einigen Jahren, das ist (meiner Meinung nach) keine wirkliche Entschuldigung. Egal. Das ist natürlich Eure Entscheidung, was (auf lange Sicht) teurer ist: ein Schnitt und Migration auf z.B. PG oder weitere viele Jahre rumwurschteln ...

PS.: eigentlich sollten sich die Zeiten ja nicht überschneiden dürfen, oder? Das könnte man in PG mit einem Constraint lösen.

privat=*# create table arbeitszeiten(mitarbeiter_id int, von_bis tsrange, exclude using gist(mitarbeiter_id with =, von_bis with &&));
CREATE TABLE
privat=*# insert into arbeitszeiten values (1, '[2015-10-16 08:00:00,2015-10-16 12:00:00)');
INSERT 0 1
privat=*# insert into arbeitszeiten values (1, '[2015-10-16 15:00:00,2015-10-16 17:00:00)');
INSERT 0 1
privat=*# insert into arbeitszeiten values (1, '[2015-10-16 09:00:00,2015-10-16 13:00:00)');
ERROR:  conflicting key value violates exclusion constraint "arbeitszeiten_mitarbeiter_id_von_bis_excl"
DETAIL:  Key (mitarbeiter_id, von_bis)=(1, ["2015-10-16 09:00:00","2015-10-16 13:00:00")) conflicts with existing key (mitarbeiter_id, von_bis)=(1, ["2015-10-16 08:00:00","2015-10-16 12:00:00")).
privat=*#

MySQL kann keine Check-Constraint und erst recht nicht solche doch recht komplexen Constraints.

Das mal nur so als 'Blick übern Tellerrand', was abseits von MySQL so Stand der Technik ist.

 

[ukulele]

In MSSQL würde ich mich an einen Lösungsversuch mit CTE wagen, das geht aber in MySQL nicht. Ich kann dir eigentlich nur zum Holzhammer raten: Berechne es gar nicht Live. Schreibe dir einen Trigger der bei Eingabe eines neuen Zeitraums auf Überschneidungen prüft und entweder die Daten direkt anpasst, die Eingabe verbietet oder zumindest eine weitere Tabelle mit Anpassungen füllt. Das ist nicht elegant, sollte aber robust genug sein.

 

[Nenaro]

Danke ukulele. Glaube ich werde so in der Richtung die Lösung "erwurschteln". Und sollte ein Wert in der Vergangenheit aus irgendeinem Grund durch ein angeschlossenes System verändert werden, dann muss eine Art Plausibilitätsprüfung danach extern ausgelöst werden.
Live-Werte reichen im Verlauf des aktuellen Tages. Historische Werte dürfen einen Stand von Zeit x haben.

Für eine generelle Umstellung auf z.B. PG fehlt aktuell leider wirklich die Zeit - und wenn erstmal alles läuft, dann wird nicht der Bedarf gesehen es zu ändern.
Glaube darin liegt die Berechtigung des Seins dieser Systeme (;

Danke euch für die Antworten und dem "Blick übern Tellerrand"
Nenaro

 

[akretschmer]

In MSSQL würde ich mich an einen Lösungsversuch mit CTE wagen, das geht aber in MySQL nicht

ACK. Meine Range-Typen hier sind nur das Sahnehäubchen, der Kern der Lösung ist die lag()-Funktion.

 

[akretschmer]

Für eine generelle Umstellung auf z.B. PG fehlt aktuell leider wirklich die Zeit - und wenn erstmal alles läuft, dann wird nicht der Bedarf gesehen es zu ändern.

Das ist halt das Problem: man zementiert die schlechte Lösung. Je länger man damit lebt, umso teurer wird irgendwann das Erwachen...