前回、集計対象のデバイスをmicro:bit V2からATOMLiteに変更しようとしてまさかの事態が発覚。ATOMLiteでデータベースに記録していたタイムスタンプのフォーマットがSQLite3の時間文字列フォーマットと異なってました。これでは期間を指定してデータを取り出したりできません。今回はフォーマットのFIX。
※「ブロックを積みながら」投稿順 index はこちら
※動作確認にはRaspberry Pi 3 model B+のRaspberry Pi OS(32bit)上にインストールした以下を使用しています。
-
- Node-RED v2.0.5
- node-red-dashboard 3.2.0
SQLite3の日時のフォーマット
SQL素人の老人の認識は以下です。
-
- SQLite3には専用の日時型フィールドはない
- 代わりに特定フォーマットの日時文字列をフィールドに格納しておく
- 日時処理用の関数は該当フォーマットの文字列を日時として認識でき、大小比較や一部取り出しなどが可能
ということで日付時刻を文字列として格納しておけば実質日時型があるかのように使えます。そのフォーマットの一例が以下です。
2022-01-20 16:20:08
とりあえずタイムゾーンとかメンドイことを踏みつぶしてしまえば、年月日を-でつないで、時刻との間はスペース、時分秒は:で区切りで処理は可能っと。
前回発覚の問題
前回発覚したのは、ATOMLiteから上がってきた測定データを日時指定できん、という問題でした。調べてみると格納されているタイムスタンプ用文字列フォーマットが違っているために、日時処理用の関数が日時と認識できていないようでした。その問題のあるフォーマット例が以下に。
2022/1/20 16:20:08
上の例と比べてみると、- であるべき年月日の区切りが / であること、１桁の月数字の前に0が入っているべきなのに入っていないこと、の２点が問題であるようです。コマケー話ですが、使用しているSQLite3が受け付けてくれないので致し方ありません。
そこでSQL素人は以下のSQL文を走らせることにいたしました。一撃でやると失敗しそうだったので２手にわけました。コンサバな。
update kvdata set timestamp=substr(timestamp, 1, 5)||'0'||substr(timestamp, 6) where instr(timestamp, ' ')=10 update kvdata set timestamp=replace(timestamp, '/', '-') where instr(timestamp, '/')>1
しかし、コマンドラインから上記を走らせてしまうのでは本シリーズ記事として成立しませんわな。
今回実験のNodeRedフロー
今回実験のフローは以下です。
Injectノードから上記のSQL文をmsg.topicに載せて送り出し、SQLノードで処理するだけのものです。一応、処理したということを末尾のDebugノードで確認。
走らせるべきSQL文が２つあり順序があるので、STEP1とSTEP2と分けました。STEP1の方はこんな感じ。文字列のところに書き込んであるのは上記SQL文の１行目です。
同様に２行目の方をstep2に書き込んであります。
InjectノードからのSQL文を受けて実際にSQLite3を動かす sqliteノードの設定が以下に。データベースの在処を指定してます。
実験結果
デバッグウインドウを見ているとupdate文２つが走ったことがわかります。
update後、前回データ抽出できなかった ATOMLite のデータを取り出してみました。昨年1月分の温度データです。こんな感じ。
レコードの抽出ができるようになりました。FIXしたのだから当たり前か。だいたい記録時にフォーマットとか間違えるなよ、自分。