Wie berechnet man die Arbeitszeiten zwischen zwei Daten in PostgreSQL, ohne Wochenenden?

Berechnen Sie die Arbeitszeiten zwischen zwei Daten in PostgreSQL

Problemstellung

Entwerfen Sie einen Algorithmus zur Berechnung der Arbeitszeiten zwischen zwei Zeitstempeln, wobei zu berücksichtigen ist, dass dies an Wochenenden nicht der Fall ist Arbeits- und Wochentage werden von 8.00 Uhr bis 15.00 Uhr gezählt Uhr.

Lösung

Schritt 1: Arbeitsstunden pro Tag ermitteln

Arbeitszeiten als Wochentage von 8.00 bis 15.00 Uhr definieren:

WITH WorkingHours AS (
  SELECT '08:00'::time AS start_time, '15:00'::time AS end_time
)

Schritt 2: Berechnen Sie die Intervalle für jeden Tag.

Erstellen Sie eine Reihe von Intervallen für jeden Tag innerhalb den angegebenen Datumsbereich:

SELECT t_id,
       generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
FROM   t

Schritt 3: Berechnen Sie die Arbeitsstunden für jedes Intervall

Interpolieren Sie die Arbeitsstunden für jeden Tag und berechnen Sie Bruchteile der Stunden mithilfe des Bereichstyps tsrange :

SELECT t_id,
       SUM(CASE
         WHEN EXTRACT(ISODOW FROM day) < 6 THEN
           COALESCE(
             f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time),
             '0'
           )
         ELSE
           '0'
       END) AS work_time
FROM   WorkingHours
CROSS JOIN (
  SELECT t_id, day
  FROM   temp
) AS temp
GROUP  BY 1

Schritt 4: Funktion zur Bruchrechnung Stunden

Definieren Sie eine Funktion f_worktime, um gebrochene Stunden zu berechnen:

CREATE FUNCTION f_worktime(_start timestamp, _end timestamp)
RETURNS interval
LANGUAGE sql AS
$func$
SELECT COALESCE(upper(tsrange(_start::timestamp, _end::timestamp)) - lower(tsrange(_start::timestamp, _end::timestamp)), '0')::interval;
$func$
IMMUTABLE;

Beispiel

WITH t AS (
  SELECT 0 AS t_id, '2023-05-01 09:00'::timestamp AS t_start, '2023-05-01 11:30'::timestamp AS t_end
UNION ALL
  SELECT 1, '2023-05-02 10:00'::timestamp, '2023-05-02 18:00'::timestamp
UNION ALL
  SELECT 2, '2023-05-03 09:15'::timestamp, '2023-05-04 10:45'::timestamp
)
SELECT t.t_id, extract(hour from work_time) AS working_hours
FROM   t
CROSS JOIN (
  SELECT t_id, SUM(CASE WHEN EXTRACT(ISODOW FROM day) < 6 THEN f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time) ELSE '0' END) AS work_time
  FROM   WorkingHours
  CROSS JOIN (
    SELECT t_id, day
    FROM   (
      SELECT t_id,
             generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
      FROM   t
    ) AS temp
  ) AS temp
  GROUP  BY 1
) AS temp;

Ausgabe:

| t_id | working_hours |
|------|---------------|
|    0 |             3 |
|    1 |             8 |
|    2 |             3 |

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