[pgsql-jp: 26322] Re: 記念日等の範囲検索

[WADA Hiroyuki]

和田です。

Hiroshi Ishiura wrote:
> ネックになるのがやはり年情報です。
> そう考えると、次いつ誕生日がくるかという判別をもたせる意味あいでphpで処
> 理をさせてcronでＤＢ更新をする（３）の方法がベストなのでしょうか＾＾

記念日というのが誕生日なら、月日だけでなくdate型にしておいた方がいい
でしょう。年のない記念日でも0000年とか1900年とか適当な値にしておけば
よくて、範囲を指定する際にもdate型にすれば、「開始日の前日の年齢と
終了日の年齢が異なるもの」という検索でできます。
ということで、

> >  加藤＠川崎です。

> > (3)memorial_dayに年情報を加える

がいいと思います。

> > (3)memorial_dayに年情報を加える
> >    (2)とは memorial_dayの型を初めから date 型(timestamp)にしておくとこ
> >    ろが異なります。くわえて、cronなどで日に1度(例えば0時に)現在よりも
> >    古い日の年情報を更新してしまいます。

> >    注意点としては、「誕生日」は更新しても「生年月日」は更新してはいけ
> >    ない、、、そりゃそうですよね。誕生日は毎年やってきますが、生年月日
> >    は一生で一度ですから。このようにカラムの入力値に依存する関係を忘れ
> >    て無闇に更新するとDBの意味がなくなったりします。

年齢を求める関数を作っておけば、tableに入れるのは「生年月日」でいい
ので、毎日更新する必要はないです。たとえば

CREATE FUNCTION nenrei(date, date) RETURNS int AS'
SELECT cast(extract(YEAR FROM $2) - extract(YEAR FROM $1)
 - CASE WHEN extract(MONTH FROM $2) < extract(MONTH FROM $1) OR
 extract(MONTH FROM $2) = extract(MONTH FROM $1) AND
 extract(DAY FROM $2) < extract(DAY FROM $1) THEN 1 ELSE 0 END
 AS int);
' LANGUAGE 'SQL';

で、$s_dayが範囲の開始１日前で$e_dayが範囲の終りの日とすると
select * from "user" where
  nenrei(memorial_day, $s_day) <> nenrei(memorial_day, $e_day);