Comment calculer la différence entre 2 points

requête de calcul de différence entre 2 données

Sujet résolu

5 mai 2022 à 8:42:09

Bonjour, je me pose une nouvelle question (toujours...) je souhaitais calculer la différence d'une donnée (poids d'une ruche) entre 2 points horaires.

Je m'explique j'ai (entre autre) dans ma table un horodatage (stamptime) et un poids (mas en kg) stocké toutes le 4 mn environ par ruche.

Je souhaitais afficher sur une page en plus de la liste de tous les enregistrements d'une ruche (ça c'est fait) des informations complémentaires du genre évolution du poids de la ruche sur 24 heures 48,72, un semaine ,un mois.

J'ai donc parcouru le web à la recherche d'une requête permettant de faire une soustraction et là surprise je n'ai rien trouvé (est-ce du à une mauvaise tournure de recherche de ma part?)

j'ai trouvé ceci :

https://www.numelion.com/calculs-dans-une-base-de-donnees-mysql.html

mais on parle de moyenne, de somme, de max de min, d'écart type mais pas de soustraction...

Quelqu'un sait il comment orienter ma recherche pour soustraire 2 données (poids en kg) entre des points distant dans le temps (horodatage) de 24h,48h,72h,..

Du coup j'ai fais un peu comme cela mais cela me parait du bidouillage :

$reponse1 = $bdd->query("SELECT horodatage, mas From Telemesures_ruches WHERE (id_ruche) = $NumRuche AND (horodatage) = CURRENT_DATE ORDER BY DATE (horodatage) ASC");
$donnees1 = $reponse->fetch();

$reponse2 = $bdd->query("SELECT horodatage, mas From Telemesures_ruches WHERE (id_ruche) = $NumRuche AND DATE (horodatage) = CURRENT_DATE -1 ORDER BY DATE (horodatage) ASC");
$donnees2 = $reponse2->fetch();

$reponse3 = $bdd->query("SELECT horodatage, mas From Telemesures_ruches WHERE (id_ruche) = $NumRuche AND DATE (horodatage) = CURRENT_DATE -2 ORDER BY DATE (horodatage) ASC");
$donnees3 = $reponse3->fetch();



$result1 = $donnees1['mas']-$donnees2['mas'];
$result2 = $donnees1['mas']-$donnees3['mas'];

echo '<table>';
echo  '<tr><th>Variation poids jour en cours :</th><th>'.$result1.'Kg</tr>';
echo  '<tr><th>Variation poids sur 24 heures :</th><th>'.$result2.'Kg</tr>';


echo '</table>';

Merci

-
Edité par GillesMangin-Voirin 5 mai 2022 à 13:02:40

Benzouye

5 mai 2022 à 18:55:01

Bonjour,

GillesMangin-Voirin a écrit:

j'ai fais un peu comme cela mais cela me parait du bidouillage

Les trois requêtes de ton code ci-dessus ne peuvent pas retourner de valeur car la colonne horadatage continet un DATETIME et CURRENT_DATE retourne une DATE ... Mais même en corrigeant avec NOW() (qui retourne un DATETIME) cela ne ferait pas l'affaire ... ou alors vraiment dans un cas de chance particulière où il existerait un horodatage exactement égal, à la seconde près, à la date d'exécution de script ... Un vrai coup de bol !

GillesMangin-Voirin a écrit:

je souhaitais calculer la différence d'une donnée (poids d'une ruche) entre 2 points horaires

Ton problème ne présente pas solution évidente ... Il faut fonctionner en plusieurs étapes :

déterminer la plus vieille mesure
déterminer la plus récente mesure
récupérer ces deux mesures
soustraire les valeurs

Exemple pour une semaine :

-- Mesure la plus récente de la semaine
SELECT mas
FROM Telemesures_ruches
WHERE
	id_ruche = '...'
	AND horodatage = (
		SELECT MAX( horodatage )
		FROM Telemesures_ruches
		WHERE
			id_ruche = '...'
			AND horodatage >= NOW() - INTERVAL 1 WEEK
	);

-- Mesure la plus ancienne de la semaine
SELECT mas
FROM Telemesures_ruches
WHERE
	id_ruche = '...'
	AND horodatage = (
		SELECT MIN( horodatage )
		FROM Telemesures_ruches
		WHERE
			id_ruche = '...'
			AND horodatage >= NOW() - INTERVAL 1 WEEK
	);

Et soustraire les deux valeurs obtenues ...

On pourrait simplifier avec une clause LIMIT :

-- Mesure la plus récente
SELECT mas
FROM Telemesures_ruches
WHERE
	id_ruche = '...'
	AND horodatage >= NOW() - INTERVAL 1 WEEK
ORDER BY horodatage DESC
LIMIT 1;

-- Mesure la plus ancienne
SELECT mas
FROM Telemesures_ruches
WHERE
	id_ruche = '...'
	AND horodatage >= NOW() - INTERVAL 1 WEEK
ORDER BY horodatage ASC
LIMIT 1;

Et si vraiment, tout mettre dans une seule requête :

SELECT
	(
		-- Mesure la plus récente
		SELECT mas
		FROM Telemesures_ruches
		WHERE
			id_ruche = '...'
			AND horodatage >= NOW() - INTERVAL 1 WEEK
		ORDER BY horodatage DESC
		LIMIT 1
	) mas_old,
	(
		-- Mesure la plus ancienne
		SELECT mas
		FROM Telemesures_ruches
		WHERE
			id_ruche = '...'
			AND horodatage >= NOW() - INTERVAL 1 WEEK
		ORDER BY horodatage ASC
		LIMIT 1
	) mas_new;

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

GillesMangin-Voirin

5 mai 2022 à 20:45:19

Merci je vais regarder cela.

Cela fonctionnait pour le jour -1,-2 et -3 (peut être comme tu le dis un coup de bol) mais pas pour la semaine ni le mois...

Dès que j'ai essayé je te dis ce qu'il en est!

Voilà c'est testé : nickel comme d'habitude , j'ai opté pour la solution 2 car comme je voulais afficher plusieurs delta

Merci encore

Alors dans la série les mystères de l'Ouest ou comment y perdre son latin...

Lors du calcul sur la différence journalière j'ai parfois (sur certaines ruches (principalement la 1 et 5)) un delta journalier (que celui-là (en tout cas actuellement...)

Là je viens d'avoir sur ma ruche 2 le calcul à 7 jours avec le même genre de chiffre :

Variation du poids sur les 24 dernières heures :	-0.1Kg	2022-05-05 09:27:07 heure	57.9Kg
Variation du poids sur les 48 dernières heures :	0.2Kg	2022-05-04 09:25:22 heure	57.6Kg
Variation du poids sur les 72 dernières heures :	-0.40000000000001Kg	2022-05-03 09:28:18 heure	58.2Kg
Variation du poids sur la dernière semaine :	-1.5Kg	2022-04-29 09:26:24 heure	59.3Kg
Variation du poids sur les 2 dernières semaine :	-1.1Kg	2022-04-22 09:26:39 heure	58.9Kg
Variation du poids sur les 3 dernières semaine :	1.2Kg	2022-04-15 09:25:50 heure	56.6Kg
Variation du poids sur le derniers mois :	15.1Kg	2022-04-06 09:28:27 heure	42.7Kg
Variation du poids sur les 2 derniers mois :	-1.1Kg	2022-04-22 09:26:39 heure	58.9Kg

du genre 0.40000000000001 kg au lieu de 0.4 kg

J'ai vérifié les mesures de mas stockés (en plus elle s'affichent) elles n'ont qu'un seul chiffre derrière la virgule

au niveau des Arduino ils ont tous le même programme et je transforme le float du poids (units) comme ceci :

 dtostrf(units,4,1,unitsX);                           // conversion float en char du poids 4 chiffres dont 1 derrière la virgule

 unitsX2 = "";                                         	// réinitialisation de la chaine pour la prochaine utilisation

 

 for(int i = 0; i < unitsX1.length(); i++) {           		// suppression des éventuels espaces si pas assez de chiffres



 if(unitsX1[i] !=' ') unitsX2 += unitsX1[i];        		// recherche espace et suppression dans la nouvelle chaine.

  queryString = "?id=" + numRuche +"&hyg=" + hygX2 + "&tex=" + tX2 + "&tin="+ tinX2 + "&mas=" + unitsX2 + "&lum=" + lumX2 + "&eau=" + eauX1;           // création de la chaine à envoyer au serveur personnel

puis je transmet .

Au niveau du Raspberry le code est identique pour chaque ruche puisque une seule page pour toutes!

Voici le code :

(mais j'avais déjà constaté ce problème... mais je croyais qu'il ne touchait que mes affichages par interface Android avant de voir que mon PC était atteint aussi...)

/*requete affichage données*/

$reponse = $bdd->query("SELECT * FROM Telemesures_ruches WHERE (id_ruche) = $NumRuche AND horodatage >= NOW() - INTERVAL 2 WEEK ORDER BY horodatage DESC");




//Mesure la plus récente
$reponse0 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 1 WEEK ORDER BY horodatage DESC LIMIT 1");

$donnees0 = $reponse0->fetch();


//Mesure la plus ancienne il y a un jour
$reponse1 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 1 DAY ORDER BY horodatage ASC LIMIT 1");

$donnees1 = $reponse1->fetch();


//Mesure la plus ancienne il y a deux jours
$reponse2 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 2 DAY ORDER BY horodatage ASC LIMIT 1");

$donnees2 = $reponse2->fetch();


//Mesure la plus ancienne il y a trois jours
$reponse3 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 3 DAY ORDER BY horodatage ASC LIMIT 1");

$donnees3 = $reponse3->fetch();


//Mesure la plus ancienne il y a une semaine
$reponse4 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 1 WEEK ORDER BY horodatage ASC LIMIT 1");

$donnees4 = $reponse4->fetch();


//Mesure la plus ancienne il y a deux semaines
$reponse5 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 2 WEEK ORDER BY horodatage ASC LIMIT 1");

$donnees5 = $reponse5->fetch();


//Mesure la plus ancienne il y a trois semaines
$reponse6 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 3 WEEK ORDER BY horodatage ASC LIMIT 1");

$donnees6 = $reponse6->fetch();


//Mesure la plus ancienne il y a un mois
$reponse7 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 1 MONTH ORDER BY horodatage ASC LIMIT 1");

$donnees7 = $reponse7->fetch();


//Mesure la plus ancienne il y a deux mois
$reponse8 = $bdd->query("SELECT horodatage, mas FROM Telemesures_ruches
WHERE id_ruche = $NumRuche AND horodatage >= NOW() - INTERVAL 2 WEEK ORDER BY horodatage ASC LIMIT 1");

$donnees8 = $reponse8->fetch();

// calculs des deltas

$result1 = $donnees0['mas']-$donnees1['mas'];		// calcul sur les dernieres 24h
$result2 = $donnees0['mas']-$donnees2['mas'];		// calcul sur les dernieres 48h
$result3 = $donnees0['mas']-$donnees3['mas'];		// calcul sur les dernieres 72h
$result4 = $donnees0['mas']-$donnees4['mas'];		// calcul sur la semaine
$result5 = $donnees0['mas']-$donnees5['mas'];		// calcul sur 2 semaines
$result6 = $donnees0['mas']-$donnees6['mas'];		// calcul sur 3 semaines
$result7 = $donnees0['mas']-$donnees7['mas'];		// calcul sur le mois
$result8 = $donnees0['mas']-$donnees8['mas'];		// calcul sur 2 mois

//affichage tableau delta
echo '<table>';
echo  '<tr><th></th><th>Delta</th><th>Horodatage point</th><th>Mesure</th></tr>';
echo  '<tr><th>Variation du poids sur les 24 dernières heures :</th><th>'.$result1.'Kg</th><th>'.$donnees1['horodatage'].' heure</th><th>'.$donnees1['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur les 48 dernières heures :</th><th>'.$result2.'Kg</th><th>'.$donnees2['horodatage'].' heure</th><th>'.$donnees2['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur les 72 dernières heures :</th><th>'.$result3.'Kg</th><th>'.$donnees3['horodatage'].' heure</th><th>'.$donnees3['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur la dernière semaine :</th><th>'.$result4.'Kg</th><th>'.$donnees4['horodatage'].' heure</th><th>'.$donnees4['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur les 2 dernières semaine :</th><th>'.$result5.'Kg</th><th>'.$donnees5['horodatage'].' heure</th><th>'.$donnees5['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur les 3 dernières semaine :</th><th>'.$result6.'Kg</th><th>'.$donnees6['horodatage'].' heure</th><th>'.$donnees6['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur le derniers mois :</th><th>'.$result7.'Kg</th><th>'.$donnees7['horodatage'].' heure</th><th>'.$donnees7['mas'].'Kg</tr>';
echo  '<tr><th>Variation du poids sur les 2 derniers mois :</th><th>'.$result8.'Kg</th><th>'.$donnees8['horodatage'].' heure</th><th>'.$donnees8['mas'].'Kg</tr>';
echo '</table>';


// affichage tableau listing données complète de la ruche
echo '<table>';
echo '<tr><th>Heure</th><th>Hygrométrie</th><th>Température exterieur</th><th>Température interieur</th><th>Masse</th><th>Luminosité</th><th>Pluie (1 si pluie)</th></tr>';


while ($donnees = $reponse->fetch())
{

if ($donnees['lum']<>'-1' AND $donnees['eau']<>'-1'){	// cas ou on a tous les capteurs
						    echo '<tr><th>'.$donnees['horodatage'].'</th><th>'.$donnees['hyg'].'%</th><th>'.$donnees['tex'].'°C</th><th>'.
							$donnees['tin'].'°C</th><th>'.$donnees['mas'].'Kg</th><th>'.$donnees['lum'].'%</th><th>'.$donnees['eau'].'</th></tr>';
						    }


if ($donnees['lum']=='-1' AND $donnees['eau']=='-1'){	// cas ou on a pas de capteur de luminosité ni de pluie
			  			    echo '<tr><th>'.$donnees['horodatage'].'</th><th>'.$donnees['hyg'].'%</th><th>'.$donnees['tex'].'°C</th><th>'.
							$donnees['tin'].'°C</th><th>'.$donnees['mas'].'Kg</th><th>'.'-</th><th>'.'-</th></tr>';	
						    }


if ($donnees['eau']=='-1'AND $donnees['lum']<>'-1' ){		// cas ou on a pas le capteur de pluie mais on a le capteur de luminosité
			  			   echo '<tr><th>'.$donnees['horodatage'].'</th><th>'.$donnees['hyg'].'%</th><th>'.$donnees['tex'].'°C</th><th>'.
							$donnees['tin'].'°C</th><th>'.$donnees['mas'].'Kg</th><th>'.$donnees['lum'].'%</th><th>'.'-</th></tr>';
			  			   }


if ($donnees['lum']=='-1'AND $donnees['eau']<>'-1'){		//cas ou on a pas le capteur de luminosité mais on a le capteur de pluie
			  			   echo '<tr><th>'.$donnees['horodatage'].'</th><th>'.$donnees['hyg'].'%</th><th>'.$donnees['tex'].'°C</th><th>'.
							$donnees['tin'].'°C</th><th>'.$donnees['mas'].'Kg</th><th>'.'-</th><th>'.$donnees['eau'].'</th></tr>';
 
 			  			   }


}


echo '</table>';

$reponse->closeCursor();


?>

Comment en soustrayant 2 chiffres de format possible xxxxx.1 et de format réeel xx.x obtenir un chiffre avec 14 chiffres derrière la virgule...

Une idée?

Merci

PS: j'ai une ligne $reponse->closeCursor();

J'ai fais un peu de recherche est-ce vraiment utile?

Faut-il fermer toutes ces requêtes après utilisation?

Merci

Alors je pense avoir trouvé comment régler le problème, même si je n'en comprends pas l'origine.

J'avais essayé un

$result1 = $donnees0['mas']-$donnees1['mas'];		// calcul sur les dernieres 24h

round($result1,2);

sans succès.. J'ai donc déplacé l'arrondissement directement sur l'affichage :

echo  '<tr><th>Variation du poids sur les 24 dernières heures :</th><th>'.round($result1,2).'Kg</th><th>'.$donnees1['horodatage'].' heure</th><th>'.$donnees1['mas'].'Kg</tr>';

Et là cela semble fonctionner, par contre je ne sais toujours pas comment la soustraction de 2 chiffres avec un seul chiffre derrière la virgule peut donner un résultat avec 14 chiffres derrière la virgule...

-
Edité par GillesMangin-Voirin 6 mai 2022 à 14:28:58

Benzouye

8 mai 2022 à 10:45:25

Le coup des 14 chiffres après la virgule vient du type de données sur les colonnes impliquées.

Seul le type DECIMAL et NUMERIC de MySQL est précis (fixed). Pour les autres, FLOAT notamment, MySQL fait des approximations qui mènent à des résultats paraissant étranges.

Je te laisse creuser la documentation si tu veux plus de détails.

https://dev.mysql.com/doc/refman/8.0/en/numeric-types.html

-
Edité par Benzouye 8 mai 2022 à 10:46:03

Seul on va plus vite, ensemble on va plus loin ... A maîtriser : Conception BDD, MySQL, PHP/MySQL

GillesMangin-Voirin

8 mai 2022 à 14:30:24

Merci