Wie kann ich die Arbeitszeiten zwischen Datumsangaben in PostgreSQL unter Berücksichtigung von Wochenenden und bestimmten Arbeitszeiten berechnen?

Berechnung der Arbeitsstunden zwischen Datumsangaben in PostgreSQL

Einführung

Bestimmen Sie in verschiedenen Szenarien die Die Anzahl der Arbeitsstunden zwischen zwei Zeitstempeln kann sich in Bereichen wie der Gehaltsabrechnung und der Terminplanung als entscheidend erweisen. In PostgreSQL erfordert diese Berechnung eine sorgfältige Berücksichtigung wochentags- und zeitspezifischer Parameter. Dieser Artikel skizziert eine umfassende Lösung unter Berücksichtigung folgender Kriterien:

• Wochenenden (Samstage und Sonntage)sind von der Arbeitszeit ausgenommen.
• Arbeitszeiten sind definiert als Montag bis Freitag, 8 bis 15 Uhr.
• Bruchzahlig Stunden sind in die Berechnung einzubeziehen.

Lösung

Methode 1: Gerundete Ergebnisse für nur zwei Zeitstempel

Dieser Ansatz arbeitet mit Einheiten von 1 Stunde und ignoriert gebrochene Stunden. Es handelt sich um eine einfache, aber weniger präzise Methode.

Abfrage:

SELECT count(*) AS work_hours
FROM   generate_series (timestamp '2013-06-24 13:30'
                      , timestamp '2013-06-24 15:29' - interval '1h'
                      , interval '1h') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00';

Beispieleingabe:

2013-06-24 13:30, 2013-06-24 15:29

Ausgabe:

2

Methode 2: Gerundete Ergebnisse für eine Tabelle mit Zeitstempeln

Dieser Ansatz erweitert die vorherige Methode, um eine Tabelle mit Zeitstempelpaaren zu verarbeiten.

Abfrage:

SELECT t_id, count(*) AS work_hours
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00'
GROUP  BY 1
ORDER  BY 1;

Methode 3: Präziser Berechnung

Für eine feinkörnigere Berechnung können kleinere Zeiteinheiten berücksichtigt werden.

Abfrage:

SELECT t_id, count(*) * interval '5 min' AS work_interval
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:55'
GROUP  BY 1
ORDER  BY 1;

Beispiel Eingabe:

| t_id | t_start                | t_end                  |
|------|-------------------------|-------------------------|
| 1    | 2009-12-03 14:00:00    | 2009-12-04 09:00:00    |
| 2    | 2009-12-03 15:00:00    | 2009-12-07 08:00:00    |
| 3    | 2013-06-24 07:00:00    | 2013-06-24 12:00:00    |
| 4    | 2013-06-24 12:00:00    | 2013-06-24 23:00:00    |
| 5    | 2013-06-23 13:00:00    | 2013-06-25 11:00:00    |
| 6    | 2013-06-23 14:01:00    | 2013-06-24 08:59:00    |

Ausgabe:

| t_id | work_interval |
|------|----------------|
| 1    | 1 hour         |
| 2    | 8 hours        |
| 3    | 0 hours        |
| 4    | 0 hours        |
| 5    | 6 hours        |
| 6    | 1 hour         |

Methode 4: Genaue Ergebnisse

Dieser Ansatz liefert exakte Ergebnisse mit Mikrosekundengenauigkeit. Es ist komplexer, aber recheneffizienter.

Abfrage:

WITH var AS (SELECT '08:00'::time  AS v_start
                  , '15:00'::time  AS v_end)
SELECT t_id
     , COALESCE(h.h, '0')  -- add / subtract fractions
       - 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 (  -- count full hours, similar to above solutions
   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;

Diese umfassende Lösung geht auf die Notwendigkeit ein, Arbeitszeiten in PostgreSQL genau und effizient zu berechnen.

Das obige ist der detaillierte Inhalt vonWie kann ich die Arbeitszeiten zwischen Datumsangaben in PostgreSQL unter Berücksichtigung von Wochenenden und bestimmten Arbeitszeiten berechnen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!