はじめに
SQLiteにはデータ型としての日時型はありません。
そこで、ここでは以下に示すテーブルt1のカラムtのように、yyyy-mm-dd hh:mm:ss書式の文字列で日時を挿入した場合に、ソートや比較、経過時間の計算をどのようにしたらよいか、考えてみました。
CREATE TABLE t1(t, name);
INSERT INTO t1 VALUES ('2017-12-09 13:13:13', 't2');
INSERT INTO t1 VALUES ('2017-12-09 14:13:10', 't3');
INSERT INTO t1 VALUES ('2017-12-08 13:10:48', 't1');
SQLiteのバージョン
こちらの記事に示した方法によりMacOS 10.11.6上でビルドしたバージョン3.21.0で動作確認しました。
$ ./sqlite3 -column -header
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
日時カラムのソート
カラムtに挿入された日時データの書式がyyyy-mm-dd hh:mm:ssで統一されている場合、文字列で辞書式にソートすることで、日時の前後関係も正しくソートされます。すなわち、単純にORDER BY t {DESC|ASC}でソートができます。
-- ソートなし
sqlite> SELECT * FROM t1;
t name
------------------- ----------
2017-12-09 13:13:13 t2
2017-12-09 14:13:10 t3
2017-12-08 13:10:48 t1
-- 昇順
sqlite> SELECT * FROM t1 ORDER BY t ASC;
t name
------------------- ----------
2017-12-08 13:10:48 t1
2017-12-09 13:13:13 t2
2017-12-09 14:13:10 t3
-- 降順
sqlite> SELECT * FROM t1 ORDER BY t DESC;
t name
------------------- ----------
2017-12-09 14:13:10 t3
2017-12-09 13:13:13 t2
2017-12-08 13:10:48 t1
経過時間を求める
カラムtに格納された日時がそれぞれ、2017年12月8日の正午からどのくらいの時間経過しているか求めたいとします。
この場合、文字列のままでは足し引きがてきないので、strftime()関数を利用してUNIXタイムスタンプに変換します。
sqlite> SELECT strftime('%s', '1970-01-01 00:01:30');
strftime('%s', '1970-01-01 00:01:30')
-------------------------------------
90
strftime()の第一引数に'%s'を指定し、第二引数に'yyyy-mm-dd hh:mm:ss'形式の文字列を与えることで、1970年1月1日0時0分0秒からの経過秒数が返されます。
これを利用して、カラムtの2017年12月8日の正午からの経過時間（秒）を求めます。
SELECT
t,
strftime('%s', t) - strftime('%s', '2017-12-08 12:00:00') AS diff_sec
FROM
t1
;
実行結果：
t diff_sec
------------------- ----------
2017-12-09 13:13:13 90793
2017-12-09 14:13:10 94390
2017-12-08 13:10:48 4248
秒で求まった経過時間を人間が読みやすい書式（'01:00:00'で1時間経過を表す、など）に変換する関数は、SQLiteのリファレンスをざっと見た限りでは見当たりませんでした。
SQLiteを使うアプリケーション側で整形するのが妥当なのでしょうか。
過去2時間以内のデータを抽出
ある日時から任意の秒数や時間、日数を足し引きした後の日時文字列を求めるには、datetime()関数のmodifierを利用できます。
datetime()関数の第一引数には'yyyy-mm-dd hh:mm:ss'形式の日時文字列、第二引数にmodifierを与えます。
以下のような感じで使えます。
sqlite> SELECT datetime('2017-12-09 15:00:00', '+1 seconds');
datetime('2017-12-09 15:00:00', '+1 seconds')
---------------------------------------------
2017-12-09 15:00:01
sqlite> SELECT datetime('2017-12-09 15:00:00', '-10 minutes');
datetime('2017-12-09 15:00:00', '-10 minutes')
----------------------------------------------
2017-12-09 14:50:00
-- カンマ区切りで複数指定もできる
sqlite> SELECT datetime('2017-12-09 15:00:00', '-2 months', '-1 days');
datetime('2017-12-09 15:00:00', '-2 months', '-1 days')
-------------------------------------------------------
2017-10-08 15:00:00
これを利用すると、2017年12月9日15時ちょうどから過去2時間以内のデータを抽出するには、以下のようなSQLが利用できます。
SELECT * FROM t1
WHERE datetime('2017-12-09 15:00:00', '-2 hours') < t;
実行結果
t name
------------------- ----------
2017-12-09 13:13:13 t2
2017-12-09 14:13:10 t3
参考
以下のリファレンスを参考にしました。modifierの一覧はModifiersの節にあります。また、strftime()で任意の書式で日時を記述する方法も解説されています。
SQL As Understood By SQLite - Date And Time Functions