Calcul des repos utilisateurs (interval)

**khalidlyon** · 13/04/2023, 17h26

Bonjour
Avec Php, MySql
Je souhaite faire une requête pour afficher les repos qui dépassent 30min.

Ma table est la suivante :

CREATE TABLE `actions` (
`id` int(11) UNSIGNED NOT NULL,
`userId` int(11) DEFAULT NULL,
`date` int(11) DEFAULT NULL,
`action` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Je souhaite afficher les résultats par jour :

Nom : relance.png
Affichages : 119
Taille : 6,4 Ko

Ma solution était de lister tous les enregistrements et boucler via PHP et comparer ... mais je suis sur et certain qu'on peux le faire via Mysql.

Code :

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

SELECT * from actions order by date, userId

Voyez-vous de meilleures solutions ?

Merci d'avance pour vos pistes.

**Séb.** · 13/04/2023, 19h22

Ce serait bien avec un extrait significatif de ta table et le résultat attendu en fonction.

Dans tes actions je vois une colonne "date" codée un INT, c'est ça qui contient l'horodatage ? Il faudrait une colonne DATETIME.

**binarygirl** · 13/04/2023, 20h42

Une piste: trier les données et utiliser le window functions telles que LAG.
Puisque j'imagine que dans un premier temps, il faut calculer la différente entre deux datetimes.

**Artemus24** · 13/04/2023, 21h13

Salut à tous.

Code mysql :

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
--------------
START TRANSACTION
--------------
 
--------------
set session collation_connection = "utf8mb4_bin"
--------------
 
--------------
set names "utf8mb4" collate "utf8mb4_bin"
--------------
 
--------------
set names "utf8mb4"
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `utf8mb4`
        DEFAULT COLLATE       `utf8mb4_bin`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`       integer unsigned NOT NULL auto_increment PRIMARY KEY,
  `userid`   varchar(255)     NOT NULL,
  `arret`    datetime         NOT NULL,
  `relance`  datetime         NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8mb4` COLLATE=`utf8mb4_bin`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`userid`,`arret`,`relance`) VALUES
  ('UserOne','2023-04-05 09:10:00','2023-04-05 09:25:00'),
  ('UserOne','2023-04-05 11:10:00','2023-04-05 12:45:00'),
  ('UserOne','2023-04-05 16:00:00','2023-04-05 16:45:00'),
  ('UserTwo','2023-04-05 10:00:00','2023-04-05 10:15:00'),
  ('UserTwo','2023-04-05 12:30:00','2023-04-05 14:16:00')
--------------
 
--------------
select * from `test`
--------------
 
+----+---------+---------------------+---------------------+
| id | userid  | arret               | relance             |
+----+---------+---------------------+---------------------+
|  1 | UserOne | 2023-04-05 09:10:00 | 2023-04-05 09:25:00 |
|  2 | UserOne | 2023-04-05 11:10:00 | 2023-04-05 12:45:00 |
|  3 | UserOne | 2023-04-05 16:00:00 | 2023-04-05 16:45:00 |
|  4 | UserTwo | 2023-04-05 10:00:00 | 2023-04-05 10:15:00 |
|  5 | UserTwo | 2023-04-05 12:30:00 | 2023-04-05 14:16:00 |
+----+---------+---------------------+---------------------+
--------------
select `id`,`userid`
 from `test`
where timestampdiff(minute,arret,relance) > 30
--------------
 
+----+---------+
| id | userid  |
+----+---------+
|  2 | UserOne |
|  3 | UserOne |
|  5 | UserTwo |
+----+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

**khalidlyon** · 14/04/2023, 15h25

Bonjour,
Merci infiniment pour vos réponses.

la date est int() car c'est un Timestamp time() et pour les afficher j'utilise:

Code :

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

1
2
SELECT id, FROM_UNIXTIME(date), userId, action, date FROM `actions`  
ORDER BY `actions`.`userId`  DESC, `actions`.`date`  DESC

ma table est :

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
 
 
CREATE TABLE `actions` (
  `id` int(11) UNSIGNED NOT NULL,
  `userId` int(11) DEFAULT NULL,
  `date` int(11) DEFAULT NULL,
  `action` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO `actions` (`id`, `userId`, `date`, `action`) VALUES
(1, 45, 1681223015, 'edit'),
(2, 55, 1681225805, 'edit'),
(3, 55, 1681223685, 'edit'),
(4, 55, 1681220885, 'add'),
(5, 55, 1681215885, 'edit'),
(6, 55, 1681223185, 'edit'),
(7, 55, 1681221885, 'edit'),
(8, 45, 1681225885, 'add'),
(9, 55, 1681220885, 'delete'),
(10, 55, 1681225085, 'add'),
(11, 55, 1681223885, 'add'),
(12, 55, 1681205885, 'edit'),
(13, 55, 1681220189, 'edit'),
(14, 55, 1681221885, 'edit'),
(15, 55, 1681225885, 'add');
 
 
ALTER TABLE `actions`
  ADD PRIMARY KEY (`id`);
 
 
ALTER TABLE `actions`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;COMMIT;

le but est de trouver les pauses (30min sans action) de l'utilisateur. en vrai le champ action ne sera pas utilisé dans la requête, il faut juste la différence de 2 date successive.

Nom : mysql.png
Affichages : 83
Taille : 121,6 Ko

Nom : mysql.png
Affichages : 83
Taille : 121,6 Ko

et résulat attendu est :

Code :

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

1
2
3
4
5
userId   		arret  					relance
45				2023-04-11 16:23:35		2023-04-11 17:11:25
55				2023-04-11 11:38:05		2023-04-11 14:24:45
55				2023-04-11 14:24:45		2023-04-11 15:36:29
55				2023-04-11 17:10:05		2023-04-11 17:11:25

Merci encore.

**Artemus24** · 14/04/2023, 20h24

Salut à tous.

Il nous faut des explications clairs, un jeu d'essai représentatif de votre problème et le résultat attendu.
Nous n'aurions pas perdu du temps à comprendre ce que vous cherchiez à faire si vous aviez respecté le fonctionnement du forum.

Comme l'indique Séb., la colonne de nom "date" doit être du type "datetime", même si elle est au format unix.
Sans cela, on ne peut pas faire des calculs sur ces dates.

De plus, votre exemple est erroné !
Pourquoi votre critère a changé et n'est plus de 30 minutes mais de 20 minutes ?

Voici l'exemple correspondant à votre demande :

Code mysql :

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
--------------
START TRANSACTION
--------------

--------------
set session collation_connection = "latin1_general_ci"
--------------

--------------
DROP DATABASE IF EXISTS `base`
--------------

--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------

--------------
DROP TABLE IF EXISTS `test`
--------------

--------------
CREATE TABLE `test`
(  `id`      integer  unsigned NOT NULL primary key,
   `userid`  smallint unsigned NOT NULL,
   `date`    datetime          NOT NULL,
   `action`  char(06)          NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------

--------------
insert into `test` (`id`,`userId`,`date`,`action`) VALUES
  ( 1,45,from_unixtime(1681223015),'edit'),( 2,55,from_unixtime(1681225805),'edit'),( 3,55,from_unixtime(1681223685),'edit'),
  ( 4,55,from_unixtime(1681220885),'add'), ( 5,55,from_unixtime(1681215885),'edit'),( 6,55,from_unixtime(1681223185),'edit'),
  ( 7,55,from_unixtime(1681221885),'edit'),( 8,45,from_unixtime(1681225885),'add'), ( 9,55,from_unixtime(1681220885),'delete'),
  (10,55,from_unixtime(1681225085),'add'), (11,55,from_unixtime(1681223885),'add'), (12,55,from_unixtime(1681205885),'edit'),
  (13,55,from_unixtime(1681220189),'edit'),(14,55,from_unixtime(1681221885),'edit'),(15,55,from_unixtime(1681225885),'add')
--------------

--------------
select * from `test`
--------------

+----+--------+---------------------+--------+
| id | userid | date                | action |
+----+--------+---------------------+--------+
|  1 |     45 | 2023-04-11 16:23:35 | edit   |
|  2 |     55 | 2023-04-11 17:10:05 | edit   |
|  3 |     55 | 2023-04-11 16:34:45 | edit   |
|  4 |     55 | 2023-04-11 15:48:05 | add    |
|  5 |     55 | 2023-04-11 14:24:45 | edit   |
|  6 |     55 | 2023-04-11 16:26:25 | edit   |
|  7 |     55 | 2023-04-11 16:04:45 | edit   |
|  8 |     45 | 2023-04-11 17:11:25 | add    |
|  9 |     55 | 2023-04-11 15:48:05 | delete |
| 10 |     55 | 2023-04-11 16:58:05 | add    |
| 11 |     55 | 2023-04-11 16:38:05 | add    |
| 12 |     55 | 2023-04-11 11:38:05 | edit   |
| 13 |     55 | 2023-04-11 15:36:29 | edit   |
| 14 |     55 | 2023-04-11 16:04:45 | edit   |
| 15 |     55 | 2023-04-11 17:11:25 | add    |
+----+--------+---------------------+--------+
--------------
select   `userid`,
         `date` as arret,
         `val`  as relance,
         `duree`

    from (      select `id`,
                       `userid`,
                       `date`,
                       @val:=if(`userid`=@prev, @val, NULL) as val,
                       timestampdiff(minute,`date`,@val) as duree,
                       @val:=`date`,
                       @prev:=`userid`

                  from `test`
            cross join ( SELECT @val:=NULL,@prev=NULL) as x
              order by `userid` desc, `date` desc
         ) as x

   where `duree` >= 20
order by `userid`, `date`
--------------

+--------+---------------------+---------------------+-------+
| userid | arret               | relance             | duree |
+--------+---------------------+---------------------+-------+
|     45 | 2023-04-11 16:23:35 | 2023-04-11 17:11:25 |    47 |
|     55 | 2023-04-11 11:38:05 | 2023-04-11 14:24:45 |   166 |
|     55 | 2023-04-11 14:24:45 | 2023-04-11 15:36:29 |    71 |
|     55 | 2023-04-11 16:04:45 | 2023-04-11 16:26:25 |    21 |
|     55 | 2023-04-11 16:38:05 | 2023-04-11 16:58:05 |    20 |
+--------+---------------------+---------------------+-------+
--------------
COMMIT
--------------

Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

**Séb.** · 14/04/2023, 23h47

Plus bas ma solution avec CTE + Fonction d'analyse LAG() (MySQL 8)

DDL + Data (je ne travaille ni avec des données temporelles codées en INT, et ni des colonnes nommées "date") :

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
set @@sql_mode = 'ANSI,TRADITIONAL';
 
drop temporary table if exists actions;
 
create temporary table actions (
  id int primary key,
  userId int,
  actionAt datetime,
  action tinytext
);
 
insert into actions (id, userId, actionAt, action)
values
(1, 45, from_unixtime(1681223015), 'edit'),
(2, 55, from_unixtime(1681225805), 'edit'),
(3, 55, from_unixtime(1681223685), 'edit'),
(4, 55, from_unixtime(1681220885), 'add'),
(5, 55, from_unixtime(1681215885), 'edit'),
(6, 55, from_unixtime(1681223185), 'edit'),
(7, 55, from_unixtime(1681221885), 'edit'),
(8, 45, from_unixtime(1681225885), 'add'),
(9, 55, from_unixtime(1681220885), 'delete'),
(10, 55, from_unixtime(1681225085), 'add'),
(11, 55, from_unixtime(1681223885), 'add'),
(12, 55, from_unixtime(1681205885), 'edit'),
(13, 55, from_unixtime(1681220189), 'edit'),
(14, 55, from_unixtime(1681221885), 'edit'),
(15, 55, from_unixtime(1681225885), 'add');

Requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
with dataset as (
	select all 
		userId as "ID utilisateur", 
		lag(actionAt) over (partition by userId order by actionAt asc) as "Arrêt",
		actionAt as "Relance",
		timediff(actionAt, lag(actionAt) over (partition by userId order by actionAt asc)) as "Temps d'arrêt"
	from actions
)
select all *
from dataset 
where "Temps d'arrêt" >= time'00:30:00';

Résultat :

Code :

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

1
2
3
4
ID utilisateur  Arrêt                   Relance                 Temps d'arrêt
45              2023-04-11 16:23:35     2023-04-11 17:11:25     00:47:50
55              2023-04-11 11:38:05     2023-04-11 14:24:45     02:46:40
55              2023-04-11 14:24:45     2023-04-11 15:36:29     01:11:44

**khalidlyon** · 15/04/2023, 00h48

Merci infiniment Séb.

Malheureusement je suis encore avec une ancienne version 5.0.12

un équivalent de ta requête stp.

Merci encore

**Artemus24** · 15/04/2023, 02h08

Salut à tous.

Autre solution :

Code mysql :

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
--------------
select     t1.`userid`,
           t1.`date`  as arret,
           t2.`date`  as relance,
           timestampdiff(minute,t1.`date`,t2.`date`) as duree
 
      from `test` as t1
inner join `test` as t2
        on t2.`userid` = t1.`userid`
       and t2.`date` = ( select min(`date`)
                           from `test`
                          where `userid` = t1.`userid`
                            and `date`   > t1.`date`
                       )
    having duree > 30
  order by t1.`userid`, t1.`date`
--------------
 
+--------+---------------------+---------------------+-------+
| userid | arret               | relance             | duree |
+--------+---------------------+---------------------+-------+
|     45 | 2023-04-11 16:23:35 | 2023-04-11 17:11:25 |    47 |
|     55 | 2023-04-11 11:38:05 | 2023-04-11 14:24:45 |   166 |
|     55 | 2023-04-11 14:24:45 | 2023-04-11 15:36:29 |    71 |
+--------+---------------------+---------------------+-------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Celle-ci est moins performante que ma précédente solution.

Cordialement.
Artemus24.
@+

**Séb.** · 15/04/2023, 02h11

MySQL 5.0.12 date de 2005 et des exploits sont reportés, ce sera sans moi.

Tu peux essayer la solution d'Artemus24.

**khalidlyon** · 15/04/2023, 12h17

Merci infiniment Séb. pour les nouveaux projets je vais me mettre en version 8, il faut suivre

Merci infiniment Artemus24. J'ai lancé la requête chez moi avec les mêmes données mais ça donne rien même avec durée > 2, je vais analyser le pourquoi.
Merci encore à vous tous.

Après analyse, c'est parce que mon champs est un entier et ta requête se base sur datetime.
vous êtes les meilleurs.
Merci encore et bonne journée

Calcul des repos utilisateurs (interval)

Requêtes MySQL

Discussions similaires

Partager

Partager