Wie berechnet man die Arbeitszeit zwischen zwei Daten in PostgreSQL?

Arbeitsstunden zwischen zwei Daten in PostgreSQL berechnen

Bei der Berechnung der Arbeitsstunden zwischen bestimmten Zeitstempeln müssen Wochenenden und die festgelegten Arbeitsstunden berücksichtigt werden. In PostgreSQL können wir verschiedene Techniken nutzen, um diese Aufgabe zu erfüllen.

Gerundete Ergebnisse

Für bestimmte Zeitstempelbereiche:

Berücksichtigen Sie Einheiten von 1 Stunde, Brüche ignorieren. Die Formel lautet wie folgt:

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';

Für eine ganze Tabelle:

-- Table Creation and Data Insertion
CREATE TABLE t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES
  (1, '2009-12-03 14:00', '2009-12-04 09:00')
, (2, '2009-12-03 15:00', '2009-12-07 08:00')
, (3, '2013-06-24 07:00', '2013-06-24 12:00')
, (4, '2013-06-24 12:00', '2013-06-24 23:00')
, (5, '2013-06-23 13:00', '2013-06-25 11:00')
, (6, '2013-06-23 14:01', '2013-06-24 08:59');

-- Main Query
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;

Präzisere Ergebnisse

Für eine höhere Präzision verwenden Sie kleiner Zeiteinheiten, z. B. 5-Minuten-Slices.

-- Precision with 5-minute Slices
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'  -- 15.00 - interval '5 min'
GROUP  BY 1
ORDER  BY 1;

Genau Ergebnisse

Für genaue Ergebnisse bis zur Mikrosekunde behandeln Sie Start- und Endzeiten getrennt.

Postgres 8.4:

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)

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