SQLiteで日時を扱う

# はじめに

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のバージョン

[こちらの記事に示した方法](https://qiita.com/baikichiz/items/c2c7e6dd622cea609a73)により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}`でソートができます。

```SQL
-- ソートなし
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タイムスタンプに変換します。

```SQL
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日の正午からの経過時間（秒）を求めます。

```SQL
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_を与えます。
以下のような感じで使えます。

```SQL
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が利用できます。

```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](https://sqlite.org/lang_datefunc.html)