Comment calculer les heures de travail entre deux dates dans PostgreSQL, hors week-end ?

Calculer les heures de travail entre 2 dates dans PostgreSQL

Énoncé du problème

Concevoir un algorithme pour calculer les heures de travail entre deux horodatages, en considérant que les week-ends ne sont pas les jours ouvrés et les jours de la semaine sont comptés de 8h à 15h pm.

Solution

Étape 1 : Identifier les heures de travail par jour

Définir les heures de travail comme les jours de semaine de 8h à 15h :

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

Étape 2 : Calculer les intervalles pour chaque jour

Créez une série d'intervalles pour chaque jour dans le plage de dates donnée :

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

Étape 3 : Calculer les heures de travail pour chaque intervalle

Interpoler les heures de travail pour chaque jour et calculer les heures fractionnaires en utilisant le type de plage 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

Étape 4 : Fonction pour calculer le fractionnaire Heures

Définir une fonction f_worktime pour calculer des heures fractionnaires :

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;

Exemple

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;

Sortie :

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!