Comment calculer avec précision les heures de travail entre deux dates dans PostgreSQL ?

Comment calculer les heures de travail entre 2 dates dans PostgreSQL

Le défi

Le calcul des heures de travail entre deux dates peut être complexe, surtout si l'on prend en compte des facteurs comme les week-ends et des horaires de travail spécifiques. Dans cet article, nous explorerons plusieurs approches utilisant PostgreSQL pour gérer efficacement cette tâche.

Prendre en compte les week-ends et les heures de travail

Supposons que nos heures de travail soient du lundi au vendredi, entre 8 et :00h et 15h00. En utilisant cette définition, nous pouvons calculer les heures de travail entre deux horodatages comme suit :

1. Ignorer les week-ends : Vérifiez si les dates tombent le week-end (samedi ou dimanche) et excluez-les. du calcul.
2. Tronquer les heures : Convertissez les horodatages pour inclure uniquement la date et l'heure dans heures de travail. Par exemple, "2023-03-08 19:30:00" deviendrait "2023-03-08 15:00:00".
3. Calculer les heures : Calculez la différence entre les l'heure de fin tronquée et l'heure de début tronquée. Cela vous donnera le nombre total d'heures de travail.

Solutions PostgreSQL

Résultats arrondis

Pour obtenir des résultats arrondis, nous pouvons utiliser Fonction generate_series() de PostgreSQL pour générer une série d'intervalles d'une heure dans la plage des heures de travail. Nous comptons ensuite le nombre d'intervalles éligibles tombant dans la période spécifiée. Voici un exemple de requête :

SELECT count(*) AS work_hours
FROM   generate_series('2023-03-08 14:00', '2023-03-09 09:00' - interval '1 hour', interval '1 hour') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

Plus de précision

Pour des résultats plus précis, vous pouvez utiliser des unités de temps plus petites, telles que des incréments de 5 minutes. La requête suivante fournit des résultats avec une précision de 5 minutes :

SELECT count(*) * interval '5 min' AS work_interval
FROM   generate_series('2023-03-08 14:01', '2023-03-09 09:00' - interval '5 min', interval '5 min') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

Résultats exacts

Pour des résultats exacts, vous pouvez adopter une approche plus nuancée en gérant séparément le début et fin du délai. Voici une requête qui fournit des résultats d'intervalle précis à la microseconde :

SELECT t_id
     , COALESCE(h.h, '0')
       - 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 (
   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;

Comparaison des approches

Les différentes approches présentées offrent différents niveaux de précision et de performances.

Résultats arrondis : Cette méthode est simple à mettre en œuvre et fournit des estimations raisonnables, notamment lorsque les temps de saisie sont proches des limites des heures de travail plage.
Plus de précision : Cette approche offre une meilleure précision en utilisant des unités de temps plus petites. L'impact sur les performances est minime dans la plupart des scénarios.
Résultats exacts : Cette méthode est plus complexe et nécessite des calculs supplémentaires. Il fournit les résultats les plus précis mais peut entraîner un coût de calcul plus élevé.

Conclusion

Le choix de l'approche appropriée dépend de la précision requise et des contraintes de performances. Pour une utilisation générale, la méthode « Plus de précision » avec des incréments de 5 minutes offre un bon équilibre entre précision et efficacité. Cependant, pour les cas où la précision absolue est primordiale, l'approche des « Résultats exacts » peut être utilisée pour fournir des intervalles précis à la microseconde près.

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!