先日「MySQL4.0からのバージョンアップ時のハマリ点〜不正な日付の扱い〜」というエントリーで、MySQL 4.0 以前では日付型に「どの月でも "31"日までの日付を登録できる」ということを紹介しました。併せて、MySQL 5.0以降でも sql-mode に ALLOW_INVALID_DATES を指定すれば従来どおりの「2月30日」や「2月31日」が登録可能であることも紹介しました。
ではこれらの「不正な」日付は処理の中ではいったいどのように扱われるのでしょうか。足し引きの計算はできるのでしょうか。
試してみました。
結論は、
・その月の日を超えた部分は翌月の 1日、2日、、として扱われる
例：6月31日→７月１日 (閏年でない)２月30日→３月２日
以下、試した足跡です。なお今回はたまたま手元の使いやすい環境にあった MySQL 5.0.45 を使用しました。
データをつくる
前回とおなじテーブル構成です。データの中には正常な日付をひとつ加えておきました。
CREATE TABLE dttest (a int auto_increment NOT NULL PRIMARY KEY, b datetime) ENGINE=InnoDB;
INSERT INTO dttest (b) VALUES ("2009-11-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-09-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-06-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-04-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-31 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-30 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-02-29 00:00:00");
INSERT INTO dttest (b) VALUES ("2009-07-10 00:00:00");
おためし SQL を考える
以下の日付の足し算引き算、時刻の足し算引き算をしてみることにしました。
SELECT b, ADDDATE(b, INTERVAL 0 DAY), ADDDATE(b, INTERVAL 1 DAY) FROM dttest;
SELECT b, SUBDATE(b, INTERVAL 0 DAY), SUBDATE(b, INTERVAL 1 DAY) FROM dttest;
SELECT b, ADDTIME(b, "00:00:00"), ADDTIME(b, "00:00:01") FROM dttest;
SELECT b, SUBTIME(b, "00:00:00"), SUBTIME(b, "00:00:01") FROM dttest;
SQLを実行！
以下実行結果です。
mysql> SELECT b, ADDDATE(b, INTERVAL 0 DAY), ADDDATE(b, INTERVAL 1 DAY) FROM dttest; +---------------------+----------------------------+----------------------------+ | b | ADDDATE(b, INTERVAL 0 DAY) | ADDDATE(b, INTERVAL 1 DAY) | +---------------------+----------------------------+----------------------------+ | 2009-11-31 00:00:00 | 2009-12-01 00:00:00 | 2009-12-02 00:00:00 | | 2009-09-31 00:00:00 | 2009-10-01 00:00:00 | 2009-10-02 00:00:00 | | 2009-06-31 00:00:00 | 2009-07-01 00:00:00 | 2009-07-02 00:00:00 | | 2009-04-31 00:00:00 | 2009-05-01 00:00:00 | 2009-05-02 00:00:00 | | 2009-02-31 00:00:00 | 2009-03-03 00:00:00 | 2009-03-04 00:00:00 | | 2009-02-30 00:00:00 | 2009-03-02 00:00:00 | 2009-03-03 00:00:00 | | 2009-02-29 00:00:00 | 2009-03-01 00:00:00 | 2009-03-02 00:00:00 | | 2009-07-10 00:00:00 | 2009-07-10 00:00:00 | 2009-07-11 00:00:00 | +---------------------+----------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> SELECT b, SUBDATE(b, INTERVAL 0 DAY), SUBDATE(b, INTERVAL 1 DAY) FROM dttest; +---------------------+----------------------------+----------------------------+ | b | SUBDATE(b, INTERVAL 0 DAY) | SUBDATE(b, INTERVAL 1 DAY) | +---------------------+----------------------------+----------------------------+ | 2009-11-31 00:00:00 | 2009-12-01 00:00:00 | 2009-11-30 00:00:00 | | 2009-09-31 00:00:00 | 2009-10-01 00:00:00 | 2009-09-30 00:00:00 | | 2009-06-31 00:00:00 | 2009-07-01 00:00:00 | 2009-06-30 00:00:00 | | 2009-04-31 00:00:00 | 2009-05-01 00:00:00 | 2009-04-30 00:00:00 | | 2009-02-31 00:00:00 | 2009-03-03 00:00:00 | 2009-03-02 00:00:00 | | 2009-02-30 00:00:00 | 2009-03-02 00:00:00 | 2009-03-01 00:00:00 | | 2009-02-29 00:00:00 | 2009-03-01 00:00:00 | 2009-02-28 00:00:00 | | 2009-07-10 00:00:00 | 2009-07-10 00:00:00 | 2009-07-09 00:00:00 | +---------------------+----------------------------+----------------------------+ 8 rows in set (0.00 sec)
mysql> SELECT b, ADDTIME(b, "00:00:00"), ADDTIME(b, "00:00:01") FROM dttest; +---------------------+------------------------+------------------------+ | b | ADDTIME(b, "00:00:00") | ADDTIME(b, "00:00:01") | +---------------------+------------------------+------------------------+ | 2009-11-31 00:00:00 | 2009-12-01 00:00:00 | 2009-12-01 00:00:01 | | 2009-09-31 00:00:00 | 2009-10-01 00:00:00 | 2009-10-01 00:00:01 | | 2009-06-31 00:00:00 | 2009-07-01 00:00:00 | 2009-07-01 00:00:01 | | 2009-04-31 00:00:00 | 2009-05-01 00:00:00 | 2009-05-01 00:00:01 | | 2009-02-31 00:00:00 | 2009-03-03 00:00:00 | 2009-03-03 00:00:01 | | 2009-02-30 00:00:00 | 2009-03-02 00:00:00 | 2009-03-02 00:00:01 | | 2009-02-29 00:00:00 | 2009-03-01 00:00:00 | 2009-03-01 00:00:01 | | 2009-07-10 00:00:00 | 2009-07-10 00:00:00 | 2009-07-10 00:00:01 | +---------------------+------------------------+------------------------+ 8 rows in set (0.00 sec) mysql> SELECT b, SUBTIME(b, "00:00:00"), SUBTIME(b, "00:00:01") FROM dttest; +---------------------+------------------------+------------------------+ | b | SUBTIME(b, "00:00:00") | SUBTIME(b, "00:00:01") | +---------------------+------------------------+------------------------+ | 2009-11-31 00:00:00 | 2009-12-01 00:00:00 | 2009-11-30 23:59:59 | | 2009-09-31 00:00:00 | 2009-10-01 00:00:00 | 2009-09-30 23:59:59 | | 2009-06-31 00:00:00 | 2009-07-01 00:00:00 | 2009-06-30 23:59:59 | | 2009-04-31 00:00:00 | 2009-05-01 00:00:00 | 2009-04-30 23:59:59 | | 2009-02-31 00:00:00 | 2009-03-03 00:00:00 | 2009-03-02 23:59:59 | | 2009-02-30 00:00:00 | 2009-03-02 00:00:00 | 2009-03-01 23:59:59 | | 2009-02-29 00:00:00 | 2009-03-01 00:00:00 | 2009-02-28 23:59:59 | | 2009-07-10 00:00:00 | 2009-07-10 00:00:00 | 2009-07-09 23:59:59 | +---------------------+------------------------+------------------------+ 8 rows in set (0.00 sec)
改めて結論
６月３１日は内部では７月１日として扱われている。ゼロ日を加えたりゼロ日を引いたりすると「７月１日」として出力されています。 １日加えたり１日引いたりした場合はそれぞれ「７月２日」「６月30日」となることからも、この扱いは一貫している。
時刻部分に着目して「１秒」を足したり引いたりした場合でも同様のことが裏付けられた。
また２月29,30,31日がそれぞれ、3月1,2,3日として扱われることも確認ができた。
.
