Mi Band Master の出力データはSQLite形式DB。Mi Band 2心拍データCSV取得方法
Mi Band Master の出力データはSQLite形式DBになっている
シャオミ(小米科技)の活動量計 Mi Band 2 ( Xiaomi Mi Band 2 fitness tracker )はなかなか良いです。
この Mi Band 2 の測定データは本体に最大7日分保存される他、クラウドに保管されます。
スマホを通して過去データも見ることができますが、痒いところに手が届かないようにも感じることがあります。
例えば、1/15の6:15amに心拍が220に跳ね上がったとして、数ヶ月後には1月の平均心拍が70最高心拍が220、最低心拍が45などと集約されてしまうアプリでは、この時に何があったか追求するのは難しく、生データを見たいと感じます。
公式アプリ Mi Fit ではデータ出力できるのかどうかわかりませんが、 Mi Band Master というアプリでは db.sqlite というデータが出力できます。そのデータは SQLite 形式であり、睡眠データも歩数データも、心拍データもすべてまとめて一つのファイルになっています。
そのため SQLite を持ち出す必要が出てきました。
SQLite 形式のデータ
SQLite では一つのファイルが一つのデータベースですが、その中に複数のテーブルを含むことができます。
エクセルファイル一つに複数シートがあるような感じです。Mi Band 2 には心拍、歩数など、多数のデータがあるのでそれを一つにまとめるのに都合が良いのだと思います。
しかし、SQLite は そのままでは Excelで簡単に開くようなことはできません。
なるべく簡単にデータを覗くことを考えます。
SQLite を簡単に使いたい
私は初め vb.net や R などで SQLite のデータベースにアクセスすることを考えました。が、vb.net では多少コードを書く必要がありそうです。R では私には不要なライブラリも含めて依存パッケージがたくさん(ファイルサイズは小さいですが)インストールされてしまうのが気持ち悪いのでやめておきます。
コマンドラインシェル SQLite3.exe を入手
http://www.sqlite.org/ の Download から
コマンドラインシェル(command-line shell program と書いてあるはず)を含むバンドルをダウンロード
2018.2.1時点 sqlite-tools-win32-x86-3220000.zip です。
このzipを任意の場所に解凍(展開)すると、sqlite3.exe、sqldiff.exe、sqlite3_analyzer.exeの三つのファイルが出てきます。
とりあえず必要なものは sqlite3.exe ただ一つです。
これはインストール不要、非常駐なので必要な時に起動すれば良いものです。
sqlite3 をクリックすると
sqlite3 .exe実行の様子
SQLite version 3.22.0 2018-01-22 18:45:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>
と、コマンドラインが表示されます。このコマンドラインでポチポチ入力しても、外部ソースを実行してもかまいません。
心拍データ取得法(仮)
心拍データの取得方法の例
sqlite>.open c:/db.sqlite sqlite>.mode csv sqlite>.headers on sqlite>select * from heart_rate; time,hr 1517950860,52 1517950980,51 1517951100,70 1517951220,76 1517951340,58 sqlite>.output hr.csv sqlite>select * from heart_rate; sqlite>.quit
timeが1517950860などとよくわからないのですが、とりあえず簡単にデータが表示でき、かつCSVにも出力できます。
外部SQLを読み込んでバッチ処理する方法
コマンドラインでポチポチ入力しても良いですが、反復して作業するなら、SQLソースやsqliteコマンドをバッチで実行する方が良いでしょう。ついでに日付時刻を正しく表示するようにします。
正しい心拍データ取得法
1.SQLを用意する。
下記コードを入力して MiBand.sql としてsqlite3.exe と同じフォルダに保存します。
.open db.sqlite .headers on .mode csv .output Mi_HeartRate.csv select datetime(time,'unixepoch','localtime') as Time,hr from heartrate; .quit
Mi Band Master のデータベースの日付時刻( 1517950860 等 )は unixepoch(UNIXのエポック秒) UTC で表されているようです。つまり1970年1月1日から始まる秒でかつUTCなので、これを datetime により日付変換し時差修正(localtimeに)しています。
2.バッチを用意する。
sql実行用のバッチ
cd /d %~dp0 sqlite3.exe < "MiBand.sql" pause
このコードをgo.batなどとして、go.bat,sqlite3.exe ,MiBand.sql,db.sqlite全て同じフォルダにおき、バッチを実行すればあらかじめMiBand.sqlに用意されたコードが実行されます。
3.出力結果
Time,hr : 2018/2/7 4:27 ,51 2018/2/7 4:29 ,54 2018/2/7 4:31 ,75 2018/2/7 4:33 ,49 2018/2/7 4:35 ,50 2018/2/7 4:37 ,49 2018/2/7 4:39 ,49 : 2018/2/7 7:19 ,74 2018/2/7 8:19 ,55 2018/2/7 9:02 ,61 2018/2/7 9:19 ,59 2018/2/7 10:19 ,102 2018/2/7 11:19 ,69 2018/2/7 12:19 ,60 2018/2/7 13:19 ,68 2018/2/7 14:19 ,60 :
私の設定通り、日中は1時間おき、睡眠中は2分おきに日付、時刻と脈拍が記録されていることがわかります。9:02は意図せず触れてしまったことで測定されたようですが、関係なく毎時19分に計測されています。19分というのは固定ではなく、その日の起床時刻と関係するようですので毎日異なっています。
根幹となる SQL select datetime(time,'unixepoch','localtime') as Time,hr from heartrate;を書き換えることで心拍以外も抽出できます。
データが多いなら期間設定
睡眠時に2分間隔、覚醒時60分間隔の心拍測定とすると、1日に200～300件の心拍データが発生しますから 日付 を指定しないととてつもなく大量のデータが抽出されます。
日付期間設定しよう
sqlite>select id,datetime(start_time,'unixepoch','localtime') as StartTime,datetime(end_time,'unixepoch','localtime') as EndTime,awake,deep,light,stages,deleted,manually from sleep where StartTime between '2018-01-26' and '2018-01-30'; id,StartTime,EndTime,awake,deep,light,stages,deleted,manually 212,"2018-01-27 00:22:00","2018-01-27 08:38:00",0,268,228,13L34D16L38D5L31D33L13D11L16D44L43D10L12D6L40D20L11D25L30D45L,0, 213,"2018-01-28 00:10:00","2018-01-28 07:59:00",0,242,227,12L42D6L11D7L27D2L12D36L16D19L26D19L35D53L12D9L16D8L11D17L25D39L9D,0, 214,"2018-01-28 23:35:00","2018-01-29 04:57:00",0,180,142,28L29D36L16D6L24D2L13D5L38D16L22D34L18D11L20D4L,0, 215,"2018-01-29 23:33:00","2018-01-30 06:10:00",0,208,189,30L25D32L19D15L29D25L30D19L10D10L10D4L50D3L11D17L14D29L10D5L,0,
のように between 'yyyy-mm-dd' and 'yyyy-mm-dd' 形式で日付期間指定できます。
