和暦対応（誰得）
先週の社内DB勉強会のネタ枠で出したネタです。
テーマは「和の暦」
和暦？
和暦といっても色々な単位がある。
月の単位
二十四節気の単位
七十二候の単位
他にもあるかもしれないけど、今回はこの3つの単位を使ってPostgreSQLのtimestamp(date)を検索しようと思う。
和暦のおさらい
今回扱う3種類の和暦に関して一応おさらい。
月の単位
睦月、如月、弥生、卯月、皐月・・・
一応、言っておくけど「艦これ」の艦娘のリストじゃないから！
帝国海軍の駆逐艦の名前が被ってるだけだから！
なお、6月以降は
水無月、葉月、長月、神無月(神在月)、霜月、師走。
七十二候
七十二候は二十四節気の各節気を初候、次候、末候の3つの候(各候はだいたい5日前後)で細分化したもの。
二十四節気までは見たことがあっても、七十二候は普段は目にしない言葉が多いですね。結構読めないものも多いし、通常の漢字表記ではないものも多々ある・・・
(「東風解凍」って「とんぷうかいとう」だと思ってたら「はるかぜこおりをとく」という読みなんですよねｗ）
七十二候は以下の様なものらしい。
東風解凍、黄鶯?薭、魚上氷、土脉潤起、霞始靆、草木萠動、蟄虫>啓戸、桃始笑、菜虫化蝶、雀始巣、桜始開、雷乃発声、玄鳥至、鴻雁北、虹始見、葭始生、霜止出苗、牡丹華、蛙始鳴、蚯蚓出、竹笋生、蚕起食桑、紅花栄、麦秋至、螳
螂生、腐草為蛍、梅子黄、乃東枯、菖蒲華、半夏生、温風至、蓮始開、鷹乃学習、桐始結花、土潤溽暑、大雨時行、涼風至、寒蝉鳴、蒙霧升降、綿柎開、天地始粛、禾>乃登、草露白、鶺鴒鳴、玄鳥去、雷乃収声、蟄虫坏戸、水始涸、鴻雁来、菊花開、蟋蟀在戸、霜始降、霎時施、楓蔦黄、山茶始開、地始凍、金盞香、虹蔵不見、朔風払>葉、橘始黄、閉塞成冬、熊蟄穴、?魚群、乃東生、麋角解、雪下出麦、芹乃栄、水泉動、雉始?、款冬華、水沢腹堅、鶏始乳
和暦をPostgreSQLで使えるようにしてみる。
しかし、timestampの検索条件として上記のような和暦をPostgreSQLでは使うことが出来ない(多分、扱えるRDBMSはないと思う)。なので使えるように拡張してみた。
ついでに演算子(<=>)も追加してみた。
wareki=# TABLE lifelog; id | data | ts -----+---------------------------------------------+--------------------- 1 | 港南区で鶏白湯らーめんを食べた。 | 2012-09-01 00:00:00 2 | 伊勢佐木町で油そばを食べた。 | 2012-09-02 00:00:00 3 | 大さん橋で冷やしラーメンを食べた。 | 2012-09-02 00:00:00 （中略） 189 | 野毛で醤油ラーメンを食べた。 | 2013-08-18 00:00:00 (189 rows)
過去のラー食記録を元に、こんな感じのライフログ(笑)を用意した
これに対して和暦を使った検索をしてみる。
検索条件に和暦を使うだけでなく、timestampから和暦に変換するFunctionもついでに作ってみた。
で、こんな感じ。
wareki=# SELECT data, ts2w24(ts), ts2w12(ts), ts FROM lifelog WHERE ts <=> '如月';
data | ts2w24 | ts2w12 | ts
------------------------------------------+--------+--------+---------------------
伊勢佐木で醤油ラーメンを食べた。 | 大寒 | 如月 | 2013-02-02 00:00:00
中区で激辛ラーメンを食べた。 | 大寒 | 如月 | 2013-02-03 00:00:00
伊豆西岸で変わった醤油ラーメンを食べた。 | 立春 | 如月 | 2013-02-08 00:00:00
伊豆東岸でサンマーメンを食べた。 | 立春 | 如月 | 2013-02-09 00:00:00
中区で刀削麺を食べた。 | 立春 | 如月 | 2013-02-10 00:00:00
根岸でタンタンメンを食べた。 | 立春 | 如月 | 2013-02-11 00:00:00
市ヶ谷で醤油ラーメンを食べた。 | 立春 | 如月 | 2013-02-16 00:00:00
職場近くで塩ラーメンを食べた。 | 雨水 | 如月 | 2013-02-23 00:00:00
目黒で家系ラーメンを食べた。 | 雨水 | 如月 | 2013-02-24 00:00:00
大井町(東京)で二郎インスパイアを食べた。 | 雨水 | 如月 | 2013-02-26 00:00:00
みなとみらいで炸醤刀削麺を食べた。 | 雨水 | 如月 | 2013-02-28 00:00:00
(11 rows)
wareki=# SELECT data, ts2w24(ts), ts2w12(ts), ts FROM lifelog WHERE ts <=> '立春';
data | ts2w24 | ts2w12 | ts
------------------------------------------+--------+--------+---------------------
伊豆西岸で変わった醤油ラーメンを食べた。 | 立春 | 如月 | 2013-02-08 00:00:00
伊豆東岸でサンマーメンを食べた。 | 立春 | 如月 | 2013-02-09 00:00:00
中区で刀削麺を食べた。 | 立春 | 如月 | 2013-02-10 00:00:00
根岸でタンタンメンを食べた。 | 立春 | 如月 | 2013-02-11 00:00:00
市ヶ谷で醤油ラーメンを食べた。 | 立春 | 如月 | 2013-02-16 00:00:00
(5 rows)
wareki=# SELECT data, ts2w24(ts), ts2w12(ts), ts FROM lifelog WHERE ts <=> '東風解凍';
data | ts2w24 | ts2w12 | ts
------------------------------------------+--------+--------+---------------------
伊豆西岸で変わった醤油ラーメンを食べた。 | 立春 | 如月 | 2013-02-08 00:00:00
(1 row)
wareki=# 雅ですね(力説)ｗ
実装
今回はあえてSQLとplpgsqlのみで実装してみた。
というのは、9.2から導入されたrange datatypeを活用したかったから。
DOMAIN定義
まず、和暦表記を受け付ける型をTEXT型のDOMAINとして実装する。
こんな感じ。
CREATE DOMAIN wareki AS text CONSTRAINT wareki_check NOT NULL CHECK ( VALUE ~ '(立春|雨水|啓蟄|春分|清明|穀雨|立夏|小満|芒種|夏至|小暑|大暑|立秋|処暑|白露|秋分|寒露|霜降|立冬|小雪|大雪|冬至|小寒|大寒|睦月|如月|衣更着 |弥生|卯月|皐月|早月|水無月|文月|葉月|長月|神無月|神有月|霜月|師走|東風解凍|黄鶯〓薭|魚上氷|土脉潤起|霞始靆|草木萠動|蟄虫啓戸|桃始笑|菜虫化蝶|雀始 巣|桜始開|雷乃発声|玄鳥至|鴻雁北|虹始見|葭始生|霜止出苗|牡丹華|蛙始鳴|蚯蚓出|竹笋生|蚕起食桑|紅花栄|麦秋至|螳螂生|腐草為蛍|梅子黄|乃東枯|菖蒲華|半 夏生|温風至|蓮始開|鷹乃学習|桐始結花|土潤溽暑|大雨時行|涼風至|寒蝉鳴|蒙霧升降|綿柎開|天地始粛|禾乃登|草露白|鶺鴒鳴|玄鳥去|雷乃収声|蟄虫坏戸|水始涸 |鴻雁来|菊花開|蟋蟀在戸|霜始降|霎時施|楓蔦黄|山茶始開|地始凍|金盞香|虹蔵不見|朔風払葉|橘始黄|閉塞成冬|熊蟄穴|〓魚群|乃東生|麋角解|雪下出麦|芹乃栄|水泉動|雉始〓|款冬華|水沢腹堅|鶏始乳)' );
単純にTEXT型のCONSTRAINTの正規表現として、月表記、二十四節気、七十二候を並べただけ。
これを wareki ドメインとして定義する。
和暦表記からDOY(Day of Year)への変換
warekiドメインのTEXTから、その表記が表すDay of Yearのin4range型に変換する。
(なのでこのEXTENSIONは9.2以降でないと動作しない)
コードはこんな感じ。
CREATE OR REPLACE FUNCTION wareki_to_doys(w wareki) RETURNS int4range AS $$
DECLARE
BEGIN
-- warekiの値によってdoy(Day of Year)のint4rangeをセットする
CASE w
-- 二十四節気表記
-- 本当は年毎に微妙に日付が微妙に違うので
-- 年から算出するのが正しいけど今回は簡易的に固定値セット。
WHEN '立春' THEN
return int4range('[35, 50)');
WHEN '雨水' THEN
return int4range('[50, 65)'); -- TODO:うるう年の場合はどうする？
WHEN '啓蟄' THEN
return int4range('[65, 80)');
・・・
WHEN '師走' THEN
return int4range('[335, 365]');
ELSE
RAISE EXCEPTION '%s is not wareki domain!', w;
END CASE;
END;
$$ LANGUAGE plpgsql;つらつらと二十四節気、七十二候、月の表記をWHENで書いていく。なかなか単調で辛いコーディングですｗ
timestampとの比較・演算子定義
で、上記の関数を呼び出すtimestampとwarekiの比較関数を書く。
こんな感じ。
CREATE OR REPLACE FUNCTION include_wareki(ts timestamp, w wareki) RETURNS boolean AS $$
DECLARE
tsr INT4RANGE;
doy integer := EXTRACT(DOY FROM ts);
BEGIN
tsr = wareki_to_doys(w);
IF w = '冬至' THEN
-- 冬至のみの例外処理
return doy <@ '[1,6)'::int4range OR doy <@ '[356,366]'::int4range ;
ELSE
return tsr @> doy ;
END IF;
END;
$$ LANGUAGE plpgsql;この関数を使うオリジナルの比較演算子も定義する。
CREATE OPERATOR <=> ( LEFTARG = timestamp, RIGHTARG = wareki, PROCEDURE = include_wareki, COMMUTATOR = <=> );
これで大体できた。
実際にはうるう年かどうかの判定関数や、timestampから二十四節気/月表記への変換関数なども作る。
TODO
まだいくつかやり残したことがある。気が向いたらやるかも。
- timestampから七十二候への変換関数(手間がかかるからやってない)。
- warekiへのInterval操作。例えば、'立春'+1 → '雨水' など。
- wareki同士での比較演算と並べ替え。これが意外と面倒。1年で循環するので、例えば半年先を最も未来においた上で大小の判定が必要かも。
