ふと、今月の日付一覧が欲しくなって、MySQLでやってみた。以前は、カマセデータ用に一時的にテーブルを作る必要があったけど、今は VALUES文のおかげで、随分ラクになった、、、、かな。
今月ついたちの日付を得るのは、色々なやり方があるけれども、これがシンプル。
mysql> SELECT DATE_FORMAT(NOW(), '%Y/%m/01'); +--------------------------------+ | DATE_FORMAT(NOW(), '%Y/%m/01') | +--------------------------------+ | 2020/04/01 | +--------------------------------+ 1 row in set (0.00 sec)
これに適当な日数を加えれば、任意の日付を得られるので、例えば4日加えるのは、こう。
mysql> SELECT DATE_ADD( -> DATE_FORMAT(NOW(), '%Y/%m/01') -> ,INTERVAL 4 DAY) d; +------------+ | d | +------------+ | 2020-04-05 | +------------+ 1 row in set (0.01 sec)
ほしいのは4日後ひとつだけじゃなくて、1ヶ月ぶんなので、とりあえず10日ぶん加えるのは、ここでVALUES文を使うと便利（以前は、加える日数だけを入れたテーブルを別途用意していた）。
mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9)) -> SELECT DATE_ADD( -> DATE_FORMAT(NOW(), '%Y/%m/01') -> ,INTERVAL n.num DAY) d FROM n; +------------+ | d | +------------+ | 2020-04-01 | | 2020-04-02 | | 2020-04-03 | | 2020-04-04 | | 2020-04-05 | | 2020-04-06 | | 2020-04-07 | | 2020-04-08 | | 2020-04-09 | | 2020-04-10 | +------------+ 10 rows in set (0.01 sec)
ということで、10日ぶんを得ることができたので、あとはこのまま VALUES に 31まで並べればいいんだね。 というのは、間違いではないけど、かしこくもない発想。演算で求めるのが知恵を使ったやり方。とりあえずちょっと多めだけど 100日ぶん、やってみる。見やすさのために ORDER BY も付けてみた。
mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9)) -> SELECT DATE_ADD( -> DATE_FORMAT(NOW(), '%Y/%m/01') -> ,INTERVAL n1.num*10+n2.num DAY) d -> FROM n n1, n n2 -> ORDER BY d; +------------+ | d | +------------+ | 2020-04-01 | | 2020-04-02 | | 2020-04-03 | | 2020-04-04 | | 2020-04-05 | ： ： | 2020-07-08 | | 2020-07-09 | +------------+ 100 rows in set (0.00 sec)
ポイントは、0～9の数字を持った n というテーブルがあるので、これを2つ使えば99までの数字を表せるということに気づけるかどうか。
さて、ここで欲しいのは100日間ではなく今月の日数だけなので、月末の日付を取って、そこまでの日数だけのものを加えるようにすれば完成。
今月の最終日付を取る関数 LAST_DAY() と、そこから日付部分だけの数字を得る DAYOFMONTH() の実行例は以下のとおり。
mysql> SELECT LAST_DAY(NOW()); +-----------------+ | LAST_DAY(NOW()) | +-----------------+ | 2020-04-30 | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT DAYOFMONTH(LAST_DAY(NOW())); +-----------------------------+ | DAYOFMONTH(LAST_DAY(NOW())) | +-----------------------------+ | 30 | +-----------------------------+ 1 row in set (0.01 sec)
さっきは100迄を加えていたのを、この関数を使って月の日数未満のものだけを加えるようにすれば完成。ついでに、NOW()が複数箇所に出てくるのがイヤだったので、変数化したよ。
mysql> SET @dt=NOW(); Query OK, 0 rows affected (0.00 sec) mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9)) -> SELECT DATE_ADD( -> DATE_FORMAT(@dt, '%Y/%m/01') -> ,Interval n1.num*10+n2.num day) d -> FROM n n1, n n2 -> WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt)) -> ORDER BY d; +------------+ | d | +------------+ | 2020-04-01 | | 2020-04-02 | | 2020-04-03 | ： ： | 2020-04-29 | | 2020-04-30 | +------------+ 30 rows in set (0.00 sec)
変数化したおかげで、任意の日付を与えやすくなった。
mysql> SET @dt='2020/05/05'; Query OK, 0 rows affected (0.00 sec) mysql> WITH n(num) AS (VALUES ROW(0), ROW(1), ROW(2), ROW(3), ROW(4), ROW(5), ROW(6), ROW(7), ROW(8), ROW(9)) -> SELECT DATE_ADD( -> DATE_FORMAT(@dt, '%Y/%m/01') -> ,Interval n1.num*10+n2.num day) d -> FROM n n1, n n2 -> WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt)) -> ORDER BY d; +------------+ | d | +------------+ | 2020-05-01 | | 2020-05-02 | | 2020-05-03 | ： ： | 2020-05-30 | | 2020-05-31 | +------------+ 31 rows in set (0.01 sec)
ちゃんと 5月なら31日までが得られていることがわかる。
この機能、べつに年月日を与える必要はなくて(日の部分は使っていない)、つまり、「年」と「月」だけを与えて動作するように改良することもできるよね。興味のある人は、やってみてください。
追記(2020/04/30) @tom__bo さんのフォロー記事を受けて
本記事を受けて、@tom__boさんが、別の角度からの解法を示してくださいました。
tombo2.hatenablog.com
たまたま、ほんの少しだけ古いバージョン（8.0.17）を使っていたことで、私のクエリで使用していた VALUES文が動作しなかったとのこと。その部分を 再帰CTEに書き換えたのと、日付自体をダイレクトに繰り返し処理しているところが工夫点。
特に 私も、0から9の数字を羅列するのに、今の VALUES の書き方はスマートじゃないなぁと（特に ROW()の記述が必要な点）思っていたので、再帰の部分だけアイデアを拝借して、私のクエリをブラッシュアップ。
SET @dt='2100/02/15'; WITH RECURSIVE n(num) AS ( SELECT 0 UNION ALL SELECT num+1 FROM n WHERE num<9 ) SELECT DATE_ADD( DATE_FORMAT(@dt, '%Y/%m/01') ,Interval n1.num*10+n2.num day) d FROM n n1, n n2 WHERE n1.num*10+n2.num < DAYOFMONTH(LAST_DAY(@dt)) ORDER BY d;
ROW(0), ROW(1)... のように書いているよりもずっとスマート。ぱっと見てこれが 0から9の10件を生成しているって読み取れるかどうかは別の話だけど。
@tom__boさんのクエリに刺激を受けて、そちらのクエリを更に書き換えてみました。ポイントは、日付（@dt）が1回しか出てこないようにした、というところです（あとカラム名にわざわざ予約語を使っているのが個人的にはイヤだったので(笑)そこも変更）
SET @dt='2020/02/15'; WITH RECURSIVE rec(D) AS ( SELECT LAST_DAY(@dt) UNION ALL SELECT D - INTERVAL 1 DAY FROM rec WHERE DAY(D) > 1 ) SELECT D as dt FROM rec ORDER BY dt;
文字通りの「逆転の発想」。
.
