Bonjour, 

J'ai une table de données avec la structure en photo ci-dessous. Il s'agit d'une base de données qui collecte des infos de différents capteurs (Une mesure toutes les 6 secondes). Je souhaiterais faire une moyenne par période pour pouvoir afficher une courbe de tendance. J'ai cherché un peu mais mes connaissances étant plutôt limité, j'ai du mal à trouver comment faire donc me voilà pour solliciter votre aide. Pour être plus clair, je voudrait pouvoir faire une moyenne des valeurs toute les trente minutes de la colonne TEcs et bien sûr avec un trie chronologique.Les requêtes seront limitées à une journées et/ou à cheval sur deux jours.

Merci

-
Edité par DanielLopes12 17 juillet 2022 à 18:41:59

Bonjour,

Quels sont les types des colonnes Date et Heure ? Personnellement je n'aurai créé qu'une seule colonne de type DATETIME ... Peux-tu encore modifier cela ?

Pour créer des moyennes sur un intervalle de 30 minutes, il faut arrondi le "timestamp" à 30 minutes :

FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP( colonne_datetime )/1800)*1800)

Dans cette formule, il faut que colonne_datetime soit de type DATETIME, et 1800 = 30 minutes exprimées en secondes.

La requête pourrait alors être :

SELECT
	FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP( colonne_datetime )/1800)*1800) AS date_arrondie,
	AVG( TEcs ) AS moyenne
FROM nomdelatable
GROUP BY date_arrondie
ORDER BY date_arrondie DESC

Tu pourras faire varier la taille de ton intervalle en changeant le 1800 à ta guise, par exemple un jour = 86 400 secondes.

-
Edité par Benzouye 18 juillet 2022 à 9:59:43

Bonjour Benzouye et merci pour ta réponse. Pas de problème je peux modifier les colonnes pour avoir cela en en une seule. C'est un projet perso donc c'est moi qui fixe les règles ;). Par contre pourrais tu me détailler la fonction UNIXTIME ou un lien et FLOOR. Et sans vouloir être trop abusif, j'aimerais un coup de pouce pour la mise en forme et affichage (J'ai pas encore compris toute les subtilités de MYSQL avec les fetch array et object). Ma derniere approche remonte à 2008 et ça à beaucoup évolué au niveau syntaxe et la PDO, j'avoue que je nage un peu.

Date est de type Tinytext et Heure est de type Time, du coup pour la colonne DATETIME tu me conseille quel type ?

Merci bien

-
Edité par DanielLopes12 19 juillet 2022 à 13:51:03

Commence par ajouter une colonne de type DATETIME dans ta table, en la nommant par exemple dateConso.

Ensuite modifie ton programme (Arduino ?) pour alimenter cette colonne au lieu des deux anciennes, avec un :

INSERT INTO nomdelatable ( dateConso, ... )
VALUES( NOW(), ... );

Attention les points de suspension sont à remplacer par les autres colonnes et valeurs déjà existantes dans ton programme.

Pour initialiser la valeur de la nouvelle colonne pour les enregistrements existants, tu peux exécuter cette requête :

UPDATE nomdelatable
SET dateConso = CONCAT( STR_TO_DATE( `Date`, '%Y%m%d' ), ' ', `Heure` );

Dès lors tu peux exécuter la requête SELECT proposée plus haut :

SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP( dateConso )/1800)*1800) AS date_arrondie,
    AVG( TEcs ) AS moyenne
FROM nomdelatable
GROUP BY date_arrondie
ORDER BY date_arrondie DESC

Pour comprendre la formule, cf. la doc MySQL : MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

En gros, on transforme la dateConso en timestamp (nombre de secondes écoulées depuis le 01/01/1970) avec UNIX_TIMESTAMP. Puis on arrondi (FLOOR) ce nombre de secondes à 1800 pour représenter des créneaux de 30 minutes et on retransforme le tout en DATETIME avec FROM_UNIXTIME.

Merci, effectivement c'est un arduino nano qui fait les mesures, qui sont transmises à un ESP32 qui grâce au WIFI transmet le tout à la BDD.

Concernant toute la partie Date et Heure, mise en forme et update de la BDD ça sera sans problème. Mon problème qui grâce à ton aide va avancer grandement c'est sur cette fameuse requête et sa mise en forme. L'objectif étant, une fois ces valeurs moyennes calculée de les mettre en forme dans un fichier .csv. Ce fichier csv est ensuite utilisé par un code en JS pour afficher mes courbes de tendances et c'est cette partie la que je trouve vraiment ardue. (Sachant que je suis partie de zéro en C et JS et le monde arduino. Ca fait un an que je suis dessus et c'est la dernière ligne droite au niveau des fonctionnnalités, après tout le reste sera de la mise en forme). Je ne peut travailler sur mon projet que le week-end car je fais des déplacements pro, aussi j'essaie de récuperer un max d'info pour finaliser cette partie. Merci  

$bd = new PDO("mysql:host=localhost;dbname=datalogger", "root", "");
 while($rs = $r->fetch(PDO::FETCH_ASSOC)){
       
		$tabledonnees[] = [($rs['Date'].$rs['Heure']), $rs['Consommation'], $rs['ConsoECS'], $rs['ProductionPV']];
        
}
$fichier_csv = fopen("DataMsqli.csv", "w+");
foreach($tabledonnees as $ligne){
fputcsv($fichier_csv, $ligne, ",");}
fclose($fichier_csv);		

 

Pourquoi passer par du CSV ?

PHP pourrais très bien transmettre les données obtenues par la requête directement à JS (AJAX).

he bien simplement parcque je ne maîtrise pas assez. J’ai récupérer un code que je modifie à tâtons par expérimentation et que je commente au fur à mesure. Je trouve js pas intuitif et galère aussi de ce côté la. Alors oui sans doute tu a raisons mais pour l’instant j’arrive déjà à obtenir des résultats en faisant tout de façon autodidacte mais heureusement que les forum existent et qu’on y trouve des gens comme toi pour aider les gens mais je peux pas non plus demander qu’on m’écriv le programme en entier à ma place 😉

Peux-tu expliquer l'organisation matérielle et logicielle actuelle de ton projet ?

Quels équipements en jeu, leur localisation et connexion réseau, quel serveur bdd et php en place, etc.

Comment souhaites-tu accéder au données visuelles ? En local ou Internet ? via le navigateur ?

Bon alors je comprends pas trop le sens des questions mais je vais essayer de répondre au mieux, 

-Organisation matériel, un arduino nano qui enregistre des valeurs températures, humidité et intensité situé dans la buanderie/Garage qui envoi les données avec un NRF24 sur un ESP32 qui collecte ces infos et mesures sur mon tableau électrique des données de consommation en MODBUS et gestion de l'ECS par puissance étagé selon la puissance fournie par panneau Photovoltaïque. A termes toutes ces données seront envoyé sur un NUC (Actuellement c'est mon portable du boulot qui me sert à la mise au point) via WIFI allumé en permanence qui abritera la BDD et servira de serveur. Coté logiciel, WAMP 3.2.6, MySQL  5.7.36, Apache 2.4.51 et PHP 7.4.26. J'accèderait à une partie des infos, les courbes que je souhaite faire en JS avec un navigateur en local, pas de consultation nécessaire par Internet. Dans la suite du projet un autre ESP32 associé à un écran type HMI nextion recevra d'autre info (type température et message alarme, statuts du système) pas de protocole de particulièrement défini mais je pense par WIFI entre les deux esp32 sans passer par le NUC.

Voilà je pense que c'est assez complet

Alors je pense que tu peux voir les choses ainsi :

L'Arduino reçoit les mesures, et les envoie au serveur web en appelant un script PHP qui les insère en base de données MySQL dans la table en question.

Sur ton serveur web, tu crées un fichier index.php qui se connecte à la BDD (via PDO), récupères les données via la requête SQL proposée plus haut et les passe en paramètre à ton script JS de génération de graphique (Chart.js ?).

C'est la solution la plus simple à mes yeux, même si un peu bourrine.

Une autre solution plus "propre" est de fonctionner avec AJAX (JavaScript asynchrone).

Là, tu as sur ton serveur web :

• un fichier insertion.php qui est appelé par l'Arduino et insère les données de mesure en BDD
• un fichier donnees.php qui se connecte à la BDD, récupères les données selon la requête proposée plus haut et les retourne au format JSON (pour utilisation en JS plus tard)
• un fichier index.html qui gère la structure HTML de ta page de consultation, et charge la librairie JS de graphique (Chart.js ?), la librairie jQuery (pour te simplifier la syntaxe de AJAX) et un fichier JS perso pour la gestion de l'affichage des graphiques
• un fichier graphique.js qui contient l'appel AJAX au fichier donnees.php et le traitement du retour JSON pour afficher les graphiques

DanielLopes12 a écrit:

mais je peux pas non plus demander qu’on m’écriv le programme en entier à ma place 😉

Si tu veux, je peux te poster une base de code pour ces 4 fichiers. Ce n'est pas grand chose, et j'ai des exemples existants qui collent à ton besoin.

Juste, je ne connais pas du tout Arduino, donc je ne t'aiderai pas sur ce point, mais cela à déjà l'air de bien fonctionner sur cette partie

-
Edité par Benzouye 21 juillet 2022 à 11:03:13

Benzouye a écrit:

L'Arduino reçoit les mesures, et les envoie au serveur web en appelant un script PHP qui les insère en base de données MySQL dans la table en question. 

OUI cela fonctionne comme ca actuellement.

Sur ton serveur web, tu crées un fichier index.php qui se connecte à la BDD (via PDO), récupères les données via la requête SQL proposée plus haut et les passe en paramètre à ton script JS de génération de graphique (Chart.js ?).

Pour les courbes ,je me suis inspirer de ça : 

D3JS - Création d'une courbe de données élégante (datavis.fr)

C'est la solution la plus simple à mes yeux, même si un peu bourrine.

OUI mais je vais rester sur ce mode là je pense car je craint de passer pas mal de temps avec ton option plus "propre" mais je peux essayer.

Une autre solution plus "propre" est de fonctionner avec AJAX (JavaScript asynchrone).

Là, tu as sur ton serveur web :

• un fichier insertion.php qui est appelé par l'Arduino et insère les données de mesure en BDD
• un fichier donnees.php qui se connecte à la BDD, récupères les données selon la requête proposée plus haut et les retourne au format JSON (pour utilisation en JS plus tard)
• un fichier index.html qui gère la structure HTML de ta page de consultation, et charge la librairie JS de graphique (Chart.js ?), la librairie jQuery (pour te simplifier la syntaxe de AJAX) et un fichier JS perso pour la gestion de l'affichage des graphiques
• un fichier graphique.js qui contient l'appel AJAX au fichier donnees.php et le traitement du retour JSON pour afficher les graphiques

DanielLopes12 a écrit:

mais je peux pas non plus demander qu’on m’écriv le programme en entier à ma place 😉

Si tu veux, je peux te poster une base de code pour ces 4 fichiers. Ce n'est pas grand chose, et j'ai des exemples existants qui collent à ton besoin. Oui je veux bien 

Juste, je ne connais pas du tout Arduino, donc je ne t'aiderai pas sur ce point, mais cela à déjà l'air de bien fonctionner sur cette partie  Oui pas de problème de se côté ça fonctionne.

-
Edité par Benzouye il y a environ 1 heure

Tout d'abord un fichier database.php pour regrouper les informations de connexion à ta base de données :

<?php
	define( 'DBTYPE' , 'mysql' );
	define( 'DBUSER' , 'utilisateur' );
	define( 'DBPASS' , 'password' );
	define( 'DBHOST' , 'localhost' );
	define( 'DBNAME' , 'nombase' );
	define( 'DBCHAR' , 'UTF8' );
	define( 'ERRMOD' , PDO::ERRMODE_EXCEPTION );
	define( 'FETMOD' , PDO::FETCH_OBJ );
	define( 'DBLOCALE' , 'fr_FR' );
	
	try {
		$pdoOptions[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET lc_time_names = "'.DBLOCALE.'"';
		$pdoOptions[PDO::ATTR_ERRMODE] = ERRMOD;
		$pdoOptions[PDO::ATTR_DEFAULT_FETCH_MODE] = FETMOD;
		$bdd = new PDO(DBTYPE.':host='.DBHOST.';dbname='.DBNAME.';charset='.DBCHAR, DBUSER, DBPASS, $pdoOptions);
	}
	catch(Exception $e) {
		echo $e->getMessage();
		exit();
	}

Ensuite un fichier insertion.php qui sera appelé par l'Arduino pour l'insertion des mesures en base avec des valeurs passées en GET :

<?php
	if( isset( $_GET['param_1'], $_GET['param_2'], ..., $_GET['param_n'] ) ) {
		require_once( 'database.php' );
		
		try {
			$requete = $bdd->prepare( '
				INSERT INTO nomdelatable ( dateConso, colonne_1, colonne_2, ..., colonne_n )
				VALUES( NOW(), ?, ?, ..., ? );'
			);
			$requete->execute( [
				$_GET['param_1'],
				$_GET['param_2'],
				...,
				$_GET['param_n']
			]);
		}
		catch(Exception $e) {
			echo $e->getMessage();
		}
	}

Dans ce fichier il faudra bien sûr adapter le nom de la table et des colonnes en fonction de la réalité.

Ensuite un fichier donnees.php qui retourne un objet JSON avec les données de la base :

<?php
	require_once( 'database.php' );
	
	try {
		$requete = $bdd->query( '
			SELECT
			    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP( dateConso )/1800)*1800) AS date_arrondie,
			    AVG( TEcs ) AS moyenne
			FROM nomdelatable
			GROUP BY date_arrondie
			ORDER BY date_arrondie DESC;'
		);
		$donnees = $requete->fetchAll();
		
		echo json_encode( $donnees );
	}
	catch(Exception $e) {
		echo $e->getMessage();
	}

Ensuite un fichier graphique.js qui va chercher les données JSON et les affiche :

$( document ).ready( function(){
	$.ajax({
		method: "GET",
		url: "donnees.php",
		dataType: 'json',
		success: function( retour ) {
			// Ici le traitement des données retournées
			console.log( retour );
		},
		error: function( retour ) {
			// Ici le traitement d'une éventuelle erreur
			console.log( retour );
		}
	});
});

Dans ce fichier il te faudra travailler la partie "success" pour faire ce que tu veux avec les données (créer le graphique).

Encore un fichier style.css pour la mise en forme de ta page web, là je te laisse voir ce que tu veux faire ...

Enfin un fichier index.html qui concentre tout cela :

<!DOCTYPE html>
<html lang="fr">
	<head>
		<meta charset="utf-8">
		<meta name="viewport" content="width=device-width, initial-scale=1.0">
		<meta http-equiv="X-UA-Compatible" content="IE=edge">
		<title>Graphique</title>
		<link rel="stylesheet" href="style.css">
	</head>
	<body>
		<div id="graphique"></div>
		
		<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.slim.min.js"></script>
		<script type="text/javascript" src="https://d3js.org/d3.v7.min.js"></script>
		<script type="text/javascript" src="graphique.js"></script>
	</body>
</html>

Le body ne contient qu'une div qui sera utilisée par le code JS comme conteneur de ton graphique (cf. la documentation de D3JS).

L'Arduino reçoit les mesures (les envoie a l'esp32 qui lui ...), les envoie au serveur web en appelant un script PHP qui les insère en base de données MySQL dans la table en question.

Oui cela fonctionne comme ça actuellement

Pour le script JS j'ai pris ça comme exemple : D3JS - Création d'une courbe de données élégante (datavis.fr) et mixer avec d'autre exemples 

Je vais rester sur la version bourrin pour le moment car j'ai peur que l'option plus propre ne me pose plus de problème mais je veux bien essayer.

Donc je veux bien tes fichiers en exemple. 

Merci

Mon post précédent devrait t'aider.

En fait les réponses se sont entrecroisées et j'avais galérer avec le forum et je savais pas si mes réponses étaient pris en compte et j'avais un message comme quoi je devais attendre 24h avant de pouvoir poster.....;)

J'arrive à comprendre les fichiers à peu près les jeux d'instruction et comment il s'articule. Là ou je comprend pas c'est ce fameux success... je ne sais pas comment seront les données , si je peux les lire avec un éditeur et les insérées dans mon modèle existant... si tu peux juste détaillé un peu cet aspect là. Le reste me semble toute a fait clair même si je découvre le PDO.

Ha oui comment devrait être la syntaxe pour que l'intervalle de 1800 soit une variable pour que je puisse la définir directement sur le navigateur (Post ou Get).

Dans tout les cas je pense que je vais pouvoir avancer assez rapidement sur cette partie Merci pour ton aide

-
Edité par DanielLopes12 22 juillet 2022 à 19:06:45

DanielLopes12 a écrit:

Là ou je comprend pas c'est ce fameux success... je ne sais pas comment seront les données

La méthode success va être exécutée lorsque le fichier donnees.php retournera les données de la requête SQL sans erreur. Ces données seront au format JSON. Le format JSON est facilement exploitable en JavaScript, et une recherche rapide sur le net te montrera des exemples de ce format et comment l'utiliser en JS.

DanielLopes12 a écrit:

comment devrait être la syntaxe pour que l'intervalle de 1800 soit une variable

Il "suffit" de modifier le fichier donnees.php pour qu'il traite une variable GET :

<?php
	require_once( 'database.php' );
	
	$intervalle = !empty( $_GET['intervalle'] ) ? intval( $_GET['intervalle'] ) : 1800;
	 
	try {
		$requete = $bdd->query( '
			SELECT
				FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP( dateConso )/'.$intervalle.')*'.$intervalle.') AS date_arrondie,
				AVG( TEcs ) AS moyenne
			FROM nomdelatable
			GROUP BY date_arrondie
			ORDER BY date_arrondie DESC;'
		);
		$donnees = $requete->fetchAll();
		 
		echo json_encode( $donnees );
	}
	catch(Exception $e) {
		echo $e->getMessage();
	}

Ici la ligne 4 permet d'utiliser l'url donnees.php?intervalle=X pour faire varier (avec X) l'intervalle souhaité, avec par défaut la valeur 1800 si aucune valeur n'est donnée via GET.

Du coup, on peut modifier le fichier graphique.js pour lui permettre de passer une variable au fichier PHP :

$( document ).ready( function(){
	$.ajax({
		method: "GET",
		url: "donnees.php",
		data: {
			intervalle: 1800
		},
		dataType: 'json',
		success: function( retour ) {
			// Ici le traitement des données retournées
			console.log( retour );
		},
		error: function( retour ) {
			// Ici le traitement d'une éventuelle erreur
			console.log( retour );
		}
	});
});

Ici ligne 6 tu pourras mettre une valeur variable en utilisant un élément du DOM (une balise select avec plusieurs choix par exemple).

Ensuite, je te déconseille d'utiliser D3JS pour commencer car cette librairie est un peu difficile à prendre en main pour un débutant. Je te propose plutôt d'utiliser Chart.js ( en remplaçant simplement dans le HTML le lien D3JS par https://cdn.jsdelivr.net/npm/chart.js ).

Du coup ton fichier JS pourra ressembler à ceci :

$( document ).ready( function(){
	$.ajax({
		method: "GET",
		url: "donnees.php",
		data: {
			intervalle: 1800
		},
		dataType: "json",
		success: function( retour ) {
			let canvas = $("#graphique");
			let graphOptions = {
				// Ici les paramètres d'affichage de Chart.js (cf. doc)
				...
			};
			let graphique = new Chart( canvas, graphOptions );
		},
		error: function( retour ) {
			// Ici le traitement d'une éventuelle erreur
			console.log( retour );
		}
	});
});

Ici ligne 13 il faudra compléter pour paramétrer le graphique selon ton besoin (type, couleur, échelle, etc.) et passer les valeurs issues de la variable retour.

-
Edité par Benzouye 22 juillet 2022 à 20:02:10

Ok un grand merci, je vais voir si je trouve le temps de tester tout cela ce week-end.

Concernant les librairies si j'utilise Chart.js à la place de D3JS, il va falloir que je réécrive mon code actuel, car certaine fonction et/ou syntaxe ne seront pas identique et/ou disponible.

Je pense que ce sera la dernière question du coup je passerai le sujet en résolu après cela.

Bon finalement j'ai pu essayer un peu mais j'aimerais savoir comment limiter le résulat obtenu à un chiffre après la virgule 

{"date_arrondie":"2022-07-23 17:00:00","moyenne":"63.129202"},{"date_arrondie":"2022-07-23 17:30:00","moyenne":"63.057350"},{"date_arrondie":"2022-07-23 18:00:00","moyenne":"62.960000"}]

Je comprends pas trop pourquoi j'obtiens 6 chiffres après la virgule alors que ma colonne est limitée à deux?....

-
Edité par DanielLopes12 23 juillet 2022 à 18:20:27

FLOAT est un type décimal à approximation, pour obtenir un nombre à deux décimales, il faut faire un arrondi (fonction ROUND) ou utiliser un type décimal fixe comme DECIMAL, voir la doc. MySQL.

OK merci pour ton aide Je passe ce sujet en résolu

Salut je repasse par là car j'ai tester ton code avec json, le premier problème est que la version de jquery-3.6.0.slim.min.js ne contient pas la fonction $.ajax. Donc il faut modifier la ligne en

<script type="text/javascript" src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

Dans le fichier index.html

Bon maintenant il faut que je trouve comment exploiter les données reçu 

Merci 

Si par hasard vous voulez m'aider pour le reste de ce projet c'est içi : 

JSON encode - Transmise depuis un script PHP vers un script.js par DanielLopes12 - page 1 - OpenClassrooms