SQLite には日付型がない。と、我々はいつから錯覚していたのか。
日付型はないのだが、なぜか日付を操作する関数群がある。これが超絶便利。
先に注意点だが、これらの関数は文字列を日付として扱うのだが、 T ではなく空白文字を区切り文字として利用しており、 ISO-8601 に準拠していない 。ドキュメントに記載がある通り 1 YYYY-MM-DD HH:MM:SS というフォーマットである。ミリ秒まで含める場合は、この後ろに .SSS と続く。 DB にはこの形式で入れておいたほうが、 index を効かせられて便利である。また、タイムゾーンが入っていないことにも注意が必要だ。基本的に SQLite3 は UTC と localtime を扱えるのだが、複数のタイムゾーンから値を読み書きをすることを考えれば UTC に揃えておくのが無難だろう。その辺は、以下の記事にも書かれている。
さて、道具があることはわかったが、これだけで本当に日付を扱えるのか。 SQLite3 の日付関数には、非常に強力な modifier という機構がある。これは、 date や datetime の文字列を返す関数に文字列の引数の形で指定をするもので、 UNIX コマンドを | でつなげて実行するような感覚で利用できる。
例えば、入力された文字列を整形する場合に、ミリ秒まで含めたい場合には以下のように subsecond modifier を噛ませる。これで出力に .SSS が付与される。ミリ秒まである形式の日付を正規化して INSERT する場合に用いることができる。
datetime(?, 'subsecond')
localtime を UTC にしたい場合には、 utc modifier を通す。日付の足し算も modifier として +1 days のように書くといい。テーブル内に UTC でデータが入っているものとして、 localtime で日付を指定して検索を行う場合には以下のようになる。正規化された日付文字列の辞書順は時系列での sort と等しいので、きちんと index も効かせることができる。
WHERE datetime(?, 'utc') <= timestamp_column
AND timestamp_column < datetime(?, 'utc', '+1 days')",
なんともうまくできた仕組みである。
- ドキュメントには ISO-8601 であると書いてあるので注意。↩
