TL;DR
- 意図したことをMySQLでやるには
CURDATE() - INTERVAL 1 DAY
- というか俺はむしろDATE型から数値を引くことに違和感があるんですがこれってOracleの書き方なんでしたっけ？
昨日の朝、こんなのを見た。
昨日の夜、このブログを読んだ。
けんつさんのブログは「文字列とDATE型」だけれど、バグレポートの方は「数値とDATE型」に起因する。
折角なのでちょっと書いておこうかと思った。
まず、DATE型と数値型を演算しようとするとDATE型が数値型にキャストされて戻り値も数値型になる。
DATE型から数値型へのキャストは 年 * 1000000 + 月 * 1000 + 日 で行われる(DATETIME型の場合は時分秒を格納するために更に桁数が増える)。逆の操作で有効な日付が生成できれば、数値型からDATE型へのキャストもできる。
mysql80 13> SELECT CURDATE(), CAST(CURDATE() AS SIGNED), CAST(20200702 AS DATE);
+------------+---------------------------+------------------------+
| CURDATE() | CAST(CURDATE() AS SIGNED) | CAST(20200702 AS DATE) |
+------------+---------------------------+------------------------+
| 2020-07-02 | 20200702 | 2020-07-02 |
+------------+---------------------------+------------------------+
1 row in set (0.00 sec)
よって(2020/7/2から)1を引くと20200701になるし、これはDATE型にキャストし返せる。 - INTERVAL 1 DAY にしておけば最初からDATE型で返ってくる。
mysql80 13> SELECT CURDATE() - 1, CAST(CURDATE() - 1 AS DATE), CURDATE() - INTERVAL 1 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 1 | CAST(CURDATE() - 1 AS DATE) | CURDATE() - INTERVAL 1 DAY |
+---------------+-----------------------------+----------------------------+
| 20200701 | 2020-07-01 | 2020-07-01 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)
これが月をまたぐと雲行きが怪しくなってくる。
mysql80 13> SELECT CURDATE() - 2, CAST(CURDATE() - 2 AS DATE), CURDATE() - INTERVAL 2 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 2 | CAST(CURDATE() - 2 AS DATE) | CURDATE() - INTERVAL 2 DAY |
+---------------+-----------------------------+----------------------------+
| 20200700 | 2020-07-00 | 2020-06-30 |
+---------------+-----------------------------+----------------------------+
1 row in set (0.00 sec)
mysql80 13> SELECT CURDATE() - 3, CAST(CURDATE() - 3 AS DATE), CURDATE() - INTERVAL 3 DAY;
+---------------+-----------------------------+----------------------------+
| CURDATE() - 3 | CAST(CURDATE() - 3 AS DATE) | CURDATE() - INTERVAL 3 DAY |
+---------------+-----------------------------+----------------------------+
| 20200699 | NULL | 2020-06-29 |
+---------------+-----------------------------+----------------------------+
1 row in set, 1 warning (0.00 sec)
閑話休題。今年1年分の日付だけをDATE型で突っ込んだ cal というテーブルを用意した。そういえば今年は閏年だったのか。
mysql80 15> SELECT * FROM cal;
+------------+
| _date |
+------------+
| 2020-01-01 |
| 2020-01-02 |
| 2020-01-03 |
..
| 2020-12-29 |
| 2020-12-30 |
| 2020-12-31 |
+------------+
366 rows in set (0.00 sec)
これで >= を使って比較してみる（「n日前から今日まで」みたいなイメージ）。
やはり月をまたぐとおかしくなるが、「本当に6月分のレコードが存在しなければこういう結果セットでもおかしくないよね」みたいな結果セットであるところがにくい。
mysql80 15> SELECT MIN(_date), CURDATE() - 1, CURDATE() - INTERVAL 1 DAY FROM cal WHERE _date >= CURDATE() - 1;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 1 | CURDATE() - INTERVAL 1 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 | 20200701 | 2020-07-01 |
+------------+---------------+----------------------------+
1 row in set (0.00 sec)
mysql80 15> SELECT MIN(_date), CURDATE() - 2, CURDATE() - INTERVAL 2 DAY FROM cal WHERE _date >= CURDATE() - 2;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 2 | CURDATE() - INTERVAL 2 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 | 20200700 | 2020-06-30 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql80 15> SELECT MIN(_date), CURDATE() - 3, CURDATE() - INTERVAL 3 DAY FROM cal WHERE _date >= CURDATE() - 3;
+------------+---------------+----------------------------+
| MIN(_date) | CURDATE() - 3 | CURDATE() - INTERVAL 3 DAY |
+------------+---------------+----------------------------+
| 2020-07-01 | 20200699 | 2020-06-29 |
+------------+---------------+----------------------------+
1 row in set, 1 warning (0.00 sec)
なおこのパターン、体感として「30日」か「90日」を超えたあたりで気が付く人が多い。
30日以上過去の日付は MONTH(_date) の値が変わることが多くの場合期待されるが、数値型になって30を引かれても100の位は変わらないから、先月ぶんはまるまる引っ掛からない。これに違和感を覚えるのが「30日」のパターン。
mysql80 15> SELECT MIN(_date), CURDATE() - 60, CURDATE() - INTERVAL 60 DAY FROM cal WHERE _date >= CURDATE() - 60;
+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 60 | CURDATE() - INTERVAL 60 DAY |
+------------+----------------+-----------------------------+
| 2020-07-01 | 20200642 | 2020-05-03 |
+------------+----------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
90日以上過去の日付を数値型にキャストするとだいたい 再びDATE型にキャスト可能な10の位の範囲 に戻ってきて、到底期待されない日付として扱われる。これに違和感をおぼえるのが「90日」のパターン。
mysql80 15> SELECT MIN(_date), CURDATE() - 90, CURDATE() - INTERVAL 90 DAY FROM cal WHERE _date >= CURDATE() - 90; -- 7/1で70だと上手くいかないので90にしたけど
+------------+----------------+-----------------------------+
| MIN(_date) | CURDATE() - 90 | CURDATE() - INTERVAL 90 DAY |
+------------+----------------+-----------------------------+
| 2020-06-12 | 20200612 | 2020-04-03 |
+------------+----------------+-----------------------------+
1 row in set (0.00 sec)
こっちはワーニングすら出ないので、仕様を知らないと混乱するかも。
90日以外のパターンは SHOW WARNINGS でも拾える。
8.0なら performance_schema.events_errors_* でそのワーニングが発生しているかどうかも多少はあたりがつけられる(文字列を数値型にキャストしようとした時にも出るワーニングだから100%とは言えないけれども…
mysql80 15> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '20200699' for column '_date' at row 1 |
+---------+------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql80 15> SELECT * FROM performance_schema.events_errors_summary_global_by_error WHERE error_number= 1292;
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| ERROR_NUMBER | ERROR_NAME | SQL_STATE | SUM_ERROR_RAISED | SUM_ERROR_HANDLED | FIRST_SEEN | LAST_SEEN |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
| 1292 | ER_TRUNCATED_WRONG_VALUE | 22007 | 1110 | 0 | 2020-07-02 11:56:07 | 2020-07-02 14:15:55 |
+--------------+--------------------------+-----------+------------------+-------------------+---------------------+---------------------+
1 row in set (0.00 sec)
ワーニングは拾った方が後々楽だと思うけれど、自作しないとなかなか挟むのムズカシイんだよなぁ。。
