Oubli du 29/02/2016 dans une requête

**Guisty** · 25/04/2016, 12h21

Bonjour,
je récupère des plages horaires depuis une table contenant une heure de début et une heure de fin (plus un projet et un utilisateur)
pour faire des sommes (par jour, semaine, mois,...). Depuis 3 ans aucun problème.
Et là avec l'année bissextile, impossible de ramener les plages du 29 février ! J'ai "tout" essayé, rien à faire. Voici la requête :

select STARTTIME, ENDTIME
from HORAIRES
where NO_PER = 59
and NO_PROJ = 22
and STARTTIME >= '2016/02/01'
and STARTTIME <= '2016/02/29'

si je donne comme date '2016/03/01' je les vois bien, mais j'aimerai quand même comprendre où est le pb...
Avez-vous déjà rencontré ce pb ?

Si vous avez une solution, merci par avance

G.

**Barbibulle** · 25/04/2016, 12h46

Bonjour

Pouvez vous préciser la version de firebird ?

La définition de cette table ?

Avez vous un message d'erreur ? lequel ?

Car ce type de select fonctionne parfaitement.

Même si j aurai plutôt écrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select STARTTIME, ENDTIME
from HORAIRES
where NO_PER = 59
and NO_PROJ = 22
and STARTTIME between '2016/02/01' and '2016/02/29'

**Guisty** · 25/04/2016, 14h06

Merci pour la réponse.

Version de Firebird : 1.5,
Description de la table :
HORAIRES (
CODE_H VARCHAR(40) NOT NULL,
NO_PER SMALLINT,
NO_PROJ SMALLINT,
SOUS_PROJ SMALLINT,
STARTTIME TIMESTAMP,
ENDTIME TIMESTAMP,
SUJET VARCHAR(40),
NOTES VARCHAR(255),
COULEUR INTEGER,
IMAGE SMALLINT
)

Je n'ai aucun message d'erreur (que j'ai quand j'essaie de forcer à 2016/02/30 évidemment)
c'est juste une non prise en compte des plages horaires du 29.
Pour le between, j'ai préféré garder un contrôle visuel de mes bornes pour le test.

**SergioMaster** · 25/04/2016, 15h04

Bonjour,

comme il s'agit de 1.5 j'aurais tendance a mettre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

and STARTTIME between CAST('2016/02/01 00:00:00' AS TIMESTAMP) and CAST('2016/02/29 23:59:59' AS TIMESTAMP)

**Barbibulle** · 25/04/2016, 15h22

Ce n'est pas lié à a version de firebird.

En 1.5 il existait le type DATE.

Et effectivement si on compare un timestamp avec une date il faut comprendre que pour la comparaison la date est convertie en timestamp et non l'inverse.

Du coup si dans la partie time de vos timestamp il y a autre chose que 00:00:00.000 vous n'aurez pas le résultat attendu...
29/02/2016 00:00:01 n'est pas <= à 29/02/2016 00:00:00

Donc votre problème n'est pas lié au années bissextiles mais à votre tests (quelques soit le mois d'ailleurs).

En conclusion soit vous faites comme SergioMaster vous l'a écrit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

and STARTTIME between CAST('2016/02/01 00:00:00' AS TIMESTAMP) and CAST('2016/02/29 23:59:59' AS TIMESTAMP)

Ou encore

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

and cast(STARTTIME as date) between '2016/02/01' and '2016/02/29'

**Guisty** · 25/04/2016, 16h03

Merci pour ces réponses

Oui mathématiquement l'expression : 29/02/2016 00:00:01 n'est pas <= à 29/02/2016 00:00:00 est correcte

mais je voudrai comprendre où elle rentre en ligne de compte dans mon problème :

la requête exécutée avec comme bornes du 01/02/2016 au 31/03/2016 me donne 266 h 30 min
la même exécutée avec comme bornes du 01/02/2016 au 29/02/2016 me donne 113 h 30 min
la même exécutée avec comme bornes du 01/03/2016 au 31/03/2016 me donne 145 h 30 min

soit un différentiel de 7 h 30 min soit les 2 créneaux horaires du 29/02 !

Je n'ai jamais eu au préalable une telle erreur dans les calculs, ce serait une coïncidence qu'elle tombe un 29 février ?

J'ai une procédure stockée qui fait le même calcul :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
begin
  TOTAL_HOR = 0;
  /* Récupération des lignes */
      for
      select STARTTIME, ENDTIME
      from horaires
      where NO_PROJ =:Code_PRO       /* Code projet    */
        and NO_PER  =:Code_PER         /* Code personnel */
        and STARTTIME >= :date_deb
        and ENDTIME <= :date_fin
      into :h_debut,
           :h_fin
      do begin
           PROV_HD = extract(hour from h_debut);
           PROV_MD = extract(minute from h_debut);
           PROV_HF = extract(hour from h_fin);
           PROV_MF = extract(minute from h_fin);
           if (PROV_HF = 0) then PROV_HF = 24;
           total_hor = total_hor + ((PROV_HF-PROV_HD)*60) + (PROV_MF-PROV_MD);
         end
  exit;
end

STARTTIME et ENDTIME de type DATE

j'ai la même erreur, faut-il que je passe aussi par un CAST ?

**Barbibulle** · 25/04/2016, 17h49

Je ne comprend pas votre procédure.

Votre table horaire contient quoi comme données ?

le starttime et le endtime sont tous les deux à la même Date ? (sinon je ne vois pas comment ça peut fonctionner...)
Si c'est le cas il aurait mieux valu une colonne DateduJour de type date, HeureDebut de type time et heureFin de type time.
enfin supposons que l'enregistrement ne concerne qu'une journée...
Si c'est le cas on peut tester la date aussi bien sur le start que sur le end.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select STARTTIME, ENDTIME
    from horaires
    where NO_PROJ =:Code_PRO /* Code projet */
       and NO_PER =:Code_PER /* Code personnel */
       and STARTTIME >= :date_deb
       and cast(ENDTIME as date) <= :date_fin

revient à

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
select STARTTIME, ENDTIME
    from horaires
    where NO_PROJ =:Code_PRO /* Code projet */
       and NO_PER =:Code_PER /* Code personnel */
       and STARTTIME >= :date_deb
       and cast(STARTTIME as date) <= :date_fin

donc votre procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
begin
  TOTAL_HOR = 0;
  /* Récupération des lignes */
  for
  select STARTTIME, ENDTIME
    from horaires
    where NO_PROJ =:Code_PRO /* Code projet */
       and NO_PER =:Code_PER /* Code personnel */
       and STARTTIME >= :date_deb
       and cast(ENDTIME as date) <= :date_fin
  into :h_debut, :h_fin
  do 
  begin
    PROV_HD = extract(hour from h_debut);     
    PROV_MD = extract(minute from h_debut); 
    PROV_HF = extract(hour from h_fin);          
    PROV_MF = extract(minute from h_fin);      
    if (PROV_HF = 0) then PROV_HF = 24;
    total_hor = total_hor + ((PROV_HF-PROV_HD)*60) + (PROV_MF-PROV_MD); 
  end
exit; // sert a quoi ici ???? pourquoi pas suspend ?
end

**SergioMaster** · 25/04/2016, 17h50

Envoyé par Barbibulle

Ce n'est pas lié à a version de firebird.
En 1.5 il existait le type DATE.

je pensais encore a certaines bases en DIALECT 1

**Barbibulle** · 25/04/2016, 18h20

Envoyé par SergioMaster

je pensais encore a certaines bases en DIALECT 1

Je n'y avais pas pensé, effectivement ça peu arriver

**Barbibulle** · 25/04/2016, 18h49

Je viens de percuter sur

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 if (PROV_HF = 0) then PROV_HF = 24;

C'est supposer que l'heure 00 correspond à la fin de la journée ?? ( normalement la fin c est 23h59min59s)

Si vous ne testez que l'heure alors 00h59min59s serait considéré comme 24h59min59 donc une journée de 25h j'en ai rêvé

Mais d'un autre coté non puisque la journée démarrerai qu'à 1h00

(ouf)

Donc déjà si vous ne pouvez changer l'alimentation de cette table et que pour vous 00h00:00 est bien pour vous la fin de journée
il faudrait tester les minutes et secondes....

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 if ((PROV_HF = 0) and (PROV_MF=0) and (PROV_SF)) then PROV_HF = 24;

Enfin.... Donc il y a un potentiel bug de calcul

**SergioMaster** · 26/04/2016, 09h22

Bonjour,

ma réponse sera peut être un peu en décalage mais ... pourquoi se casser la tête, utilisez une UDF externe je pense aux fonctions de FreeAdhocUDF en particulier F_Ageinhours et ses consœurs

de plus comme Barbibulle, je ne comprends pas trop cette procédure ! je me méfie des calculs dans les boucle FOR SELECT .... dans une procédure alors qu'un
SELECT SUM() ... GROUP BY pourrait faire la même chose

Bref, en admettant que :
1- une durée (STARTTIME,ENDTIME) peut être à cheval sur plusieurs jours
2- qu'un date de fin peut être le mois suivant
et je ne parle pas des jours non travaillés

les fonctions internes étant si pauvre avec FB 1.5 (plus maintenue, dois-je l'indiquer ?)
je reste sur ma position d'ajout d'UDF

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
declare external function F_AGEINMINUTES
  date, date
  returns integer by value
  entry_point 'ageinminutes' module_name 'FreeAdHocUDF';

et la procedure peut être remplacée par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT F_AGEINMINUTES(ENDTIME,STARTTIME) FROM HORAIRES 
WHERE NO_PROJ =:Code_PRO /* Code projet */
       and NO_PER =:Code_PER /* Code personnel */
       and STARTTIME >= :date_deb
       and cast(ENDTIME as date) <= :date_fin

resterai à prévoir le cas n°2

**Barbibulle** · 26/04/2016, 09h34

J'aurai également opté pour des fonctions de calcul toutes faites avec les UDF.

Mais s'il considère que 00h00 c'est la fin de journée, ca ne va pas fonctionner car pour les UDF et pour firebird 00h00 c'est le début de journée...

Donc faudrait commencer par revoir la façon d'insérer les données.

Envoyé par SergioMaster

Bref, en admettant que :
1- une durée (STARTTIME,ENDTIME) peut être à cheval sur plusieurs jours

Son code ne fonctionne que si starttime et endtime sont sur la même journée. Donc il n'a pas à se préoccuper de çà ici ni du cas 2 du coup.

**SergioMaster** · 26/04/2016, 09h59

Envoyé par Barbibulle

J'aurai également opté pour des fonctions de calcul toutes faites avec les UDF.

j'en ai pas rajouté une couche en parlant de la version de Firebird

Envoyé par Barbibulle

Mais s'il considère que 00h00 c'est la fin de journée, ca ne va pas fonctionner car pour les UDF et pour firebird 00h00 c'est le début de journée...

il suffit d'ôter une seconde voire milliseconde (je suis comme Firebird j'ai du mal à ne pas prendre mon début de journée à "00 00" comme disent nos amis d'outre manche

ou de rajouter 24H . Je n'ai pas compris le principe STARTTIME = TIMESTAMP de début OK ENDTIME = ? DATE de STARTTIME + un temps ou 0 ou ? ,

je n'ai pas trop envie de me triturer les méninges en lisant la procédure

Envoyé par Barbibulle

Son code ne fonctionne que si starttime et endtime sont sur la même journée. Donc il n'a pas à se préoccuper de çà ici ni du cas 2 du coup.

oui mais ce code ... voir au dessus

**Artemus24** · 26/04/2016, 12h53

Salut à tous.

Il y a plusieurs problèmes dans votre façon de gérer vos dates :

1) la syntaxe d'une date est : YYYY-MM-DD
Si vous mettez des "/", FireBird va faire une conversion et vous allez perdre du temps pour rien.

2) si votre requête ne fonctionne pas, c'est que vous comparez un timestamp avec une date.
Pour résoudre votre problème, vous devez normaliser votre comparaison :
--> soit en mettant partout des timestamp
--> soit en mettant partout des dates.

3) si vous manipulez uniquement des dates, alors utilisez le type "date".
Si vous avez besoin de l'heure (j'en doute), alors utilisez le type "time", en créant une colonne à part.
Mais déclarer un timestamp, juste pour manipuler des dates est une erreur de conception de votre base de données.

4) pour résoudre le problème de votre requête, vou avez le choix entre :
--> Soit vous adaptez votre requête en utilisant la solution de SergioMaster (post #4).
--> Soit vous corrigez votre base de données en utilisant le type "date" (voir §3).
--> soit vous extrayez la date de votre timestamp, mais cette solution n'est pas propre.

5) j'ai fais un exercice, en reprenant votre table et en insérant un jeu d'essai.
J'ai mis une date courant février, une au 29 février (cas de l'année bisextile) et encore une autre courant mars. Au total, j'ai 20 heures !
J'ai aussi repris la procédure, en la simplifiant.

Et voici mon exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
 
-- ===============================
-- Création de la table 'horaires'
-- ===============================
 
CREATE TABLE HORAIRES (
CODE_H    VARCHAR(40)  NOT NULL collate fr_fr,
NO_PER    SMALLINT,
NO_PROJ   SMALLINT,
SOUS_PROJ SMALLINT,
STARTTIME TIMESTAMP,
ENDTIME   TIMESTAMP,
SUJET     VARCHAR(40)  NOT NULL collate fr_fr,
NOTES     VARCHAR(255) NOT NULL collate fr_fr,
COULEUR   INTEGER,
IMAGE     SMALLINT
);
 
-- =========================
-- Insertion dans 'horaires'
-- =========================
 
insert into horaires (code_h,no_per,no_proj,sous_proj,starttime,endtime,sujet,notes,couleur,image) values ('c1', 59, 22, 1, '2016/02/09 09:00:00', '2016/02/09 18:00:00', 'bla bla', 'rien', 5, 2);
insert into horaires (code_h,no_per,no_proj,sous_proj,starttime,endtime,sujet,notes,couleur,image) values ('c1', 59, 22, 2, '2016/02/29 15:00:00', '2016/02/29 19:00:00', 'bla bla', 'rien', 5, 2);
insert into horaires (code_h,no_per,no_proj,sous_proj,starttime,endtime,sujet,notes,couleur,image) values ('c1', 59, 22, 3, '2016/03/05 07:00:00', '2016/03/05 14:00:00', 'bla bla', 'rien', 5, 2);
 
-- ====================
-- Vidage de 'horaires'
-- ====================
 
select * from horaires;
 
CODE_H                                    NO_PER NO_PROJ SOUS_PROJ                 STARTTIME                   ENDTIME SUJET                                    NOTES                                                                                                                                                                                                                                                                COULEUR   IMAGE
======================================== ======= ======= ========= ========================= ========================= ======================================== =============================================================================== ============ =======
c1                                            59      22         1 2016-02-09 09:00:00.0000  2016-02-09 18:00:00.0000  bla bla                                  rien                                                                                                                                                                                                                                                                       5       2
c1                                            59      22         2 2016-02-29 15:00:00.0000  2016-02-29 19:00:00.0000  bla bla                                  rien                                                                                                                                                                                                                                                                       5       2
c1                                            59      22         3 2016-03-05 07:00:00.0000  2016-03-05 14:00:00.0000  bla bla                                  rien                                                                                                                                                                                                                                                                       5       2
 
 
-- ============
-- Requête N°01
-- ============
 
select STARTTIME, ENDTIME
from   HORAIRES
where  NO_PER  = 59
and    NO_PROJ = 22
and    STARTTIME BETWEEN '2016-02-01' AND '2016-03-31';
 
                STARTTIME                   ENDTIME
========================= =========================
2016-02-09 09:00:00.0000  2016-02-09 18:00:00.0000
2016-02-29 15:00:00.0000  2016-02-29 19:00:00.0000
2016-03-05 07:00:00.0000  2016-03-05 14:00:00.0000
 
 
-- ============
-- Requête N°02
-- ============
 
select STARTTIME, ENDTIME
from   HORAIRES
where  NO_PER  = 59
and    NO_PROJ = 22
and    STARTTIME BETWEEN '2016-02-01' AND '2016-02-29';
 
                STARTTIME                   ENDTIME
========================= =========================
2016-02-09 09:00:00.0000  2016-02-09 18:00:00.0000
 
 
-- ============
-- Requête N°03
-- ============
 
select STARTTIME, ENDTIME
from   HORAIRES
where  NO_PER  = 59
and    NO_PROJ = 22
and    STARTTIME BETWEEN '2016-02-01 00:00:00.0000' AND '2016-02-29 23:59:59.9999';
 
                STARTTIME                   ENDTIME
========================= =========================
2016-02-09 09:00:00.0000  2016-02-09 18:00:00.0000
2016-02-29 15:00:00.0000  2016-02-29 19:00:00.0000
 
 
-- ============
-- Requête N°04
-- ============
 
select STARTTIME, ENDTIME
from   HORAIRES
where  NO_PER  = 59
and    NO_PROJ = 22
and    cast(STARTTIME as date) BETWEEN '2016-02-01' AND '2016-02-29';
 
                STARTTIME                   ENDTIME
========================= =========================
2016-02-09 09:00:00.0000  2016-02-09 18:00:00.0000
2016-02-29 15:00:00.0000  2016-02-29 19:00:00.0000
 
 
-- ======================
-- Create procedure N° 01
-- ======================
 
SET TERM $;
CREATE PROCEDURE proc1 (Code_PRO  SMALLINT,
                        Code_PER  SMALLINT,
                        date_deb  date,
                        date_fin  date)
returns                (total_hor integer)     /* Récupération des lignes */
AS
  DECLARE h_debut    timestamp;
  DECLARE h_fin      timestamp;
 
  DECLARE prov_hd    integer;
  DECLARE prov_md    integer;
  DECLARE prov_hf    integer;
  DECLARE prov_mf    integer;
BEGIN
  total_hor = 0;
 
  for select STARTTIME, ENDTIME
  from horaires
  where NO_PROJ     =:Code_PRO                  /* Code projet    */
    and NO_PER      =:Code_PER                  /* Code personnel */
    and STARTTIME >= :date_deb
    and ENDTIME   <= :date_fin
   into :h_debut,
        :h_fin
   do begin
      PROV_HD = extract(hour   from h_debut);
      PROV_MD = extract(minute from h_debut);
      PROV_HF = extract(hour   from h_fin);
      PROV_MF = extract(minute from h_fin);
 
      if (PROV_HF = 0) then PROV_HF = 24;
      total_hor = total_hor + ((PROV_HF-PROV_HD)*60) + (PROV_MF-PROV_MD);
   end
   suspend;
END$
SET TERM ;$
 
select total_hor from proc1(22, 59, '2016-02-01', '2016-03-31');
 
   TOTAL_HOR
============
        1200
 
 
-- ======================
-- Create procedure N° 02
-- ======================
 
SET TERM $;
CREATE PROCEDURE proc2 (Code_PRO  SMALLINT,
                        Code_PER  SMALLINT,
                        date_deb  date,
                        date_fin  date)
returns                (total_hor integer)     /* Récupération des lignes */
AS
BEGIN
  total_hor = 0;
 
  select sum(datediff(minute from starttime to endtime))
  from horaires
  where NO_PROJ     =:Code_PRO                  /* Code projet    */
    and NO_PER      =:Code_PER                  /* Code personnel */
    and STARTTIME >= :date_deb
    and ENDTIME   <= :date_fin
   into :total_hor;
   suspend;
END$
SET TERM ;$
 
commit;
 
select total_hor from proc2(22, 59, '2016-02-01', '2016-03-31');
 
   TOTAL_HOR
============
        1200
 
 
exit;
 
Appuyez sur une touche pour continuer...

@+