Discussion :

[punkoff]

Bonjour,


je m'auto-quote car le problème est le même :

Votre clause WHERE implique une restriction sur la table calendrier.

De ce fait, vous demandez au SGBD de ne considérer que les lignes qui ont actuellement une date comprise entre X et Y.

Si cette date est null (votre cas en fait) la ligne ne sera pas selectionnée.

Donc deux solutions (vori 3 mais bon) :
- rajouter un OR d.date_heure_locale IS NULL dans la clause WHERE
- remonter ce test au niveu de la jointure externe

[CinePhil]

Pour plus d'explication sur la condition de restriction et la jointure externe, voir mon billet de blog.

[nrpfc]

ok

effectivement en écrivant

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT r.id_station_stations, c.id_capteur, d.date_heure_locale, IFNULL(r.valeur, '-9999.99' ) AS valeur
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL

Ça marche nickel

J'ai une petite question qu'une des personnes, qui utilisera la base que je développe lors de mon stage, m'a demandé :"Peut on agencer les résultats ?"

Je m'explique actuellement quand j'envoie ma requête, j'obtiens une table comportant 4 colonnes id_station_stations, id_capteur, date_heure_locale et valeur.
On m'a demandé si on pouvait sortir les données sous la forme suivante :
id_station_stations, date_heure_locale et une colonne par capteur (de 0 à 82) avec leur valeur ...

Est ce possible ? et si oui pouvait me dire ou chercher parce que je n'ai rien trouver sur le sujet, je cherche encore ...

[CinePhil]

C'est faisable, je crois assez facilement, avec un SGBD qui sait exécuter des requêtes récursives... c'est à dire pas avec MySQL... d'où le conseil depuis le début de nos discussions d'utiliser un autre SGBD.

Sinon il faut se tourner du côté applicatif. Tu ordonnes le résultat de la requête en la terminant par ORDER BY puis c'est le logiciel qui s'occupe de la mise en forme des données avec un capteur par colonne en gérant les ruptures dans le parcours du tableau de résultat.

D'ailleurs, vouloir, en SQL, présenter des lignes en colonnes est l'une des erreurs fréquentes relevées par SQLPro et qu'il appelle de la cosmétique.

D'une manière générale, de toute façon, on ne donne pas accès directement au SGBD à un utilisateur humain ; on lui fournit un logiciel qui utilise la BDD. C'est beaucoup plus sûr car l'utilisateur ne peut faire que ce que le logiciel lui permet alors qu'avec un accès direct à la BDD, il peut tout casser !

L'accès au SGBD est à réserver au DBA et aux applications.

[nrpfc]

ok on en reviens donc aux limites de MySQL ...

Tu parles de logiciel qui utilise la BDD, j'imagine que pour MySQL il n'y a pas de tel logiciel en dehors de PhpMyadmin ?

je vais voir ce que je peux faire avec ORDER BY ...

Quoi qu'il en soit merci pour votre aide, elle m'a été d'un grand secours

[CinePhil]

Tu parles de logiciel qui utilise la BDD, j'imagine que pour MySQL il n'y a pas de tel logiciel en dehors de PhpMyadmin ?

Non, je parle d'une application développée spécialement pour le besoin de ton relevé de mesures, de l'importation des données dans la BDD, de l'étude des données récoltées...

Cela sort peut-être un peu du cadre de ton stage, ou peut-être que tu n'auras pas le temps de le faire, mais une BDD n'est qu'un entrepôt organisé. Il faut aussi prévoir le transpalettes, les chariots à roulettes et toute la logistique qui va avec pour exploiter ce qu'il y a dans l'entrepôt, c'est à dire un logiciel qui répond au besoin des utilisateurs en mettant à leur disposition une interface adaptée à leur travail.

PhpMyAdmin est un logiciel qui facilite l'administration de la BDD mais ce n'est pas un logiciel à mettre à disposition de l'utilisateur final car cela revient à lui donner accès à la BDD directement donc, potentiellement, à lui donner le marteau pour tout casser !

Derrière le forum sur lequel nous échangeons, il y a une base de données relationnelle mais les utilisateurs que nous sommes ne la voient jamais.

[nrpfc]

ok,

mais bon la je crois que ça dépasse un peu mes compétences, c'est du développement ça et je ne suis pas développeur je prépare un diplôme TSGERI (technicien supérieur en gestion et exploitation de ressources informatique).
Je suis déjà dépassé avec une base de données alors pour la prog je vais attendre de remonter mon niveau lol

J'ai déjà signalé à mes tuteurs de stage que MySql n’était pas adapté à nos besoins, je leur ai parlé de PostgreSQL, affaire a suivre mais je ne serais pas la pour voir l'évolution ...

je vais me concentrer sur ce qu'ils veulent que je fasse même si le résultat n'est pas formidable.

La requête sur laquelle je suis depuis ce matin à pour but de m'ajouter une colonne UTC dans un premier temps puis après il faudra que je fasse de même avec le temps écoule depuis le 1er janvier 2000 à minuit.

j'espere y arriver seul

je mettrais la requête ici quand j'y serais arrivé

[CinePhil]

Utilise les fonctions de date de MySQL pour ça.

[Fred_34]

J'ai une petite question qu'une des personnes, qui utilisera la base que je développe lors de mon stage, m'a demandé :"Peut on agencer les résultats ?"

Vu tes données ces deux requêtes pourraient fonctionner :

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
SELECT r.id_station_stations, d.date_heure_locale
	, SUM(CASE WHEN  c.id_capteur = 0 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v0
	, SUM(CASE WHEN  c.id_capteur = 1 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v1
...
	, SUM(CASE WHEN  c.id_capteur = 81 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v81
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL
GROUP BY r.id_station_stations, d.date_heure_locale

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT r.id_station_stations, d.date_heure_locale
	, GROUP_CONCAT(IFNULL(r.valeur, '-9999.99' ) ORDER BY c.id_capteur ASC)
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL
GROUP BY r.id_station_stations, d.date_heure_locale

[nrpfc]

Wahou,

merci pour ta réponse Fred_34

j'ai testé tes requêtes, pour la 1ere j'ai un message d'erreur

Code :

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

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '... , SUM(CASE WHEN c.id_capteur = 82 THEN IFNULL(r.valeur, '-9999.99' ) ELSE ' at line 4

la seconde fonctionne mais me sort les résultats sous 3 colonnes avec de mauvaises valeurs, j'ai joins un extrait des résultats.

J'en ai parlé à mon tuteur, qui m'a dit de voir ça à la fin si il me restait du temps, apparemment c'est juste une question de rendu ...

Sur mon problème de datetime UTC j'ai bien suivis les instructions suivantes Time-zone-supportl

mais quand je fait

select convert_tz(now(), 'Europe/Paris', 'UTC');

j'obtiens un résultat NULL, ce qui m'étonne beaucoup

[nrpfc]

bon je pense que je touche au but avec la colonne du temps écoulé depuis le 1er janvier 2000

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT IFNULL(r.id_station_stations,'1') AS id_station_stations, c.id_capteur, d.date_heure_locale, DATEDIFF(date_heure_locale,'2000/01/01 00:00:00') AS temps_ecoule, IFNULL(r.valeur, '-9999.99' ) AS valeur
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL

le seul Hic c'est que je pensais qu'en mettant DATEDIFF(date_heure_locale,'2000/01/01 00:00:00'), ça m'aurait fait le calcul avec date_heure_local de la ligne concerné mais ça aurait été trop facile.
La j'ai la valeur 4383 partout ???

[CinePhil]

Ta date est mal formatée alors MySQL ne doit pas la comprendre comme une date je pense.

En SQL, un DATETIME se formate 'aaaa-mm-jj hh:mm:ss'.

En plus :

DATEDIFF(expr,expr2)
DATEDIFF() retourne le nombre de jours entre la date de début expr et la date de fin expr2.

D'ailleurs, la traduction de la doc en français est fausse car la différence est bien expr - expr2 d'après les exemples !

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1

Mais j'imagine que le nombre de jours est insuffisamment précis pour toi non ?

Regarde du côté de la fonction UNIX_TIMESTAMP pour convertir et faciliter le calcul.

[Fred_34]

j'ai testé tes requêtes, pour la 1ere j'ai un message d'erreur

Code :

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

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '... , SUM(CASE WHEN c.id_capteur = 82 THEN IFNULL(r.valeur, '-9999.99' ) ELSE ' at line 4

Il faut remplacer "..." par 82 CASE !

la seconde fonctionne mais me sort les résultats sous 3 colonnes avec de mauvaises valeurs, j'ai joins un extrait des résultats.

La 3eme colonne est censée contenir les 82 valeurs.
Bizarre que les valeurs soient mauvaises. Es-tu sûr quelles sont juste avec ta requête initiale ?

[nrpfc]

oui en effet, je n'avais vu ça comme ça.

Code :

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

1
2
SELECT DATEDIFF('2012/01/01 00:00:00','2011/01/01 00:00:00');
->365

ça me calcul des jours moi il me faut des heures voir des minutes. Je continue mes investigations avec UNIX_TIMESTAMP

Il faut remplacer "..." par 82 CASE !

J'y ai bien pensé

La 3eme colonne est censée contenir les 82 valeurs.
Bizarre que les valeurs soient mauvaises. Es-tu sûr quelles sont juste avec ta requête initiale ?

oui, les valeurs de ma requête initiale sont bonnes, je la redonne au cas ou on ne parlerais pas de la même, elle a subbit quelques modification mais elle fonctionne nickel

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT IFNULL(r.id_station_stations,'1') AS id_station_stations, c.id_capteur, d.date_heure_locale, IFNULL(r.valeur, '-9999.99' ) AS valeur
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL

je regarde tout ça, merci pour votre aide.

[nrpfc]

Bonjour a tous

Ce matin grosse réunion ou je vais pouvoir faire le point avec toutes les personnes concernées par mon projet.

Je suis assez impatient

hier soir j'ai essayer de reprendre

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
SELECT r.id_station_stations, d.date_heure_locale
	, SUM(CASE WHEN  c.id_capteur = 0 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v0
	, SUM(CASE WHEN  c.id_capteur = 1 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v1
...
	, SUM(CASE WHEN  c.id_capteur = 82 THEN IFNULL(r.valeur, '-9999.99' ) ELSE 0 END) AS v82
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL
GROUP BY r.id_station_stations, d.date_heure_locale

si je vire les ... et que je fait chaque ligne a la main ça marche presque si ce n'est que j'ai que des 0 ou des -9999,99 dans les colonnes valeurs mais j'ai la présentation que je cherchais ce qui est déjà une bonne piste.

bon faut que j'aille a ma réunion a plus tard

[nrpfc]

bonjour à tous,

donc si je veux tout les capteurs de la station 1 entre le 1er janvier 2012 00h00 et le 2 janvier 2012 02h00 avec une colonne me disant combien de temps c'est écoulé en minutes depuis le 1er janvier 2000 00h00 :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT IFNULL(r.id_station_stations,'1') AS id_station_stations, 
c.id_capteur, d.date_heure_locale, 
TIMESTAMPDIFF(MINUTE,d.date_heure_locale,'2000-01-01 00:00:00') AS temps_ecoule, IFNULL(r.valeur, '-9999.99' ) AS valeur
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL

Suite à la réunion de vendredi il s'avère que le coté esthétique des résultats soit assez important pour les chercheurs.
donc il va falloir que je trouve une solution à ce problème
Si vous avez des suggestions pour orienter mes recherches je suis preneur.
Il me reste la requête pour la colonne date_heure_UTC a finaliser et apres je devrais m'occuper de ce problème.

[nrpfc]

bon j'ai un soucis avec ma colonne date_heure_UTC

Pour créer une colonne date_heure_utc je pensais rajouter dans mon select

Code :

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

convert_tz(d.date_heure_locale, 'Europe/Paris', 'UTC')

mais quand je fait

Code :

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

select convert_tz(now(), 'Europe/Paris', 'UTC');

j'obtiens un résultat NULL ...
alors que si je fait

Code :

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

SELECT UTC_TIMESTAMP()

ça me donne bien l'heure UTC actuelle

je ne comprend pas

[nrpfc]

j'ai trouvé pourquoi la requête suivante ne fonctionne

Code :

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

SELECT convert_tz(now(), 'Europe/Paris', 'UTC');

c'est que les tables time_zone de la base de MySQL ne sont pas complétés, le problème étant que je n'ai trouvé nul part comment faire pour les compléter sur windows, j'ai trouvé pour MACos ou Linux mais apparemment pour windows c'est la galère ...


J'ai récupéré les données time_zone, je les ai bien copié après avoir arrêté MySQL et quand je le redémarre je n'ai rien de changé ...

Si quelqu'un sait comment faire ou sait ou trouver l'information merci de me la communiquer

[nrpfc]

bon j'ai grugé, j'ai ete chez un ami qui tourne sur un OS linux et j'ai recupéré le contenu des tables Time_Zone, je sais ce n'est pas très conventionnel mais bon fallait bien trouver une solution.

Donc pour avoir une colonne date_heure_UTC en plus la requête est

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT IFNULL(r.id_station_stations,'1') AS id_station_stations, 
c.id_capteur, d.date_heure_locale, CONVERT_TZ(d.date_heure_locale, 'Europe/Paris', 'UTC') AS date_heure_UTC, 
TIMESTAMPDIFF(MINUTE,d.date_heure_locale,'2000-01-01 00:00:00') AS temps_ecoule, IFNULL(r.valeur, '-9999.99' ) AS valeur
FROM   capteur c
       CROSS JOIN calendrier d 
       LEFT OUTER JOIN relever_mesure r
              ON r.id_capteur_capteur=c.id_capteur
                 AND d.id_calendrier=r.id_calendrier_calendrier
                 AND r.id_station_stations=1
 
WHERE d.date_heure_locale BETWEEN '2012/01/01 00:00:00'
                          AND     '2012/01/02 00:00:00'
OR d.date_heure_locale IS NULL

YOUPI ça marche nickel

Bon ben me reste le problème d'agencement de mes données, je vais regarder les pistes que vous m'avez donnés mais si vous avez de nouvelles suggestions je suis preneur

[CinePhil]

Suite à la réunion de vendredi il s'avère que le coté esthétique des résultats soit assez important pour les chercheurs.

La solution est dans le développement d'une application qui exploite la base de données.

Ne laisse pas n'importe qui mettre les doigts directement dans le SGBD, c'est trop risqué !
Même en tant que DBA expérimenté, on n'est pas à l'abri d'une mauvaise manip qui casse les données, voire la BDD entière !

Il y a des opérations qu'on fait avec une petite goutte de sueur au coin du front quand on est DBA.