MySQLビューを使って日付範囲から連続日付を生成する方法

MySQLには、他のデータベース製品のような連番生成関数やカレンダーテーブルが標準搭載されていないため、指定期間の全日付を一覧表示したい場合はひと工夫必要です。本記事ではビュー（VIEW）を複数組み合わせて、指定した日付範囲の連続した日付を動的に生成する方法を解説します。

全体の仕組み

この手法は次の3つのステップで構成されます。

1. 0〜9の数字を返すビュー「digits」を作成する
2. digitsを掛け合わせて0〜9999の連続数値を持つビュー「numbers」を作成する
3. 数値を日付演算に変換して日付リストを返すビュー「dates1」を作成する

ステップ1：0〜9の数字を持つ「digits」ビューを作成

まず、UNION ALLを使って0から9までの10個の数値を1列に並べたビューを作成します。

mysql> CREATE VIEW digits AS
    -> SELECT 0 AS digit UNION ALL
    -> SELECT 1 UNION ALL
    -> SELECT 2 UNION ALL
    -> SELECT 3 UNION ALL
    -> SELECT 4 UNION ALL
    -> SELECT 5 UNION ALL
    -> SELECT 6 UNION ALL
    -> SELECT 7 UNION ALL
    -> SELECT 8 UNION ALL
    -> SELECT 9;
Query OK, 0 rows affected (0.08 sec)

ステップ2：0〜9999の連続数値を持つ「numbers」ビューを作成

次に、「digits」を4つ自己結合し、それぞれ一の位・十の位・百の位・千の位として扱うことで、0から9999までの10,000個の連続数値を生成します。

mysql> CREATE VIEW numbers AS
SELECT ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
FROM digits AS ones, digits AS tens, digits AS hundreds, digits AS thousands;
Query OK, 0 rows affected (0.09 sec)

ステップ3：日付を生成する「dates1」ビューを作成

「numbers」の各数値を日付計算に使い、現在日を基準とした過去と未来の日付リストを作成します。

mysql> CREATE VIEW dates1 AS
SELECT SUBDATE(CURRENT_DATE(), number) AS date FROM numbers
UNION ALL
SELECT ADDDATE(CURRENT_DATE(), number + 1) AS date FROM numbers;
Query OK, 0 rows affected (0.09 sec)

SUBDATE(CURRENT_DATE(), number) は現在日から number 日前の日付を、ADDDATE(CURRENT_DATE(), number + 1) は現在日から number＋1 日後の日付を返します。これにより、今日を挟んで過去9999日分と未来10000日分、合計約20,000日分の日付が自動的に生成されます。

ステップ4：BETWEEN句で目的の日付範囲を抽出

あとはWHERE句のBETWEENで範囲を指定するだけで、その期間の全日付を取得できます。

mysql> SELECT date FROM dates1
WHERE date BETWEEN '2017-11-15' AND '2017-11-30'
ORDER BY date;
+------------+
| date       |
+------------+
| 2017-11-15 |
| 2017-11-16 |
| 2017-11-17 |
| 2017-11-18 |
| 2017-11-19 |
| 2017-11-20 |
| 2017-11-21 |
| 2017-11-22 |
| 2017-11-23 |
| 2017-11-24 |
| 2017-11-25 |
| 2017-11-26 |
| 2017-11-27 |
| 2017-11-28 |
| 2017-11-29 |
| 2017-11-30 |
+------------+
16 rows in set (0.05 sec)

2017年11月15日から30日までの16日分が正しく出力されました。

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

MySQL 8.0以降を使用している場合は、再帰共通テーブル式（WITH RECURSIVE）を使えば、事前にビューを定義しなくても同じ結果が得られます。

WITH RECURSIVE calendar AS (
  SELECT DATE('2017-11-15') AS date
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM calendar WHERE date < '2017-11-30'
)
SELECT * FROM calendar;

なお、「dates1」はCURRENT_DATE()を基準としているため、実行時点から大きく離れた日付（約27年以上前または未来）を取得したい場合は、基準日を固定の日付に変更するか、桁数を増やすなどの調整が必要です。