LoginSignup
11

More than 5 years have passed since last update.

SQLite3で時刻の差分を求める

Last updated at Posted at 2014-12-08

やりたかったこと

実験用のWebアプリを作る際に,アプリへのアクセスログから,あるアクションから他のアクションに至るまでの滞留時間などをロギングする必要がありました.
そこで,SQLを用いて,あるレコードに記された時刻と,他のレコードに記された時刻の差分を取りたかったのですが,あまりまとまった記事がないようなのでまとめておきます.

SQLiteはRuby on Rails 4.0.0にバンドルされているもので,バージョンは3.8.2.

SQLiteにおける時刻の差分の計算

SQLiteにはdatetime型が存在せず,時刻の情報は,フォーマットに従った文字列に格納されます.
これを,strftime関数を用いてUNIX時間に変換し,1970年1月1日からの経過秒数を取得します.
この差を求めることで,秒単位の差分を求めることができます.

例えば,テーブルaがid,created_at(レコードが作られた時刻),updated_at(レコードが最後に更新された時刻)というカラムを持っているとすると,

diff.sql
select a.id, strftime('%s', a.updated_at) - strftime('%s', a.created_at)
from a
where a.id = 100;

とすることで,idが100のレコードについてのupdated_atとcreated_atの差分,すなわち,レコードが作られてから最後に更新されるまでの時間(秒)を求めることができます.

レコード間の時刻の差分

本題です.

例えば,'accesses'という名前の,

id type created_at updated_at
100 search 2014-12-08 14:01:30.634287 2014-12-08 14:01:30.634287
158 result 2014-12-08 16:23:01.541760 2014-12-08 16:26:41.654827
160 result 2014-12-08 16:23:01.541760 2014-12-08 16:26:41.047661

のようなテーブルがあって,idが100(typeが'search')のレコードのupdated_atと,他のレコードのupdated_atの差分の集合を取得したい時は,

diff_records.sql
select a.id, b.id, strftime('%s', b.updated_at) - strftime('%s', a.updated_at)
from accesses as a, accesses as b
where a.id = 100 and b.id <> 100;

のように,自己結合を使うとよいかと思います.

これで問題なくログが取れそうです!

参考

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
11