20 | 11月 | 2018

SQLite記録データ集計

（１）概要
ESPr Developerで測定した温度データをレンタルサーバー上のSQLiteファイルに書き込んでいますが、このデータを見やすいように加工して表示してみます。先回投稿時には下の図の通りすべてのデータを単に並べています。（本記事作成時には最新２０件表示に一部改良）
ただし、実際にはそんなに過去の温度を知りたくはありませんし、上の図の様に約５分単位の温度記録も必要ありません。そこで、今回は当日含め過去３日分のデータを１時間単位で表示する様にします。今回作成したブラウザへの出力は次の通りです。
温度データを測定し始めたのが、２日前の１３:４１～（本記事作成時）でしたので、それ以前の時間はデータが空白状態です。同様にインターネット接続し、ブラウザーを開いた時刻以降の当日のデータもありませんので空白になります。
今回の表示は２日前の午前０時から約１時間毎のデータを検索しブラウザー表示します。ESPr Developerは約５分周期で温度測定を行い、データをサーバー登録します。測定時刻は大きくズレはないと思いますが、通信遅れやサーバー側登録時刻であることなど、厳密には測定時刻ではありませんので予めご了承下さい。
以上の様にESPr Developerは定時刻に測定を行っていませんので、検索方法は、２日前の午前０時から約１時間単位でデータ検索し、複数あるデータの中の最も早い時間の温度データをその時間帯の代表データとして抽出します。

※ESPr Developerを他目的でも使用する為、プログラムを2018年11/19～11/21の３日間固定検索する様に変更しました。

尚、SQLite処理については、下記も参考にしてみて下さい。
[https://kats-eye.net/info/2018/11/13/php-sqlite/]

（２）プログラム
プログラムを以下に示します。行番２～４は対象日を変数に設定します。行番11～41のforループでは、対象日をループごとに変更します。行番14～40のforループでは、対象時間帯をループごとに変更します。このループによって日付と時間帯を確定します。行番18，19では、それぞれ開始と終了の日付・時間を設定します。
行番22～25でSQLiteのデータ検索を行うSQL文を作成します。行番24で検索対象範囲の時間帯を設定しています。行番25は登録の早い順に並びかえる様に検索順序を指定しています。
行番26でSQLを実行し、行番28～38で実行結果を表示します。各時間帯は約１時間ですので、５分に１回データ登録していることから、データは１２件程度存在するはずですが、行番37で検索結果表示を中断し、次の時間帯の検索に移ります。結果、各時間帯の最も早い時間の１件のみがブラウザー上に出力されます。

<?php
	$tmp_dt[0] = date('Y-m-d', strtotime('-2 day')) ;
	$tmp_dt[1] = date('Y-m-d', strtotime('-1 day')) ;
	$tmp_dt[2] = date('Y-m-d') ;
	echo "<br><br>";
	

	$db = null;$sql = null;
	$db = new SQLite3("./sq/temp_record.sqlite3");
	
	for($i = 0 ; $i < 3; $i++){
	    $dsp_cnt = 0;

	    for($j = 0 ; $j < 24; $j++){
		$dsp_cnt = $dsp_cnt + 1 ;
		echo $dsp_cnt." , " ;

		$start_tm = $tmp_dt[$i]." ".mb_substr("00".$j,-2).":00:00" ;
		$end_time = $tmp_dt[$i]." ".mb_substr("00".$j,-2).":59:59" ;

		$res = null;
		$sql = "SELECT created_datetime , g_temp ";
		$sql = $sql."FROM temp_reco ";
		$sql = $sql."WHERE created_datetime >= '".$start_tm."' AND created_datetime <= '".$end_time."' ";
		$sql = $sql."ORDER BY created_datetime";
		$res = $db->query($sql);

		while($row = $res->fetchArray(SQLITE3_NUM)) {

	    		for($k = 0 ; $k < count($row); $k++){
				echo $row [$k];
    			
				if($k < count($row) - 1){
		    			echo " , " ;
				}
			}
			break ; 
	    	}
	    	echo "<br>";
	    }
	}

?>

PHPによる表の作成（テーブル）

PHPを使って、ESPr Developerで測定したデータを表にして出力します。最初は判りづらかったのですが、ようやくPHPプログラムから、HTMLを出力するのに慣れてきたところですので、ここで復習もかねてやってみます。
ブラウザへの出力は次の通りです。各データを枠線で囲んで少しデータが見やすくなったと思います。表の中のすき間等も設定できるのですが、私のスキルではそう簡単ではなく、時間がかかりますので割愛させて頂きます。
詳細の説明は省略させて頂きますが、プログラムは次の通りです。PHPの echo 文でHTMLのTABLE要素等を出力しています。

<?php
	$tmp_dt[0] = date('Y-m-d', strtotime('-2 day')) ;
	$tmp_dt[1] = date('Y-m-d', strtotime('-1 day')) ;
	$tmp_dt[2] = date('Y-m-d') ;
	echo "<br><br>";
	

	$db = null;$sql = null;
	$db = new SQLite3("./sq/temp_record.sqlite3");
	

	echo "<TABLE border='1' style='border-collapse: collapse;' >";
	for($i = 0 ; $i < 3; $i++){
	    $dsp_cnt = 0;

	    for($j = 0 ; $j < 24; $j++){
		$dsp_cnt = $dsp_cnt + 1 ;

		$start_tm = $tmp_dt[$i]." ".mb_substr("00".$j,-2).":00:00" ;
		$end_time = $tmp_dt[$i]." ".mb_substr("00".$j,-2).":59:59" ;

		$res = null;
		$sql = "SELECT created_datetime , g_temp ";
		$sql = $sql."FROM temp_reco ";
		$sql = $sql."WHERE created_datetime >= '".$start_tm."' AND created_datetime < '".$end_time."' ";
		$sql = $sql."ORDER BY created_datetime";
		$res = $db->query($sql);

		while($row = $res->fetchArray(SQLITE3_NUM)) {
			
		    echo "<TR><TD>".$dsp_cnt."</TD>" ;

	    	    for($k = 0 ; $k < count($row); $k++){
			echo "<TD>".$row [$k]."</TD>";
		    }
		    break ; 
	    	}
	    	echo "</TR>";
	    }
	}
	echo "</TABLE>";

?>

まとめ

PHP言語でSQLiteに記録されたデータを処理するプログラムを作成したきました。これまで作成したのは、下記の通りです。

次回以降は１時間単位に集計した温度データをグラフにする検討をして行きたいと思っています。幾つか調べてみたのですが、Chart.jsというJavaScriptのチャートライブラリーがフリーで使えて、比較的扱いやすそうです。JavaScriptはブラウザー側で動くプログラムですので、サーバーからデータを取得する為にHTTP通信を使う必要がある様です。次回、HTTP通信の基本的な使い方を確認してみます。