Forum Programmation.autre champs "durée" et postgres

Posté par  .
Étiquettes :
0
21
juil.
2005
D'après vous, quelle est la meilleure méthode pour gérer
des durées avec PostgreSql ?

Je m'explique, j'ai une table de pointages:
-un id (pk)
-un numéro de personne
-une date (le jour concerné)
-une heure d'arrivée (timestamp sans tz)
-une heure de départ (ts sans tz)
-une durée (ts sans tz)

Dans cette table, il y a soit une plage d'heures soit une durée.

Mon souci est quand une plage va par exemple de 22h00 à 01h00.
Le problème ne vient pas du fait qu'il y ait deux lignes dans la
table mais que la soustraction "normale" heurefin-heuredebut me
donne -22 et pas 2 pour le premier jour !
Et la syntaxe '24:00:00' n'est pas permise dans pg...

J'ai pensé à appeler une fonction avec les 3 champs qui me
renverrait la bonne durée mais j'ai peur que le fait qu'elle
soit à un bas niveau fasse méchamment tomber les perfs !
Surtout que cette vue servira de base à d'autres jointures
assez tordues (du genre gérer les 2 passages heure d'été/hiver
en rajoutant +/-1 sur la durée !
Tiens d'ailleurs, vous géreriez ça comment ?

Et sinon, rien à voir mais pourquoi pas une rubrique "db" dans la section programmation des forums ?
  • # Je ne sais pas si c'est la bonne méthode, mais....

    Posté par  . Évalué à 3.

    Logiquement, si tu peux avoir le cas de quelqu'un qui arrive à 22H00 et part à 01H00, il faudrait récupérer dans ta base, non seulement l'heure de départ et celle d'arrivée, mais aussi le jour... Sinon, ça ne peut pas fonctionner.
    Pour ce qui est de la durée, effectivement, un champ de type timezone ne peut dépasser les 24H. Il faut donc créer un nouveau type pour ça.
    Je n'ai pas mes sources sous la main, mais en gros, voici comment j'ai procédé :
    - J'enregistre les heures de départ et d'arrivée sous forme de texte à 5 caractères (heure + séparateur + minutes).
    - J'ai fait une fonction de conversion de cette chaîne vers des entiers (pour avoir un nombre entier de minutes) et l'inverse, en plpgSQL, mais on doit pouvoir le faire en C pour améliorer les perfs (?).
    Ce n'est pas trop compliqué à faire, et je te conseille éventuellement de lire la doc de postgres concernant la fonction "create type" de façon à voir comment l'appliquer à ton cas.
    Si tu as besoin de plus d'infos, fais-le savoir dans ce thread.
    Voilà, en espérant que ça aide.
    P.S. : côté perfs, je n'ai pas à me plaindre, mais je ne gère qu'une centaine de personnes, pas plus, par le biais de la chaîne apache + php + postgreSQL.
    • [^] # Re: Je ne sais pas si c'est la bonne méthode, mais....

      Posté par  . Évalué à 1.

      Pour ce qui est de la tronche de la table, je vais réflechir au fait
      d'enregistrer une période par ligne plutôt que d'en avoir deux
      en cas de chevauchement (mais je dois faire des stats par jour férié
      alors j'ai pensé à y inclure le jour, enfin bon, à voir...).

      C'est simplement qu'après avoir peiné 15 ans sur des bases
      conséquentes dénormalisées à outrance, j'ai enfin la chance de
      pouvoir créer une base (une 50aine de tables) from scratch, j'en
      profite pour la faire bien d'équerre !! :-)

      Sinon, oui, je vais regarder pour les types "maison", c'est vrai que
      c'est pas mal cette possibilité d'extension...

      Enfin, ça peut peut-être servir à d'autres, j'ai fait 2 tests de perfs,
      la table contient 100.000 lignes, le serveur est un amd 2GHz avec
      512Mo de ram et un disque IDE de 80 Go et os=debian testing:

      La première requête suivante prend 0,3 sec (lancée plusieurs
      fois pour annuler l'effet de cache) :

      select
      id_pers,
      sum(duree)
      from
      vu_heur_reel_duree
      group by
      id_pers

      La vue appellée étant:

      create or replace view vu_heur_reel_duree(id_pers,jour,duree) as
      select
      hr.id_pers,
      hr.jour,
      case
      when (duree is not null) then (hr.duree - '00:00:00')
      when (hr.heure_fin >= hr.heure_deb) then
      (hr.heure_fin - hr.heure_deb)
      else ('23:59:59' - hr.heure_deb + '00:00:01')
      end
      from
      tb_heur_reel hr;

      Avec la bidouille de rajouter une seconde dans l'expression, ça
      marche...

      Le 2eme test, je l'ai fait avec une fonction que voila:

      create or replace function fc_elapsed_time(
      time without time zone,
      time without time zone,
      time without time zone)
      returns time without time zone as '
      declare duration alias for $1;
      declare start_time alias for $2;
      declare end_time alias for $3;
      begin
      if (duration is null) then
      if (end_time >= start_time) then
      return end_time - start_time;
      else
      -- hack to handle substract from midnight
      return ''23:59:59'' - start_time + ''00:00:01'';
      end if;
      else
      return duration;
      end if;
      return;
      end;
      ' language 'plpgsql';

      Et la requête (sur la table directement):

      select
      id_pers,
      sum(fc_elapsed_time(duree,heure_deb,heure_fin))
      from
      tb_heur_reel
      group by
      id_pers;

      Ca renvoie évidemment la même chose, mais ça met 2 secondes
      soit 6 fois plus !

      La différence est insignifiante sur de petits volumes (qq milliers de
      lignes) mais l'écart se creuse quand on atteint les volumes de
      "production".

      Comme je vois que tu bosses aussi sur un soft de pointage, as-tu
      prévu qq chose pour les changements heure été/hiver ?
      • [^] # Re: Je ne sais pas si c'est la bonne méthode, mais....

        Posté par  . Évalué à 2.

        En fait, je parlais d'une centaine de personnes gérées par cette appli, pour arrondir, mais dans ma table servant à renseigner les heures de pointage, j'ai décomposé les informations de cette façon :
        - un champ id_pers (int)
        - un champ date de pointage (date)
        - un champ est_matin (booleen)
        - l'heure d'arrivée (char(5))
        - l'heure de départ (char(5))
        - Le temps travaillé dans la demi-journée (char(5))
        - Une observation.
        - Un champ qui indique si cette demi-journée est travaillée ou non (booléen).
        Le booléen sert à différencier le matin de l'après-midi.
        La clef primaire se calcule sur les 3 premiers champs.
        Par ailleurs, j'ai un trigger qui intervient après mise à jour et qui permet de fixer la valeur de la demi-journée à ce que doit (je suis fonctionnaire, et de part les conventions passées entre l'état et les syndicats, il y a certaines règles à appliquer, je ne vais pas rentrer dans le détail).
        Une période de pointage s'étale entre le 01/09 et le 31/08 (année scolaire). Sur cette période, les agents titulaires ont automatiquement une période de travail équivalente, sur laquelle ils ne peuvent intervenir que pour en changer la quotité de travail (personnes à temps partiels). La complexité intervient pour les gens qui sont à temps partiel que par périodes, par ex : 6 mois à 80% et 6 mois à temps plein. Bref. Pour les agents non titulaires, il faut qu'ils entrent les dates de début et de fin de leur contrat, dans une table à part, ainsi que la quotité de travail associée. Chaque contractuel est soumis au régimé des 35H par semaines tantis que les agents titulaires ont 1593 Heures à réaliser sur le total de la période.
        Je n'ai visiblement pas les mêmes soucis que toi, car :
        - Nous ne pointons que sur les heures d'ouverture "standard" régie plus ou moins par des contraintes matérielles : les portes et grilles n'ouvrent qu'à 07H le matin, et nous sommes obligés de partir avant 19H30 à cause des alarmes.
        - De ce fait, et comme nous sommes sur le même site, je n'ai aucunement à gérer les heures d'hiver ou d'été. Chacun renseigne son heure de départ ou d'arrivée à la main.
        - Pour le problème des jours fériés, j'ai conçu une table jour_fériés qui renferme les 11 jours avec leur libellé, car je trouvais ça joli ;-) !
        Cette table est utilisée par un trigger pour calculer la durée attribuée à la journée selon les cas.
        Quoi qu'il en soit, pour me faciliter le travail, lorsqu'un agent se voit attribuer une période de travail, qu'il soit contractuel ou titulaire, les lignes correspondante sont automatiquement ajoutées dans la table de pointage, pour tous les jours concernés, c'est à dire du lundi matin au samedi matin inclus. De cette façon, mon application, développée en php, certes, n'a à gérer que des mises à jour.
        Enfin, j'ai remarqué par expérience que les gens ne pointent pas quand ils devraient, mais quand ils n'ont rien d'autre à faire. Par exemple, Mme Michu, arrivée à 08H03, et partant manger vers 12H07, se rendra compte dans l'après-midi, juste avant de partir, qu'elle a failli oublier cette @!#%! de pointeuse, et elle renseignera allégrement qu'elle est arrivée à 08H00 et est partie manger à 12H10. (ce qui lui fait un bonni de 6 min, bref).
        Enfin, ce n'est pas le propos. J'espère que ce petit débroussaillage te sera utile.
        J'ai mis pas mal de temps pour en arriver à ce système, mais il fonctionne pour nos besoins, à quelques bugs près que je corrigerais fin août.
        Si tu as besoins de plus d'infos, tu sais où me trouver !
        Sur ce, bon courage !
        • [^] # Re: Je ne sais pas si c'est la bonne méthode, mais....

          Posté par  . Évalué à 2.

          Merci pour tous ces détails !

          Pour la table des jours fériés, j'ai fait la même chose (avec un petit
          bouton sur le formulaire de màj pour insérer les jours standards
          d'une année).

          Je vais peut-être aussi utiliser plus de triggers, ça peut éviter de
          couteuses jointures (pléonasme ... :-) car dans le genre exception
          à la con, j'ai par exemple: si le personne a une certaine fonction
          (ET à la date voulue, car ça bouge...) et si elle bosse entre 23h00
          et 07h00 alors son compte d'heure sera un forfait de 3h00 et non
          pas 08h00 ! (c'est un forfait pour des éducateurs de nuit).

          Et je ne veux *pas* avoir de code spécifique au calcul dans l'appli,
          tout doit venir de la bdd, pour éventuellement faire des extractions
          directes via odbc sans se taper de champs "calculés" par le client
          (qu'il soit logiciel ou humain :-).

          Cette gestion de planning est ma première appli php. Autant j'aurais
          pu torcher ça en 2 semaines avec un Delphi sous Windows, j'ai
          estimé le tout à environ 2 mois de taf. Comme ça, une migration des
          postes clients sous un *nix est envisageable !

          Mon gros taf actuellement est d'écrire un browser générique de
          table (liste/tri/filtre/ajout/supp/modif/impression). Ne connaissant
          pas les limites du php (parce que les fonctionnalités c'est facile, il
          suffit de lire les docs !), je perds beaucoup de temps à valider
          toutes mes routines de bas-niveau (perfs entre autres...). Mine de
          rien, ça fait déja + de 2000 lignes de code (oui, je sais, il y a des
          libs php qui existent, mais je crois que je vais plus vite que de
          trouver comment elles marchent, et puis ça forme !).

          Pour faciliter les pointages, je pense bidouiller qq chose avec des
          clefs usb: on fout une clef spécialement formatée et pouf, on a
          pointé (avec un petit message vocal). Mais ça ne règle pas le
          problème d'un pointage oublié (si on oublie de pointer le départ
          de midi, le pointage d'arrivée à 14h00 sera pris comme le départ
          oublié)... A moins de générer un départ automatique si la période
          est "louche"...
          • [^] # Re: Je ne sais pas si c'est la bonne méthode, mais....

            Posté par  . Évalué à 2.

            Bon, ben à ce que je vois, on est sur la même longueur d'onde !
            Et je ne veux *pas* avoir de code spécifique au calcul dans l'appli,(...)
            Mon Dieu, comme je suis d'accord avec toi !
            De mon côté, j'ai tout fait entre d'un côté les tables, et de l'autre, des vues et des triggers. Pas question, à quelque moment que ce soit, d'avoir le moindre calcul innérent à la cohérence de la base ailleurs que dans la base elle-même.
            Pour le côté php, j'ai conçu des fonctions qui, selon l'appel, me conçoivent les requêtes à passer à la base de données. Comme tu souhaite apprendre par toi même, ce qui est fort louable, je ne te les propose pas !
            Par contre, j'ai pu constater que php se démerdait très bien avec les tableaux, et je dois bien avouer que j'en ai usé et abusé dans tous les sens. Surtout des tableaux multi-dimentionnnels avec indices non numériques. C'est fort pratique pour récupérer les champs de fomulaires, dans un tableau à 3 dimentions qui me donne le nom du formulaire d'où les informations proviennent, la ligne ou l'indice, qui me permet de numéroter les requêtes ainsi générées, ainsi que les noms des tables et des champs.
            Pour les problèmes de sécurités, les informations correctes vis-à-vis de la base de données sont dans la session, alors que les valeurs des formulaires ne sont que les clefs qui me permettent d'accéder à ces valeurs. Je ne sais pas si je suis clair, mais passons.
            Pour le coup du pointage par clef usb, c'est une bonne idée, mais pourquoi ne pas prévoir un interface web (tout du moins intranet) sur lequel les gens peuvent éventuellement corriger les horraires oubliés ou "mal pointés". Il faudra de toute façon un tel dispositif, car tu ne peux décider pour les gens si leur absence correspond à un congé, un stage, un arrêt maladie ou une journée de R.T.T.
            Enfin, ce ne sont que quelques idées en vrac, comme ça !
            Bon courage, quoi qu'il en soit !
  • # champs "durée" et postgres

    Posté par  . Évalué à 1.

    Salut,

    Personnellement j'utilise des champs bigint pour avoir les dates
    en "long", ce qui représente des millisecondes. Ca a l'avantage
    d'être rapide et de simplifier les calculs. Ensuite tu peux soustraire
    facilement (date de fin - date de début) = temp écoulé en millisecond.
    Et finalement une petite méthode pour convertir cela en format HH:mm:ss
    et le tour est joué ! Comme c'est en java, ensuite j'utilise l'object Calendar
    qui travaille aussi avec des long.

    Voilà, peut être une nouvelle piste à explorer !
    a+
    Xavier

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.