Discussion :

[peofofo]

Bonjour,

Je cherche une solution pour créer une vue d'une table avec les dates intermédiaires:
J'ai une table:

datetime	valeur
2015-06-12 15:19:43	1
2015-06-15 16:47:37	0

Je cherche a connaitre le temps écoulé par JOUR, SEMAINE, MOIS, ANNEE lors du passage de valeur 1 à 0.
Seulement pour arriver a cet objectif, il me faut la table:

datetime_deb	valeur	datetime_fin
2015-06-12 15:19:43	1	2015-06-12 23:59:59
2015-06-13 00:00:00	1	2015-06-13 23:59:59
2015-06-14 00:00:00	1	2015-06-14 23:59:59
2015-06-15 00:00:00	1	2015-06-15 16:47:37

Seulement je ne vois pas comment ajouter des lignes:
J'en suis ici:

Code :

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

1
2
3
4
5
6
7
8
SELECT
ha.datetime, CONCAT(DATE_FORMAT(ha.datetime, '%Y-%m-%d '), '23:59:59') AS date_fin, ha.valeur,
(SELECT hi.datetime FROM historique hi WHERE value=0 AND (hi.datetime BETWEEN ha.datetime AND date_fin)) as date_term, 
(CASE WHEN valeur=1 THEN 
TIMESTAMPDIFF(SECOND, ha.datetime, '2015-06-15 16:47:37') ELSE 0 END) AS temps_ecoule
FROM historique ha
WHERE (datetime BETWEEN '2015-06-10 00:00:00' AND '2015-06-17 00:00:00')
GROUP BY DAY(datetime)

J'ai ceci:

datetime	date_fin	valeur	date_term	temps_ecoule
2015-06-12 15:19:43	2015-06-12 23:59:59	1	NULL	264474
2015-06-15 16:47:37	2015-06-15 23:59:59	0	2015-06-15 16:47:37	0

Merci de votre aide

[Drizzt [Drone38]]

Bonjour,

Je n'ai pas forcément compris le lien entre ta demande (temps écoulé par JOUR, MOIS, ...) et la requête intermédiaire que tu souhaites obtenir.

Je vais donc essayer de répondre selon deux approches.

Approche 1 : pour obtenir ton résultat intermédiaire.
Pour "ajouter" des lignes comme tu dis. Tu dois passer par une table "Calendrier" contenant l'ensemble des jours de la période. Une telle table est toujours utile à avoir dans sa BDD.

La requête peut ensuite s'écrire ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
select  timestamp(greatest(sc.datetime, t.datetime_deb)), timestamp(least(t.datetime_fin, date_add(sc.datetime, INTERVAL 24*3600-1 SECOND))) date_fin
 from
  (  select st1.datetime datetime_deb, st2.datetime datetime_fin
    from s_table st1
      join s_table st2 
    where st2.datetime = (select max(datetime) from s_table where smt_tmp.datetime > st1.datetime and valeur = 0)
      and st1.valeur = 1
  ) as t
  join s_calendar sc on DATE(sc.datetime) between DATE(t.datetime_deb) and DATE(t.datetime_fin)

Il y a certainement moyen de faire mieux pour la sous-requete de la table t qui permet de rapprocher le début de période ou la valeur est 1 et la fin de la période. L'objectif ici était juste de te montrer comment on peut utiliser une table calendrier dans ton cas.


Approche 2 : en calculant directement la différence
La seconde approche serait de calculer directement la différence comme tu le fais plus ou moins dans ta requête.
La encore tout est dans le rapprochement.

Code :

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

1
2
3
4
5
select TIMESTAMPDIFF(SECOND, st1.datetime,  st2.datetime), TIMESTAMPDIFF(DAY, st1.datetime,  st2.datetime) 
    from s_table st1
      join s_table st2 
    where st2.datetime = (select max(datetime) from smt_tmp where smt_tmp.datetime > st1.datetime and valeur = 0)
      and st1.valeur = 1

Tu peux améliorer le rapprochement en numérotant les lignes dans ta table source (si il y a une alternance de 1 et 0 chronologiquement).
Ainsi tu pourras simplement faire

Code :

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

1
2
3
4
select TIMESTAMPDIFF(SECOND, st1.datetime,  st2.datetime), TIMESTAMPDIFF(DAY, st1.datetime,  st2.datetime) 
    from s_table st1
      join s_table st2 on st2.compteur = st1.compteur+1
    where st1.valeur = 1

Si tu ne peux pas ajouter la numérotation dans ta table, il faut la calculer à la volée :

Code :

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

 select datetime, valeur, @num:=@num+1 FROM s_table st1, (select @num:=0) t ORDER BY datetime

[peofofo]

Merci pour ta réponse Drizzt,

Je crois que j'ai pas très bien expliqué mon problème.

datetime valeur
2015-06-12 15:19:43 1
2015-06-15 16:47:37 0

Dans la table ci-dessus, j'ai le moment de mise en marche valeur à 1 et le moment de mise à l'arret valeur à 0.
Je cherche à connaitre le temps de fonctionnement (passage de 1 à 0) c'est en gros le champs temps_ecoule.
Pour chercher le temps de fonctionnement par jour, semaine, mois, année, une solution consiste à écrire une table intermédaire avec les valeurs qui me permettent de faire un regroupement.

Je n'ai pas compris pourquoi tu me proposes de faire une jointure?

[Drizzt [Drone38]]

Tu veux donc un résultat du type

Semaine 1 : 360secondes
Semaine 2 : 500secondes ...

Dans ce cas l'approche 1 que j'ai présentée devrait te convenir. La table calendrier te permet d'ajouter les lignes manquantes à tes périodes et il te reste juste ensuite à regrouper par Semaine/Mois/Année selon le résultat voulu.