PostgreSQL　9.2のRangeを使った二十四節気の擬似型

日本に住んでいるなら二十四節気というのは馴染みがあるはず。
ならば、二十四節気を使った日時の範囲検索とか面白そうじゃね？と思ったのでPostgreSQL 9.2-betaに入っていたRange型を使って、簡単に作ってみた。
参考：PostgreSQL: Documentation: 9.2: Range Types

作り方

w24という型をtextのドメインとして作成する。
- w24型は二十四節気の表記以外を受け付けないTEXT型として指定する。
timestampとw24型を引数として、timestampが節気に含まれるか否かのboolean関数を作成する。
- この中でint4rangeとintの包含比較演算子 @> を使う。
- timestampとw24の比較演算子として <=> という演算子を定義する。

実験

まず、w24型、関数、演算子を登録する。

$ psql test -e -f w24.sql
CREATE DOMAIN w24 AS text
CONSTRAINT w24_check NOT NULL
CHECK (
VALUE ~ '(立春|雨水|啓蟄|春分|清明|穀雨|立夏|小満|芒種|夏至|小暑|大暑|立秋|処暑|白露|秋分|寒露|霜降|立冬|小雪|大雪|冬至|小寒|大寒)'
);
CREATE DOMAIN
CREATE OR REPLACE FUNCTION include_weather(ts timestamp, w w24) RETURNS  boolean AS $$
DECLARE
  tsr INT4RANGE;
  doy integer := EXTRACT(DOY FROM ts);
BEGIN
  CASE w
    WHEN '立春' THEN
      -- tsrに立春の開始日と終了日（正確には次の節気の開始未満）をセット。
      -- 本当は年毎に微妙に日付が微妙に違うので
      -- 年から算出するのが正しいけど今回は簡易的に固定値セット。
      tsr := int4range('[35, 50)');
    WHEN '雨水' THEN
      tsr := int4range('[50, 66]');
    -- 以下、全部の節気を本当は作らないといけないけど割愛。
    ELSE
      RAISE EXCEPTION '%s is not support!', w24;
  END CASE;
  return tsr @> doy ;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE OPERATOR <=> (
  LEFTARG = timestamp,
   RIGHTARG = w24,
  PROCEDURE = include_weather,
  COMMUTATOR = <=>
);
CREATE OPERATOR
$

timestamp型を含むテーブルを作って、そのテーブルに対して指定した w24型の範囲に含まれるレコードを検索してみる。

$ psql test -e -f w24_test.sql
CREATE TEMP TABLE foo (id integer, data text, ts timestamp);
CREATE TABLE
INSERT INTO foo VALUES
  (1, 'aaa', '2012-01-01'),
  (2, 'bbb', '2012-02-01'),
  (3, 'ccc', '2012-02-10'),
  (4, 'ddd', '2012-03-01') ;
INSERT 0 4
SELECT * FROM foo;
 id | data |         ts
----+------+---------------------
  1 | aaa  | 2012-01-01 00:00:00
  2 | bbb  | 2012-02-01 00:00:00
  3 | ccc  | 2012-02-10 00:00:00
  4 | ddd  | 2012-03-01 00:00:00
(4 rows)

SELECT * FROM foo WHERE ts <=> '立春'::w24;
 id | data |         ts
----+------+---------------------
  3 | ccc  | 2012-02-10 00:00:00
(1 row)

$

出来た。一応成功。
とはいえ、この程度のことはRange型でなければ出来ないというわけでもない(int型を2つ定義すれば十分ではある）。
まだまだ、Range型の真の力を使いこなすには勉強が足りぬ。