スマートメーターのデータなどをcsvで保存してjsonに変換してchart.jsでグラフにしているが、snmpなどのデータも取り始めて、sqlite3に変更してみた。
ずっとcsvでも良いかと思っていたのですが、sqlite3を調べてみたところ、ライブラリを22万行のファイル1本で書かれていて、依存もなくちょっとやってみようという気がおきました。
ZRouterのports-modokiに入れてピルドできるようにしました。
mrbgemsはこれを使いました。
csvで処理していた時には、cronから起動させるバッチで定期的にjsonを作っていましたが、sqlite3を使えばcgiで動的に作ることが可能になります。
とりあえずこんな感じでデータを保存しあります。1カラム目が日付で2カラム目が時間です。これらはdefault CURRENT_DATE,CURRENT_TIMEで設定してあります。
sqlite> select * from sm;
2020-04-05|23:50:47|6279.7
2020-04-06|00:00:01|6279.7
2020-04-06|00:10:01|6279.8
2020-04-06|00:20:01|6279.8
2020-04-06|00:30:02|6279.8
2020-04-06|00:40:02|6279.9
2020-04-06|00:50:01|6279.9
2020-04-06|01:00:01|6279.9
2020-04-06|01:10:02|6280.0
2020-04-06|01:20:01|6280.0
2020-04-06|01:30:02|6280.0
2020-04-06|01:40:01|6280.1
2020-04-06|01:50:01|6280.1
2020-04-06|02:00:02|6280.1
cvsの時はJSTで保存していたのですが、sqlite3はデフォルトUTCになるようです。
JSTとUTCの関係はこんな感じです。
JSTの今日はUTCの昨日の15時から今日の15時になります。
結構無理やりですが、今日のメーター値の抜き出しを、こんなコードにしてみました。
# !/usr/local/bin/mruby
db = SQLite3::Database.new('/tmp/ouchi.db')
time = Array.new
val = Array.new
yday = (Time.now - 60*60*24).to_s[0, 10]
tday = Time.now.to_s[0, 10]
i = 0
# select by JST
sel = 'select time, power from sm'
whe = ' where (time>="15:00:00" and date=?) or (time<"15:10:00" and date=?)'
db.execute(sel + whe, yday, tday) do |row, fields|
time[i] = row[0]
val[i] = row[1]
i = i + 1
end
print "Content-type: text/json\n\n"
j = 0
tmo = 0
print '{"date":"' + tday + '","type":"sm.log","data":['
while j < i do
if j != 0
print ","
end
print "["
# convert to JST from UTC
h = time[j][0, 2].to_i
if tmo == 0 && h >= 15
h = h - 15
hs = "0" + h.to_s
elsif h == 0
hs = "09"
tmo = 1
else
h = h + 9
hs = h.to_s
end
print "\"" + hs + time[j][2, 6] + "\"," + val[j].round(1).to_s
print "]"
j = j + 1
end
print ']}'
一日の集計は丸一日分のデータと次の日の一つ目のデータが必要なので24時台のデータを入れるようにしてあります。
jsonはバッチで作っていた時と同じフォーマットにして、こんな感じで吐かれます。
{"date":"2020-04-06","type":"sm.log","data":[["08:50:47",6279.7],["09:00:01",6279.7],["09:10:01",6279.8],["09:20:01",6279.8],["09:30:02",6279.8],["09:40:02",6279.9],["09:50:01",6279.9],["10:00:01",6279.9],["10:10:02",6280],["10:20:01",6280],["10:30:02",6280],["10:40:01",6280.1],["10:50:01",6280.1],["11:00:02",6280.1],["11:10:01",6280.2],["11:20:02",6280.2],["11:30:01",6280.2],["11:40:02",6280.3],["11:50:01",6280.3],["12:00:02",6280.3],["12:10:01",6280.4],["12:20:01",6280.4],["12:30:01",6280.4],["12:40:01",6280.4],["12:50:01",6280.5],["13:00:01",6280.5],["13:10:01",6280.5],["13:20:02",6280.6],["13:30:02",6280.6],["13:40:01",6280.6],["13:50:01",6280.7]]}
chart.jsでこんなグラフにしてます。
JSTのcsvファイルをUTCのcsvファイルに変換するプログラムを作って見ました。
def jst2utc d, t
y = d[0, 4].to_i
m = d[6, 2].to_i
d = d[8, 2].to_i
h = t[0, 2].to_i
mi = t[3, 2].to_i
s = t[6, 2].to_i
u = Time.local(y, m, d, h, mi, s).utc
[u.to_s[0, 10], u.to_s[11, 8]]
end
fd = File.open("sm.log")
while line = fd.gets do
arr = line.chop.split(",")
d, t = jst2utc arr[0], arr[1]
print d + "," + t + "," + arr[2] + "\n"
end
データファイルサイズはcsvが240530バイトに対してsqlite3が323584になっていました。
日ごと使用量を調べるために日が変わった最初のデータを拾って引き算しています。
select date, power from sm where time>"15:00:00" and time<"15:00:10" order by date
これはあまりよくなくて、15:00:00のデータ取得に失敗していた場合には、数字がおかしくなります。
select date, power from sm as A where A.time in (select B.time from sm as B where B.date=A.date and time > "15:00:00" limit 1)
すると日が変わって最初のデータを拾えるようなので、問題起きないのですが、結構重いです。
以下のようにするとJSTに変換されるようです。
select datetime(date, time, 'localtime'), power from sm;
rubyもsqlも素人なので、もっと良い方法があると思うので、ご指摘ください。