SQL
sqlite

SQLite3で時刻の差分を求める

More than 3 years have passed since last update.


やりたかったこと

実験用の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;

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

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


参考

http://www.dbonline.jp/sqlite/function/index8.html