SQLiteで日付を扱うのって悩ましい

MySQLなどとは異なり、SQLiteにはDate型が存在しない。そのため、SQLiteで日付データを扱う場合、文字列として、あるいは数値として扱うのが一般的であると思われる。

文字列の場合、'2012-09-12'のようになるが、気をつけなくてはいけないのは、'2012-9-12'などとしてはいけないことだ。つまり、月日は、2ケタに達しない場合、0を左につけなければならない。次のような実験をしてみる：

sqlite> select strftime('%Y-%m', '2012-09-12');
2012-09
sqlite> select strftime('%Y-%m', '2012-9-12');
(何も表示されない)

strftimeによって年月を得ようとしても、0が抜けていると思うように動作しない。また、strftimeで取得しなくても、例えば日付データでorder byをする場合、2ケタ化していないと、例えば09より1のほうが大きいとみなされてしまう。つまり、9月より1月のほうが後、とみなされてしまう。そのため、2ケタ化は必須の作業となる。

もう一つ、数値として日付を扱う場合を考える。この場合も、どの時刻を基準とするか、という選択肢があるが、個人的にはユリウス日を使用するのが最も良いと思う。例えば、2012-09-12をinsertする場合：

sqlite> insert into tbl(date) values(strftime('%J', '2012-09-12'));

のようになる。%Jでユリウス日に変換してから書き込むことができる。ただ、ここでもstrftimeの第二引数は2ケタ化しておく必要がある。

もちろん、strftimeを使用することで、年月日を取得することもできる：

sqlite> select strftime('%Y-%m-%d', date) from tbl;
2012-09-12

また、これは文字列形式、数値形式どちらでも行えるが、例えば年を指定して月別の集計を行うような場合、

sqlite> select sum(cost) as c, strftime('%m',date) as m
             from tbl group by m where strftime('%Y',date) = "2012";

のようにすればよい。つまり、strftimeの結果でgroup byを実行することも当然可能である。でも、ここでも気をつけなくてはいけないのは、"2012"のように、年を""で囲ってやる必要があるということである。strftimeの結果との比較は文字列での比較なので、こうする必要がある。""で囲わなくてもエラーにはならないが、思わぬ結果になってしまう(>=などで試してみればわかる)。

いずれにせよ、ちょっと工夫をすればSQLiteで日付データを扱うことは可能だが、2ケタ化はアプリケーション側で必須であり、strftimeが乱発されてしまうので、あまり美しくはない、と思う。たいていのアプリでは日付データを扱うことがあると思うので、せめてDate型は実装してほしいと皆思っていると思うのだが...

Comments