Help us understand the problem. What is going on with this article?

mrubyでsqlite3

スマートメーターのデータなどをcsvで保存してjsonに変換してchart.jsでグラフにしているが、snmpなどのデータも取り始めて、sqlite3に変更してみた。

ずっとcsvでも良いかと思っていたのですが、sqlite3を調べてみたところ、ライブラリを22万行のファイル1本で書かれていて、依存もなくちょっとやってみようという気がおきました。

ZRouterのports-modokiに入れてピルドできるようにしました。

mrbgemsはこれを使いました。

https://github.com/mattn/mruby-sqlite3

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の関係はこんな感じです。

SQL-UTC.png

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でこんなグラフにしてます。

filename-31.png

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も素人なので、もっと良い方法があると思うので、ご指摘ください。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away