[SQLite][SQL]Date データの扱い

Date データを扱う時は date() 関数を通す。

sqlite> CREATE TABLE tokyoTemperature (id INTEGER NOT NULL PRIMARY KEY, date DATE NOT NULL, ave FLOAT NOT NULL);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-01'), 6.6);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-02'), 7.0);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-03'), 5.9);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-04'), 6.3);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-05'), 7.3);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-06'), 6.5);
sqlite> SELECT * from tokyoTemperature;
id          date        ave
----------  ----------  ----------
1           2011-01-01  6.6
2           2011-01-02  7.0
3           2011-01-03  5.9
4           2011-01-04  6.3
5           2011-01-05  7.3
6           2011-01-06  6.5
sqlite> SELECT * from tokyoTemperature WHERE date >= date('2011-01-05');
id          date        ave
----------  ----------  ----------
5           2011-01-05  7.3
6           2011-01-06  6.5

date() を通さずにデータを登録すると異なる値が入ってしまう。

sqlite> INSERT INTO tokyoTemperature VALUES (NULL, 2011-01-07, 4.0);
sqlite> SELECT * FROM tokyoTemperature WHERE id = 7;
id          date        ave
----------  ----------  ----------
7           2003        4.0

参考: SQLite Query Language: Date And Time Functions