SELECT文だけでカレンダーデータを作成する

私は以前、個人的に勤務時間管理をするデータベースがほしかったのですが、どうしても勤務日を管理するカレンダーデータが必要となってしまいました。
ですが……

SQLiteでは行を返す関数が定義できないので、当然カレンダーを作るには以下の手順を必要とします。

1. 「カレンダー」テーブルをCREATE文で用意
2. 「カレンダー」に必要な日付データをINSERT文で用意

問題は２の「カレンダー」に必要な日付データをINSERT文で用意することです。
1年程度のデータであればExcelでも利用して作成できるかも知れません。
でも、個人的に使う程度のデータベースにそこまで手間をかけてやるのもイヤだし、日付データの修正をおこなうのも面倒です。

何とかできないか？と考えて採用したのが下の方法です。

SQLiteにはWITH句というものが用意されています。これはインラインビューとか共通テーブル式とか言われています。
さらにWITH句で定義したものを自分自身が呼び出せる「再帰」処理が可能な点が強力です。

これを利用すると、
カレンダーの始まりと終わりの日付のみ指定すればその間の日付はSQLiteが自動で生成
してくれるようなSELECT文を作成できます！

そのクエリが以下の通りのSELECT文です。
例として、2010年01月01日からSELECT文実行日の20年後までのSELECT文を作ってみました。
※この例では20年後日付としましたが、もちろん直接日付を指定してもOKです。

WITH RECURSIVE calendar(calendar_date) AS (
    SELECT 
        -- ここに始まりの日付を指定する
        DATE('2010-01-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここに終わりの日付を指定する
        calendar_date < DATE('now', '+20 years')
)
-- ここが実際のSELECT文
SELECT calendar_date FROM calendar;
            

ただ、上のSELECT文をカレンダーが必要になったとき毎回書くのは面倒です。
ではどうするか？ここでSELECT文なのが効いてきます。

データベースにはビュー(VIEW)という便利な機能があります。
上のカレンダー取得SELECT文をビューとして、そのまま定義してしまえばいいのです！

それは以下のようになりますね。

-- ビュー定義CREATE文
CREATE VIEW calendar 
( 
  calendar_date
) AS 
WITH RECURSIVE calendar(calendar_date) AS (
    SELECT 
        DATE('2010-01-01') AS calendar_date
    UNION ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        calendar_date < DATE('now', '+20 years')
)
SELECT calendar_date FROM calendar;
            

これで何時でも下のように簡単なSELECT文として読み出すことができるようになりました！

SELECT calendar_date FROM calendar;
            

ここからは応用編として、もっとカレンダーに便利機能を入れたいと思います。
具体的には以下の通りです。

1. 日付と同時に曜日も取りたい

2. 祝日名も取りたい

このくらいのデータがあれば勤務日を管理するカレンダーデータとしては合格できそうですね。
じゃあ、さくっと実装したのが以下のビューです。

-- 祝日管理用テーブル
CREATE TABLE holydays
(holyday_date       DATE          NOT NULL
,holyday_name       VARCHAR(100)  NOT NULL
,PRIMARY KEY(holyday_date));

-- 祝日データ
-- とりあえず 2016年から2018年分を用意
INSERT INTO holydays
(holyday_date, holyday_name) VALUES
('2016-01-01', '元旦'),
('2016-01-11', '成人の日'),
('2016-02-11', '建国記念の日'),
('2016-03-20', '春分の日'),
('2016-03-21', '振替休日'),
('2016-04-29', '昭和の日'),
('2016-05-03', '憲法記念日'),
('2016-05-04', 'みどりの日'),
('2016-05-05', 'こどもの日'),
('2016-07-18', '海の日'),
('2016-08-11', '山の日'),
('2016-09-19', '敬老の日'),
('2016-09-22', '秋分の日'),
('2016-10-10', '体育の日'),
('2016-11-03', '文化の日'),
('2016-11-23', '勤労感謝の日'),
('2016-12-23', '天皇誕生日'),
('2017-01-01', '元旦'),
('2017-01-02', '振替休日'),
('2017-01-09', '成人の日'),
('2017-02-11', '建国記念の日'),
('2017-03-20', '春分の日'),
('2017-04-29', '昭和の日'),
('2017-05-03', '憲法記念日'),
('2017-05-04', 'みどりの日'),
('2017-05-05', 'こどもの日'),
('2017-07-17', '海の日'),
('2017-08-11', '山の日'),
('2017-09-18', '敬老の日'),
('2017-09-23', '秋分の日'),
('2017-10-09', '体育の日'),
('2017-11-03', '文化の日'),
('2017-11-23', '勤労感謝の日'),
('2017-12-23', '天皇誕生日'),
('2018-01-01', '元旦'),
('2018-01-08', '成人の日'),
('2018-02-11', '建国記念の日'),
('2018-02-12', '振替休日'),
('2018-03-21', '春分の日'),
('2018-04-29', '昭和の日'),
('2018-04-30', '振替休日'),
('2018-05-03', '憲法記念日'),
('2018-05-04', 'みどりの日'),
('2018-05-05', 'こどもの日'),
('2018-07-16', '海の日'),
('2018-08-11', '山の日'),
('2018-09-17', '敬老の日'),
('2018-09-23', '秋分の日'),
('2018-09-24', '振替休日'),
('2018-10-08', '体育の日'),
('2018-11-03', '文化の日'),
('2018-11-23', '勤労感謝の日'),
('2018-12-23', '天皇誕生日'),
('2018-12-24', '振替休日');

-- 曜日/祝日付きのカレンダー
CREATE VIEW calendar
(
    calendar_date
    , holyday_name
    , week_day
) AS 
WITH RECURSIVE calendar_no_holyday(calendar_date) AS (
    -- 2010/01/01からSELECT文実行日から+20年までのカレンダー
    SELECT 
        DATE('2010-01-01') AS calendar_date
    UNION ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar_no_holyday
    WHERE
        calendar_date < DATE('now', '+20 years')
)
SELECT
  calendar_date
  -- 祝日管理用テーブルとはLEFT JOINなので、
  -- NULLが混入しないようCOALESCE関数を使用して祝日名を取得する。
  , COALESCE(holyday_name, '') AS holyday_name
  -- 日付から曜日を計算してもらい、その値で曜日を判定
  , CASE STRFTIME('%w', calendar_date)
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土'
    ELSE ''
  END AS week_day
FROM calendar_no_holyday    AS cal
    
    LEFT JOIN
    holydays                AS holy
    ON
        cal.calendar_date = holy.holyday_date;