DB内に誕生日をint(timestamp)で保存している場合に、年齢で検索をしたい的なお話

なんのこっちゃと思うようなタイトルだけど、
DBにbirthdayという形でユーザーの誕生日を保存しているという前提でのお話。
ユーザーの検索をする際に、年齢での検索をしたいということがある。
もちろんDBにageみたいな形でユーザーの年齢を保存するフィールドを用意すればいいんだけど、
いちいち毎日cronを回して更新する必要が出て来て面倒だしそんなのにindexを貼りたくはないわけで。

というわけで今日はint型(timestamp)で誕生日を保存している際に、年齢検索をするには的なお話をば。

■何歳から何歳までという条件をtimestampで計算する

// 20歳から22歳までのユーザーを算出したい
SET @age_from:=20;
SET @age_to:=22;

// 何歳からのタイムスタンプを計算
// 今日が2015/06/13の場合、1995/06/13のタイムスタンプが算出される
SET @where_age_from:=unix_timestamp(
  date_format(now(),'%Y%m%d') - (@age_from * 10000)
);

// 何歳までのタイムスタンプを計算
// 今日が2015/06/13の場合、1992/06/14のタイムスタンプが算出される
SET @where_age_to:=unix_timestamp(
  date_format(now(),'%Y%m%d') - ((@age_to + 1) * 10000) + 1
);

SELECT *
FROM user
WHERE birthday <= @where_age_from AND birthday >= @where_age_to;

こんな感じで何歳から何歳までのタイムスタンプを計算してあげれば大丈夫。

なお何歳までのタイムスタンプ計算が実は厄介で、下記のようにやると間違い。

// 今日が2015/06/13の場合、1993/06/13のタイムスタンプが算出されてしまって間違い
SET @where_age_to:=unix_timestamp(
  date_format(now(),'%Y%m%d') - (@age_to * 10000)
);

これだと1992/06/14〜1993/06/13の間が計算されなくなってしまう。
だから@age_to + 1歳になる前日の日を求めて計算すればよい。
つまり22歳までのものを求めたかったら23歳になる前日の日までのタイムスタンプを算出するということになる。

■タイムスタンプからの年齢計算

ちなみにタイムスタンプからの年齢計算の基本的なやり方は下記になる。

// 1993/06/13のタイムスタンプ
SET @timestamp:=739929600;

// 22歳が出てくる
SELECT
(date_format(now(),'%Y%m%d') - from_unixtime(@timestamp,'%Y%m%d')) / 10000 as age;

ということで(現在の日付(Ymd) - とある日付(Ymd)) / 10000をすれば年齢が計算出来る。


年齢でユーザーを絞り込みたいとかそういう機能って実はなかなかにあるものなんだけど、
年齢っていうフィールドを増やしたらそれを毎日更新しないといけないだとかでかなり面倒だったりするけど、
timestampで保存してあればこういう形ですんなり計算出来てかなり楽だったりする。
調べても情報が出てこなかったけど、みんなどういう風に年齢計算をしているんだろうかとちょっと気になったなぁ的な。