Arbeitszeiten berechnen

[mike-man]

Hallo ich habe eine simple Datenbank, welche das einstempeln sowie das ausstempeln erfasst.

Dies ist jeweils gekennzeichnet mit anfang = 'Y' oder 'N' bzw. ende = 'Y' oder 'N'.

Mein Problem ist jetzt, dass die Differenz dazwischen berechne möchte und somit die Zeit zwischen ein-und ausstempeln berechnen möchte.
Das klappt soweit auch ganz gut, aber jetzt tritt das Problem auf, das ich sozusagen Zeiten überspringe.

Ein Beispiel:
Daten:
1,2024-04-02 19:10:25,Y,N,2024-04-02
1,2024-04-03 05:10:25,N,Y,2024-04-03
1,2024-04-03 16:10:25,Y,N,2024-04-03
1,2024-04-03 23:59:25,N,Y,2024-04-03
1,2024-04-04 12:10:26,Y,N,2024-04-04
1,2024-04-04 20:00:23,N,Y,2024-04-04


Als Ausgabe sollte folgendes rauskommen:

emp_id, anfangs_zeit, end_zeit
1 2024-04-02 19:10:25,2024-04-03 05:10:25
1 2024-04-03 16:10:25,2024-04-03 23:59:25

etc.

Bei mir kommt aber folgendes raus.
emp_id, anfangs_zeit, end_zeit
1 2024-04-02 19:10:25,2024-04-03 05:10:25
1 2024-04-03 16:10:25,2024-04-03 23:59:25
1 2024-04-02 19:10:25,2024-04-03 23:59:25
1 2024-04-02 19:10:25,2024-04-04 20:00:23

etc.

ich habe soetwas vor ein paar Monaten zig mal gelöst aber stehe diesmal einfach auf dem schlauch und komme nicht weiter.
ich würde mich über jede Hilfe freuen.

Das Datenbankdesign ist nur ein Prototyp und nur zum schnellen Testen gedacht das wird nochmal überarbeitet

 

[castorp]

Etwas in der Art vielleicht?

select anfang.emp_id,
       anfang.zeitpunkt as beginn,
       ende.zeitpunkt as ende
from stempelzeiten anfang
  left join lateral (
      select t.zeitpunkt
      from stempelzeiten t
      where anfang.emp_id = t.emp_id
        and t.ende = 'Y'
        and t.zeitpunkt > anfang.zeitpunkt
      order by t.zeitpunkt
      limit 1
  ) ende on true
where anfang.anfang = 'Y'

Wobei ich die zwei Spalten mit Ende/Anfang überflüssig finde. Eine Spalte "Art" mit "anfang" und "ende" als Werte fände ich sinnvoller.

 

[ukulele]

Hier mal eine Variante mit Window-Function anstelle eines Joins:

WITH tabelle(emp_id,datumzeit,anfang,ende) AS (
    SELECT 1,'2024-04-02 19:10:25',1,0 UNION ALL
    SELECT 1,'2024-04-03 05:10:25',0,1 UNION ALL
    SELECT 1,'2024-04-03 16:10:25',1,0 UNION ALL
    SELECT 1,'2024-04-03 23:59:25',0,1 UNION ALL
    SELECT 1,'2024-04-04 12:10:26',1,0 UNION ALL
    SELECT 1,'2024-04-04 20:00:23',0,1
    ), t AS (
    SELECT    emp_id,
            (CASE WHEN anfang = 1 THEN datumzeit ELSE NULL END) AS anfang_datumzeit,
            lead(CASE WHEN ende = 1 THEN datumzeit ELSE NULL END) OVER (PARTITION BY emp_id ORDER BY datumzeit) AS ende_datumzeit
    FROM    tabelle
    )
SELECT    t.*
FROM    t
WHERE    t.anfang_datumzeit IS NOT NULL

Knackpunkte hat man in der Praxis manchmal wenn z.B. Kommen statt Gehen gestempelt wurde, das sollte in dem Fall aber korrekt NULL sein. Das hängt aber immer von den Eigenheiten der Zeiterfassung ab, unsere hat da so Einige.

PS: Vorsicht, wenn du den Zeitunterschied kalkulierst. datediff() rechnet nicht Minuten in Stunden um und rundet dann sondern betrachtet nur den Wechsel im Stundenanteil, zumindest bei MSSQL. Siehe: SQL Server DATEDIFF hour rounding

 

[castorp]

PS: Vorsicht, wenn du den Zeitunterschied kalkulierst. datediff() rechnet nicht Minuten in Stunden um und rundet dann sondern betrachtet nur den Wechsel im Stundenanteil, zumindest bei MSSQL

Nein, das Problem gibt es in Postgres nicht. Die Differenz zweier TIMESTAMP Werte liefert ein INTERVAL in dem alle "Teile" korrekt sind