こんにちは。hnwです。今回はSQLの話題です。SQLで、TIMESTAMP型（MySQL で言えばDATETIME型）のカラムに未定義値の意味で「0000-00-00 00:00:00」という値をセットする人が居るかと思います。でも、これは止めた方がいいんじゃないでしょうか、という話題です。
標準SQLについて
私の手元に『標準SQLガイド 改訂第4版』というSQL/92の解説本があるのですが、「17.3 日時」によれば、TIMESTAMP型で扱える範囲は0001-01-01 00:00:00から9999-12-31 23:59:61.999までとなっています。つまり、標準SQLのTIMESTAMP型を考えると0000-00-00の扱いは未定義ということになります。
PostgreSQLについて
PostgreSQLのTIMESTAMP型に関して言うと、標準SQLを超える範囲の年も扱えるようです。紀元前4713年から5874897年まで（PostgreSQL 7.3.x以前では146501年まで）扱える、とマニュアルには書いてあります。
私の手元のPostgreSQL8.1.9で実験してみました。
sampledb=> CREATE TABLE sampletable(d timestamp); CREATE TABLE sampledb=> INSERT INTO sampletable VALUES('0000-01-01'); INSERT 0 1 sampledb=> SELECT * FROM sampletable; d ------------------------ 0001-01-01 00:00:00 BC (1 row)
0000年を試してみたところ、紀元前1年と解釈されました。これはISO 8601に従うとすれば正しい挙動です。
sampledb=> INSERT INTO sampletable VALUES('0000-00-00'); ERROR: date/time FIELD value out of range: "0000-00-00" HINT: Perhaps you need a different "datestyle" setting.
一方で、月と日については0を与えるとエラーとなります。ISO 8601に従うとすれば月も日も1以上として規定されており、エラーが出ても不思議ではないと言えます。
ISO 8601について
ISO 8601を突然持ち出しましたが、ISO 8601というのはSQLに限らず、日付文字列の解釈についての標準規格だと私は認識しています。各種プログラミング言語の中の人々も参照している規格だと思いますので、DBが出力する日付文字列はISO 8601から逸脱しない方が望ましいはずです。
MySQLについて
ところで、MySQLでは「0000-00-00」が使えることが半ば前提のような気がします。特に何も設定をしていないMySQLでは、ありえない日付は全て0000-00-00として扱われるからです。私の手元のMySQL 5.0.22で実験してみました。
ただし、MySQLのTIMESTAMP型は標準SQLのTIMESTAMP型とは意味が異なります。標準SQLのTIMESTAMP型に一番近いと思われるのはDATETIME型です。ここではMySQLのDATETIME型について見ていきます。
mysql> CREATE TABLE sampletable(d datetime); Query OK, 0 rows affected (0.01 sec) mysql> INSERT sampletable VALUES('0000-01-01'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM sampletable; +---------------------+ | d | +---------------------+ | 0000-01-01 00:00:00 | +---------------------+ 1 row IN SET (0.00 sec)
0年としてINSERTしてみたところ、SELECTしてみても0年となっています。
mysql> UPDATE sampletable SET d='0000-00-00'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM sampletable; +---------------------+ | d | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row IN SET (0.00 sec)
0月と0日についても同様で、そのまま記録されています。マニュアルによれば、0は不明とか未定義とかダミーとかの意味だそうです。
mysql> UPDATE sampletable SET d='9999-12-31'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM sampletable; +---------------------+ | d | +---------------------+ | 9999-12-31 00:00:00 | +---------------------+ 1 row IN SET (0.00 sec) mysql> UPDATE sampletable SET d='9999-12-32'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SHOW warnings; +---------+------+-----------------------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------------------+ | Warning | 1264 | Out of range value adjusted FOR COLUMN 'd' at row 1 | +---------+------+-----------------------------------------------------+ 1 row IN SET (0.00 sec) mysql> SELECT * FROM sampletable; +---------------------+ | d | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row IN SET (0.00 sec)
ありえない日付が入ると勝手に0000-00-00にされてしまいます。SQL_MODEの設定により年月日とも0が入らないような挙動にもできますが、そのような設定変更をしている環境はあまり無さそうに思います。
結論
OracleやMS SQLやDB2に関しては、私が気楽に触れるテスト環境が無かったので試していません。ただ、標準SQLとISO 8601と2つの標準に沿っていないものを積極的に使う理由は無いでしょう。趣味やプロジェクトでの標準化が原因で日付フィールドがNOT NULLとなっていることもあるでしょうけど、そうでなければ未定義値の意味でNULLを利用すべきではないでしょうか。もしくは、0001-01-01 を未定義値の代わりに使うわけにはいかないでしょうか。
SQLについては一家言ある方が多いでしょうし、反対の意見の方もいらっしゃるかもしれません。ご意見お待ちしております。
参考リンク
追記：おそらく11/12頃から11/13 12:00頃まで以前の書きかけの原稿に先祖返りしていました。ごめんなさい。
コメント / トラックバック 1 件
[...] こんにちは。hnwです。今回は前回記事「SQLのTIMESTAMP型と日付「0000-00-00」」の続きで、MySQLの日付について少し実験してみました。 [...]
コメントする