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 ?
Forum Programmation.autre champs "durée" et postgres
21
juil.
2005
# Je ne sais pas si c'est la bonne méthode, mais....
Posté par Gyro Gearllose . Évalué à 3.
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 zx81 . Évalué à 1.
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 Gyro Gearllose . Évalué à 2.
- 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 zx81 . Évalué à 2.
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 Gyro Gearllose . Évalué à 2.
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 Xavier FACQ . Évalué à 1.
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.