Wie berechnet man die Arbeitszeiten zwischen zwei Daten in PostgreSQL genau?

So berechnen Sie die Arbeitszeit zwischen zwei Daten in PostgreSQL

Die Herausforderung

Die Berechnung der Arbeitszeit zwischen zwei Daten kann komplex sein, insbesondere wenn man Faktoren berücksichtigt wie Wochenenden und bestimmte Arbeitszeiten. In diesem Beitrag untersuchen wir verschiedene Ansätze mit PostgreSQL, um diese Aufgabe effizient zu bewältigen.

Wochenenden und Arbeitszeiten berücksichtigen

Nehmen wir an, dass unsere Arbeitszeiten von Montag bis Freitag zwischen 8 und 8 Uhr sind :00 Uhr und 15:00 Uhr. Mit dieser Definition können wir die Arbeitsstunden zwischen zwei beliebigen Zeitstempeln wie folgt berechnen:

1. Wochenenden ignorieren: Überprüfen Sie, ob die Daten auf Wochenenden (Samstag oder Sonntag) fallen, und schließen Sie sie aus aus der Berechnung.
2. Stunden abschneiden: Konvertieren Sie die Zeitstempel so, dass sie nur Datum und Uhrzeit enthalten innerhalb der Arbeitszeit. Beispielsweise würde „2023-03-08 19:30:00“ zu „2023-03-08 15:00:00“ werden.
3. Stunden berechnen: Berechnen Sie die Differenz zwischen den verkürzte Endzeit und verkürzte Startzeit. Dadurch erhalten Sie die Gesamtzahl der Arbeitsstunden.

PostgreSQL-Lösungen

Gerundete Ergebnisse

Um gerundete Ergebnisse zu erhalten, können wir verwenden PostgreSQLs Funktion „generate_series()“ zum Generieren einer Reihe von 1-Stunden-Intervallen innerhalb des Arbeitszeitbereichs. Anschließend zählen wir die Anzahl der in Frage kommenden Intervalle, die in den angegebenen Zeitraum fallen. Hier ist eine Beispielabfrage:

SELECT count(*) AS work_hours
FROM   generate_series('2023-03-08 14:00', '2023-03-09 09:00' - interval '1 hour', interval '1 hour') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

Mehr Präzision

Für präzisere Ergebnisse können Sie kleinere Zeiteinheiten verwenden, z. B. 5-Minuten-Schritte. Die folgende Abfrage liefert Ergebnisse mit einer Genauigkeit von 5 Minuten:

SELECT count(*) * interval '5 min' AS work_interval
FROM   generate_series('2023-03-08 14:01', '2023-03-09 09:00' - interval '5 min', interval '5 min') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

Genaue Ergebnisse

Für exakte Ergebnisse können Sie einen differenzierteren Ansatz wählen, indem Sie den Start separat behandeln und Ende des Zeitrahmens. Hier ist eine Abfrage, die präzise Intervallergebnisse auf die Mikrosekunde genau liefert:

SELECT t_id
     , COALESCE(h.h, '0')
       - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6
               AND t_start::time > v_start
               AND t_start::time < v_end
         THEN t_start - date_trunc('hour', t_start)
         ELSE '0'::interval END
       + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6
               AND t_end::time > v_start
               AND t_end::time < v_end
         THEN t_end - date_trunc('hour', t_end)
         ELSE '0'::interval END                 AS work_interval
FROM   t CROSS JOIN var
LEFT   JOIN (
   SELECT t_id, count(*)::int * interval '1h' AS h
   FROM  (
      SELECT t_id, v_start, v_end
           , generate_series (date_trunc('hour', t_start)
                            , date_trunc('hour', t_end) - interval '1h'
                            , interval '1h') AS h
      FROM   t, var
      ) sub
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= v_start
   AND    h::time <= v_end - interval '1h'
   GROUP  BY 1
   ) h USING (t_id)
ORDER  BY 1;

Vergleich der Ansätze

Die verschiedenen vorgestellten Ansätze bieten unterschiedliche Grade an Präzision und Leistung.

Abgerundete Ergebnisse: Diese Methode ist einfach zu implementieren und liefert vernünftige Schätzungen, insbesondere wenn die Eingabezeiten nahe an den Grenzen der Arbeitszeit liegen Bereich.
Mehr Präzision: Dieser Ansatz bietet eine bessere Genauigkeit durch die Verwendung kleinerer Zeiteinheiten. Die Auswirkungen auf die Leistung sind in den meisten Szenarien minimal.
Genaue Ergebnisse: Diese Methode ist komplexer und erfordert zusätzliche Berechnungen. Es liefert die genauesten Ergebnisse, kann jedoch mit einem höheren Rechenaufwand verbunden sein.

Fazit

Die Wahl des geeigneten Ansatzes hängt von der erforderlichen Präzision und den Leistungsbeschränkungen ab. Für den allgemeinen Einsatz bietet die „More Precision“-Methode mit 5-Minuten-Schritten ein gutes Gleichgewicht zwischen Genauigkeit und Effizienz. In Fällen, in denen es jedoch auf absolute Genauigkeit ankommt, kann der Ansatz „Exakte Ergebnisse“ verwendet werden, um präzise Intervalle auf die Mikrosekunde genau zu liefern.

Das obige ist der detaillierte Inhalt vonWie berechnet man die Arbeitszeiten zwischen zwei Daten in PostgreSQL genau?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!