MySQLで日付の範囲から日付リストを生成する方法

MySQLで日付の範囲から日付リストを生成する方法

MySQLでは、adddate()関数と複数の派生テーブル（数字テーブル）を組み合わせたクエリを使うことで、指定した期間内のすべての日付を簡単に生成できます。

以下の例では、'2016-12-15'から'2016-12-31'までの日付を一覧として生成しています。

日付を生成するSQLクエリ

mysql> select * from
    -> (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
    -> (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    -> Where gen_date between '2016-12-15' and '2016-12-31'
    -> ;
+------------+
| gen_date   |
+------------+
| 2016-12-15 |
| 2016-12-16 |
| 2016-12-17 |
| 2016-12-18 |
| 2016-12-19 |
| 2016-12-20 |
| 2016-12-21 |
| 2016-12-22 |
| 2016-12-23 |
| 2016-12-24 |
| 2016-12-25 |
| 2016-12-26 |
| 2016-12-27 |
| 2016-12-28 |
| 2016-12-29 |
| 2016-12-30 |
| 2016-12-31 |
+------------+
17 rows in set (0.30 sec)

クエリの仕組み

このクエリのポイントは、0〜9の数字だけを持つ5つのサブクエリ（t0〜t4）をクロス結合している点です。これにより、0から99999までの連続した数値が作られます。

次に、その数値を基準日である '1970-01-01' に adddate() 関数で加算することで、連続した日付を大量に生成しています。最後に WHERE 句の BETWEEN で目的の範囲だけに絞り込む仕組みです。

生成したい期間が短い場合は、数字テーブルの数を減らすことでクエリをシンプルにできます。たとえば1年分程度であれば、3つのテーブル（0〜999）でも十分対応可能です。

MySQL 8.0以降なら再帰CTEも便利

MySQL 8.0以降を使用している場合は、再帰CTE（共通テーブル式）を使うと、より直感的に記述できます。

WITH RECURSIVE dates AS (
  SELECT '2016-12-15' AS gen_date
  UNION ALL
  SELECT gen_date + INTERVAL 1 DAY
  FROM dates
  WHERE gen_date + INTERVAL 1 DAY <= '2016-12-31'
)
SELECT * FROM dates;

開始日から1日ずつ加算しながら終了日に達するまで行を生成するため、コードの意図が分かりやすく、メンテナンス性も向上します。用途やバージョンに応じて使い分けると良いでしょう。