GA

2020/07/02

MySQLで CURDATE() - 1 は「昨日の日付」を返さない

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)

ワーニングは拾った方が後々楽だと思うけれど、自作しないとなかなか挟むのムズカシイんだよなぁ。。

​
投稿者 時刻:
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )