Comment puis-je calculer les heures de travail entre les dates dans PostgreSQL, en tenant compte des week-ends et des heures de travail spécifiques ?

Calcul des heures de travail entre des dates dans PostgreSQL

Introduction

Dans divers scénarios, déterminer le Le nombre d’heures de travail entre deux horodatages peut s’avérer essentiel dans des domaines tels que la paie et la planification. Dans PostgreSQL, ce calcul nécessite un examen attentif des paramètres spécifiques au jour de la semaine et à l'heure. Cet article présente une solution globale, prenant en compte les critères suivants :

• Les week-ends (samedi et dimanche) sont exclus des heures de travail.
• Les heures de travail sont défini comme du lundi au vendredi, de 8h à 15h pm.
• Les heures fractionnaires sont à inclure dans le calcul.

Solution

Méthode 1 : Arrondi Résultats pour seulement deux horodatages

Cette approche fonctionne sur des unités de 1 heure, ignorer les heures fractionnées. C'est une méthode simple mais moins précise.

Requête :

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 &amp;lt;= '14:00';

Exemple d'entrée :

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

Sortie :

2

Méthode 2 : Résultats arrondis pour une table d'horodatages

Cette approche étend la méthode précédente pour gérer une table de paires d'horodatages.

Requête :

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;

Méthode 3 : Plus précise Calcul

Pour un calcul plus fin, des unités de temps plus petites peuvent être envisagées.

Requête :

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;

Exemple Entrée :

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

Sortie :

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

Méthode 4 : Résultats exacts

Cette approche fournit des résultats exacts avec une précision de la microseconde. C'est plus complexe mais plus efficace sur le plan informatique.

Requête :

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;

Cette solution complète répond au besoin de calculer les heures de travail avec précision et efficacité dans PostgreSQL.

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!