Requête d'addition de temps

**gastoncs** · 05/05/2016, 20h48

Bonsoir,

je suis face a un problème, j'ai une table disponibilite qui contient les champs suivants :

id_perso (l'id de la table membre, contenant nom et prénom)
statut_perso (qui est soit absent ou disponible)
date_perso (un champ datetime qui contient la date et l'heure de changement de statut_perso
... d'autres champs sans utilité

Nom : exemple_datetime.png
Affichages : 87
Taille : 12,3 Ko

je souhaite pour chaque id_perso (pour chaque membre) connaitre le temps en heures passé en statut absent et disponible et ainsi faire la somme des deux pour connaitre le nombres d'heure d'absence et de disponibilité pour chaque membre.
Je me demande si la structure de ma table le permet ? ou bien dois je rajouter un champ datetime pour la date et heure de fin du statut ?

Merci pour votre aide.

**Artemus24** · 06/05/2016, 15h10

Salut Gastoncs.

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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `disponibilite`
--------------
 
--------------
CREATE TABLE `disponibilite`
(
  `id`            integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer unsigned NOT NULL,
  `statut_perso`  varchar(255)     NOT NULL,
  `date_perso`    datetime         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `disponibilite` (`id_perso`, `statut_perso`,`date_perso`) VALUES
  ( 1, 'absent',     '2015-12-10 22:36:52'),
  ( 1, 'disponible', '2015-12-09 13:48:13'),
  ( 1, 'absent',     '2015-12-09 13:53:11'),
  ( 1, 'disponible', '2015-12-09 13:54:51'),
  ( 1, 'absent',     '2015-12-10 10:05:22'),
  (50, 'absent',     '2015-12-10 22:33:33'),
  ( 1, 'disponible', '2015-12-10 22:34:01'),
  (50, 'disponible', '2015-12-10 22:34:54')
--------------
 
--------------
select * from disponibilite
--------------
 
+----+----------+--------------+---------------------+
| id | id_perso | statut_perso | date_perso          |
+----+----------+--------------+---------------------+
|  1 |        1 | absent       | 2015-12-10 22:36:52 |
|  2 |        1 | disponible   | 2015-12-09 13:48:13 |
|  3 |        1 | absent       | 2015-12-09 13:53:11 |
|  4 |        1 | disponible   | 2015-12-09 13:54:51 |
|  5 |        1 | absent       | 2015-12-10 10:05:22 |
|  6 |       50 | absent       | 2015-12-10 22:33:33 |
|  7 |        1 | disponible   | 2015-12-10 22:34:01 |
|  8 |       50 | disponible   | 2015-12-10 22:34:54 |
+----+----------+--------------+---------------------+
--------------
select id_perso,
       statut_perso,
       date_perso                               as date_before,
       @prev2:=if(id_perso=@prev1,@prev2, null) as date_after,
       @prev1:=id_perso,
       @prev2:=date_perso
from disponibilite, (select @prev1:='',@prev2:='') as x
order by id_perso, date_perso desc
--------------
 
+----------+--------------+---------------------+---------------------+------------------+---------------------+
| id_perso | statut_perso | date_before         | date_after          | @prev1:=id_perso | @prev2:=date_perso  |
+----------+--------------+---------------------+---------------------+------------------+---------------------+
|        1 | absent       | 2015-12-10 22:36:52 | NULL                |                1 | 2015-12-10 22:36:52 |
|        1 | disponible   | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 |                1 | 2015-12-10 22:34:01 |
|        1 | absent       | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |                1 | 2015-12-10 10:05:22 |
|        1 | disponible   | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 |                1 | 2015-12-09 13:54:51 |
|        1 | absent       | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |                1 | 2015-12-09 13:53:11 |
|        1 | disponible   | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 |                1 | 2015-12-09 13:48:13 |
|       50 | disponible   | 2015-12-10 22:34:54 | NULL                |               50 | 2015-12-10 22:34:54 |
|       50 | absent       | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |               50 | 2015-12-10 22:33:33 |
+----------+--------------+---------------------+---------------------+------------------+---------------------+
--------------
select id_perso,
       statut_perso,
       ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff,
           date_before,
       date_after
from (
 
    select id_perso,
           statut_perso,
           date_perso                               as date_before,
           @prev2:=if(id_perso=@prev1,@prev2, null) as date_after,
           @prev1:=id_perso,
           @prev2:=date_perso
    from disponibilite, (select @prev1:='',@prev2:='') as x
    order by id_perso, date_perso desc
 
) as y
order by id_perso, date_before
--------------
 
+----------+--------------+----------+---------------------+---------------------+
| id_perso | statut_perso | diff     | date_before         | date_after          |
+----------+--------------+----------+---------------------+---------------------+
|        1 | disponible   | 00:04:58 | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 |
|        1 | absent       | 00:01:40 | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |
|        1 | disponible   | 20:10:31 | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 |
|        1 | absent       | 12:28:39 | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |
|        1 | disponible   | 00:02:51 | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 |
|        1 | absent       | 00:00:00 | 2015-12-10 22:36:52 | NULL                |
|       50 | absent       | 00:01:21 | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |
|       50 | disponible   | 00:00:00 | 2015-12-10 22:34:54 | NULL                |
+----------+--------------+----------+---------------------+---------------------+
--------------
select id_perso,
       case statut_perso when 'absent'     then sec_to_time(sum(time_to_sec(diff))) else '00:00:00' end as absent,
       case statut_perso when 'disponible' then sec_to_time(sum(time_to_sec(diff))) else '00:00:00' end as disponible
from (
 
    select id_perso,
           statut_perso,
           ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff
    from (
 
        select id_perso,
               statut_perso,
               date_perso                                     as date_before,
               @prev2:=if(id_perso=@prev1,@prev2, '00:00:00') as date_after,
               @prev1:=id_perso,
               @prev2:=date_perso
        from disponibilite, (select @prev1:='',@prev2:='') as x
        order by id_perso, date_perso desc
 
    ) as y
    order by id_perso, date_before
 
) as z
group by id_perso, statut_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 00:00:00   |
|        1 | 00:00:00 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
|       50 | 00:00:00 | 00:00:00   |
+----------+----------+------------+
--------------
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select id_perso,
           case statut_perso when 'absent'     then sum(time_to_sec(diff)) else 0 end as absent,
           case statut_perso when 'disponible' then sum(time_to_sec(diff)) else 0 end as disponible
    from (
 
        select id_perso,
               statut_perso,
               ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff
        from (
 
            select id_perso,
                   statut_perso,
                   date_perso                                     as date_before,
                   @prev2:=if(id_perso=@prev1,@prev2, '00:00:00') as date_after,
                   @prev1:=id_perso,
                   @prev2:=date_perso
            from disponibilite, (select @prev1:='',@prev2:='') as x
            order by id_perso, date_perso desc
 
        ) as y
        order by id_perso, date_before
 
    ) as z
    group by id_perso, statut_perso
 
) as t
group by id_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
+----------+----------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

J'ai fait en sorte de remonter la date suivante sur la ligne précédente afin de calculer la différence en time.

J'ai décomposé en plusieurs étapes la requête afin que tu puisses voir les résultats intermédiaires.

Tu me dis ce que tu en penses, et nous pourrons pas la suite voir ce que l'on peut améliorer dans la structure de ta table.

@+

**gastoncs** · 06/05/2016, 16h09

Merçi atemus24,

j'ai reproduis chaque requete, et je dois dire que c'est complexe, mais ça fonctionne belle et bien !
je ne connaissais pas tout ces instructions Sql comme :

Code :

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

1
2
3
@prev2:=if(id_perso=@prev1,@prev2, "00:00:00") as date_after,
                   @prev1:=id_perso,
                   @prev2:=date_perso

Mais afin d'améliorer ma requête, enfin plutôt ta dernière requête je me demande, si je dois appliquer une clause 'where' sur id_perso afin de récupérer la personne qui m'intéresse ou dois je la placer... ?

**gastoncs** · 06/05/2016, 16h21

Pardon, mais avec un peu de recherche j'ai trouvé :

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
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select id_perso,
           case statut_perso when "absent"     then sum(time_to_sec(diff)) else 0 end as absent,
           case statut_perso when "disponible" then sum(time_to_sec(diff)) else 0 end as disponible
    from (
 
        select id_perso,
               statut_perso,
               ifnull(cast(timediff(date_after, date_before) as time), "00:00:00") as diff
        from (
 
            select id_perso,
                   statut_perso,
                   date_perso                                     as date_before,
                   @prev2:=if(id_perso=@prev1,@prev2, "00:00:00") as date_after,
                   @prev1:=id_perso,
                   @prev2:=date_perso
            from disponibilite, (select @prev1:="",@prev2:="") as x
            order by id_perso, date_perso desc
 
        ) as y
        order by id_perso, date_before
 
    ) as z
    group by id_perso, statut_perso
 
) as t
where id_perso = "'.ici_je_place_l'id_de_la_personne_concernée.'"
group by id_perso

**Artemus24** · 06/05/2016, 17h59

Salut Gastoncs.

Le where, je l'aurai mis dans la requête la plus interne, comme ci-après :

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
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select id_perso,
           case statut_perso when "absent"     then sum(time_to_sec(diff)) else 0 end as absent,
           case statut_perso when "disponible" then sum(time_to_sec(diff)) else 0 end as disponible
    from (
 
        select id_perso,
               statut_perso,
               ifnull(cast(timediff(date_after, date_before) as time), "00:00:00") as diff
        from (
 
            select id_perso,
                   statut_perso,
                   date_perso                                     as date_before,
                   @prev2:=if(id_perso=@prev1,@prev2, "00:00:00") as date_after,
                   @prev1:=id_perso,
                   @prev2:=date_perso
            from disponibilite, (select @prev1:="",@prev2:="") as x
            where id_perso = "'.ici_je_place_l'id_de_la_personne_concernée.'"
            order by id_perso, date_perso desc
 
        ) as y
        order by id_perso, date_before
 
    ) as z
    group by id_perso, statut_perso
 
) as t
group by id_perso;

Pour améliorer ta requête, c'est-à-dire supprimer un niveau d'imbrication des sous-requêtes, serait-il possible d'ajouter dans ta table, la date que je nomme 'date_after' ?
Car la requête la plus interne a juste pour rôle de faire remonter la dernière date de id_perso.

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
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `disponibilite`
--------------
 
--------------
CREATE TABLE `disponibilite`
(
  `id`            integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer unsigned NOT NULL,
  `statut_perso`  varchar(255)     NOT NULL,
  `date_before`   datetime         NOT NULL,
  `date_after`    datetime             NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `disponibilite` (`id_perso`, `statut_perso`,`date_before`,`date_after`) VALUES
  ( 1, 'absent',     '2015-12-10 22:36:52',                  null),
  ( 1, 'disponible', '2015-12-09 13:48:13', '2015-12-09 13:53:11'),
  ( 1, 'absent',     '2015-12-09 13:53:11', '2015-12-09 13:54:51'),
  ( 1, 'disponible', '2015-12-09 13:54:51', '2015-12-10 10:05:22'),
  ( 1, 'absent',     '2015-12-10 10:05:22', '2015-12-10 22:34:01'),
  (50, 'absent',     '2015-12-10 22:33:33', '2015-12-10 22:34:54'),
  ( 1, 'disponible', '2015-12-10 22:34:01', '2015-12-10 22:36:52'),
  (50, 'disponible', '2015-12-10 22:34:54',                  null)
--------------
 
--------------
select * from disponibilite order by id_perso, date_before
--------------
 
+----+----------+--------------+---------------------+---------------------+
| id | id_perso | statut_perso | date_before         | date_after          |
+----+----------+--------------+---------------------+---------------------+
|  2 |        1 | disponible   | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 |
|  3 |        1 | absent       | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |
|  4 |        1 | disponible   | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 |
|  5 |        1 | absent       | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |
|  7 |        1 | disponible   | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 |
|  1 |        1 | absent       | 2015-12-10 22:36:52 | NULL                |
|  6 |       50 | absent       | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |
|  8 |       50 | disponible   | 2015-12-10 22:34:54 | NULL                |
+----+----------+--------------+---------------------+---------------------+
--------------
select id_perso,
       statut_perso,
       ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff,
       date_before,
       date_after
from disponibilite
order by id_perso, date_before
--------------
 
+----------+--------------+----------+---------------------+---------------------+
| id_perso | statut_perso | diff     | date_before         | date_after          |
+----------+--------------+----------+---------------------+---------------------+
|        1 | disponible   | 00:04:58 | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 |
|        1 | absent       | 00:01:40 | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |
|        1 | disponible   | 20:10:31 | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 |
|        1 | absent       | 12:28:39 | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |
|        1 | disponible   | 00:02:51 | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 |
|        1 | absent       | 00:00:00 | 2015-12-10 22:36:52 | NULL                |
|       50 | absent       | 00:01:21 | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |
|       50 | disponible   | 00:00:00 | 2015-12-10 22:34:54 | NULL                |
+----------+--------------+----------+---------------------+---------------------+
--------------
select id_perso,
       case statut_perso when 'absent'     then sec_to_time(sum(time_to_sec(diff))) else '00:00:00' end as absent,
       case statut_perso when 'disponible' then sec_to_time(sum(time_to_sec(diff))) else '00:00:00' end as disponible
from (
 
    select id_perso,
           statut_perso,
           ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff,
           date_before,
           date_after
    from disponibilite
    order by id_perso, date_before
 
) as x
group by id_perso, statut_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 00:00:00   |
|        1 | 00:00:00 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
|       50 | 00:00:00 | 00:00:00   |
+----------+----------+------------+
--------------
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select id_perso,
           case statut_perso when 'absent'     then sum(time_to_sec(diff)) else 0 end as absent,
           case statut_perso when 'disponible' then sum(time_to_sec(diff)) else 0 end as disponible
    from (
 
        select id_perso,
               statut_perso,
               ifnull(cast(timediff(date_after, date_before) as time), '00:00:00') as diff
        from disponibilite
        order by id_perso, date_before
 
    ) as x
    group by id_perso, statut_perso
 
) as y
group by id_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
+----------+----------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

Un niveau d'imbrication en moins et la requête devient moins compliqué.

On peut encore améliorer de deux niveaux, en faisant en sorte de mettre dans la même ligne, le couple (date_before ; date_after) pour "absence" et pour "disponible".
Ce qui donne un changement de la structure de ta table :
--> id_perso
--> date_before
--> date_inter
--> date_after

avec le couple (date_before ; date_inter) correspond à "disponible".
avec le couple (date_inter ; date_after) correspond à "absence".

Pourquoi ai-je commencé par disponible ?
Tout simplement pour indiquer la présence d'une personne.

Bien entendu la date_inter de "disponible" correspond à la date_inter de "absence".
Autrement dit, la fin de la période "disponible" est aussi le début de la période "absence".

Et bien sûr, quand tu ajoutes une nouvelle ligne, tu dois reporter la date_after précédente dans la date_before.
Et voici la requête.

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
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `disponibilite`
--------------
 
--------------
CREATE TABLE `disponibilite`
(
  `id`            integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer unsigned NOT NULL,
  `date_before`   datetime             NULL,
  `date_inter`    datetime             NULL,
  `date_after`    datetime             NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `disponibilite` (`id_perso`,`date_before`,`date_inter`,`date_after`) VALUES
 
  ( 1, '2015-12-09 13:48:13', '2015-12-09 13:53:11', '2015-12-09 13:54:51'),
  ( 1, '2015-12-09 13:54:51', '2015-12-10 10:05:22', '2015-12-10 22:34:01'),
  ( 1, '2015-12-10 22:34:01', '2015-12-10 22:36:52',                  null),
  (50,                  null, '2015-12-10 22:33:33', '2015-12-10 22:34:54'),
  (50, '2015-12-10 22:34:54',                  null,                  null)
--------------
 
--------------
select * from disponibilite order by id_perso, date_before
--------------
 
+----+----------+---------------------+---------------------+---------------------+
| id | id_perso | date_before         | date_inter          | date_after          |
+----+----------+---------------------+---------------------+---------------------+
|  1 |        1 | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |
|  2 |        1 | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |
|  3 |        1 | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 | NULL                |
|  4 |       50 | NULL                | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |
|  5 |       50 | 2015-12-10 22:34:54 | NULL                | NULL                |
+----+----------+---------------------+---------------------+---------------------+
--------------
select id_perso,
       sec_to_time(sum(time_to_sec(ifnull(cast(timediff(date_inter, date_before) as time), '00:00:00')))) as disponible,
       sec_to_time(sum(time_to_sec(ifnull(cast(timediff(date_after, date_inter)  as time), '00:00:00')))) as absence
from disponibilite
group by id_perso
order by id_perso
--------------
 
+----------+------------+----------+
| id_perso | disponible | absence  |
+----------+------------+----------+
|        1 | 20:18:20   | 12:30:19 |
|       50 | 00:00:00   | 00:01:21 |
+----------+------------+----------+
 
Appuyez sur une touche pour continuer...

C'est plus court et plus performant !

Qu'est-ce que tu en penses de cette approches ?

@+

**Artemus24** · 06/05/2016, 19h43

Salut Gastoncs.

Comme le sujet est intéressant, j'ai cherché à simplifier la requête.
Je n'ai pas décomposé la requête cette fois-ci !
Voici le résultat :

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
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `disponibilite`
--------------
 
--------------
CREATE TABLE `disponibilite`
(
  `id`            integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer unsigned NOT NULL,
  `statut_perso`  varchar(255)     NOT NULL,
  `date_perso`    datetime         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `disponibilite` (`id_perso`, `statut_perso`,`date_perso`) VALUES
  ( 1, 'absent',     '2015-12-10 22:36:52'),
  ( 1, 'disponible', '2015-12-09 13:48:13'),
  ( 1, 'absent',     '2015-12-09 13:53:11'),
  ( 1, 'disponible', '2015-12-09 13:54:51'),
  ( 1, 'absent',     '2015-12-10 10:05:22'),
  (50, 'absent',     '2015-12-10 22:33:33'),
  ( 1, 'disponible', '2015-12-10 22:34:01'),
  (50, 'disponible', '2015-12-10 22:34:54')
--------------
 
--------------
select * from disponibilite order by id_perso, date_perso
--------------
 
+----+----------+--------------+---------------------+
| id | id_perso | statut_perso | date_perso          |
+----+----------+--------------+---------------------+
|  2 |        1 | disponible   | 2015-12-09 13:48:13 |
|  3 |        1 | absent       | 2015-12-09 13:53:11 |
|  4 |        1 | disponible   | 2015-12-09 13:54:51 |
|  5 |        1 | absent       | 2015-12-10 10:05:22 |
|  7 |        1 | disponible   | 2015-12-10 22:34:01 |
|  1 |        1 | absent       | 2015-12-10 22:36:52 |
|  6 |       50 | absent       | 2015-12-10 22:33:33 |
|  8 |       50 | disponible   | 2015-12-10 22:34:54 |
+----+----------+--------------+---------------------+
--------------
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select tb1.id_perso                                                                                                       as id_perso,
           case tb1.statut_perso when 'absent'     then time_to_sec(timediff(min(tb2.date_perso), tb1.date_perso)) else 0 end as absent,
           case tb1.statut_perso when 'disponible' then time_to_sec(timediff(min(tb2.date_perso), tb1.date_perso)) else 0 end as disponible
    from       disponibilite as tb1
    inner join disponibilite as tb2
    on  tb2.id_perso   = tb1.id_perso
    and tb2.date_perso > tb1.date_perso
    group by tb1.id_perso, tb1.date_perso, tb1.statut_perso
    order by tb1.id_perso, tb1.date_perso, tb1.statut_perso
 
) as x
group by id_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
+----------+----------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Il y a encore deux niveaux d'imbrications, et je pense que l'on ne peut pas faire mieux.

P.S.: une requête se travail en essayant plusieurs possibilités. Au premier abord, je n'ai pas pensé passer par une jointure.
J'ai essayé de faire cela en une requête, mais je me suis retrouvé avec une erreur en faisant un sum() sur le case.

@+

**gastoncs** · 06/05/2016, 22h36

Bonsoir Artemus24, merci pour l’intérêt que tu porte a mon sujet et ton aide précieuse.

Alors j'ai suivis tes conseils précédent et j'ai donc modifier ma table afin d'avoir deux champs date un pour le début et un pour la fin du statut, ainsi j'ai modifié mes requêtes.
C'est a dire lorsqu'une personne modifie sont statut, à l'instant T le champ date_fin est rempli avec l'heure et date actuelle (UPDATE)
et juste après je fait une insertion dans ma table avec le nouveau statut, et seul le champ date_debut est rempli laissant vide le champ date_fin, et ainsi de suite...

Donc voici a présent la structure de ma table, comme tu me la décrit précédemment :
Nom : sql_6mai.png
Affichages : 77
Taille : 7,9 Ko

Nom : sql_6mai.png
Affichages : 77
Taille : 7,9 Ko

et ensuite je me suis donc servit de ta requête, que je comprend plus aisément (du faite que la structure est plus simple)

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
'select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
    select id_perso,
           case statut_perso when "absent"     then sum(time_to_sec(diff)) else 0 end as absent,
           case statut_perso when "disponible" then sum(time_to_sec(diff)) else 0 end as disponible
    from (
        select id_perso,
               statut_perso,
               ifnull(cast(timediff(date_fin, date_debut) as time), "00:00:00") as diff
        from disponibilite
		where id_perso = "'.je_passe_l'id de l'utilisateur_ici.'"
        order by id_perso, date_debut
    ) as x
    group by id_perso, statut_perso
) as y
group by id_perso'

j'ai juste rajouter la clause where pour afficher un utilisateurs spécifique et j'ai inversé timediff(date_fin, date_debut) car avant j'obtenais des sommes de temps négative, et tout fonctionne a merveille.

Qu'en pense tu ?

**Artemus24** · 07/05/2016, 11h48

Salut Gastoncs.

Avec seulement deux niveaux cette fois-ci et avec la date_fin dans la table :

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
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
SET collation_connection = latin1_general_ci
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `disponibilite`
--------------
 
--------------
CREATE TABLE `disponibilite`
(
  `id`            integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_perso`      integer unsigned NOT NULL,
  `statut_perso`  varchar(255)     NOT NULL,
  `date_debut`    datetime         NOT NULL,
  `date_fin`      datetime             NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `disponibilite` (`id_perso`,`statut_perso`, `date_debut`,`date_fin`) VALUES
  ( 1, 'disponible', '2015-12-09 13:48:13', '2015-12-09 13:53:11'),
  ( 1, 'absent',     '2015-12-09 13:53:11', '2015-12-09 13:54:51'),
  ( 1, 'disponible', '2015-12-09 13:54:51', '2015-12-10 10:05:22'),
  ( 1, 'absent',     '2015-12-10 10:05:22', '2015-12-10 22:34:01'),
  ( 1, 'disponible', '2015-12-10 22:34:01', '2015-12-10 22:36:52'),
  ( 1, 'absent',     '2015-12-10 22:36:52',                  null),
  (50, 'absent',     '2015-12-10 22:33:33', '2015-12-10 22:34:54'),
  (50, 'disponible', '2015-12-10 22:34:54',                  null)
--------------
 
--------------
select * from disponibilite order by id_perso, date_debut
--------------
 
+----+----------+--------------+---------------------+---------------------+
| id | id_perso | statut_perso | date_debut          | date_fin            |
+----+----------+--------------+---------------------+---------------------+
|  1 |        1 | disponible   | 2015-12-09 13:48:13 | 2015-12-09 13:53:11 |
|  2 |        1 | absent       | 2015-12-09 13:53:11 | 2015-12-09 13:54:51 |
|  3 |        1 | disponible   | 2015-12-09 13:54:51 | 2015-12-10 10:05:22 |
|  4 |        1 | absent       | 2015-12-10 10:05:22 | 2015-12-10 22:34:01 |
|  5 |        1 | disponible   | 2015-12-10 22:34:01 | 2015-12-10 22:36:52 |
|  6 |        1 | absent       | 2015-12-10 22:36:52 | NULL                |
|  7 |       50 | absent       | 2015-12-10 22:33:33 | 2015-12-10 22:34:54 |
|  8 |       50 | disponible   | 2015-12-10 22:34:54 | NULL                |
+----+----------+--------------+---------------------+---------------------+
--------------
select id_perso,
       sec_to_time(sum(absent))     as absent,
       sec_to_time(sum(disponible)) as disponible
from (
 
    select id_perso,
           statut_perso,
           case statut_perso when 'absent'     then sum(time_to_sec(ifnull(timediff(date_fin, date_debut), '00:00:00'))) else 0 end as absent,
           case statut_perso when 'disponible' then sum(time_to_sec(ifnull(timediff(date_fin, date_debut), '00:00:00'))) else 0 end as disponible
    from disponibilite
    group by id_perso, statut_perso
    order by id_perso, statut_perso
) as x
group by id_perso
order by id_perso
--------------
 
+----------+----------+------------+
| id_perso | absent   | disponible |
+----------+----------+------------+
|        1 | 12:30:19 | 20:18:20   |
|       50 | 00:01:21 | 00:00:00   |
+----------+----------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

En fait, il n'était pas nécessaire de remonter la date suivante dans la table en la baptisant "date_fin".
Si tu as regardé mon dernier message (post #6), j'ai une solution avec aussi seulement deux niveaux.

Mais en remontant la date, tu simplifies la requête.

Je pense que ton sujet est résolu.
N'oublie pas les '+1'. Merci !

@+

Requête d'addition de temps

Requêtes MySQL

Discussions similaires

Partager

Partager