[Redshift] 誕生日から年齢を計算する（閏年対応版）

はじめに

Redshiftで使うSQLを作成していて年齢計算や会社の期などを計算する必要があったのでその方法を調べました。 RedshiftではAge関数が使えない(リーダーノード専用関数)のでDATEDIFFとDATE_PART(doy)を使う方法が当エントリにあったのでこちらを利用してみました。

• [Redshift] 誕生日から年齢を計算する

改善前

SELECT
user_name,
birthday
FROM public.users;

user_name | birthday
---------+-----------
takashi | 1990-08-06
izumi | 1990-08-07
hanako | 1990-08-08
ken | 1992-08-06
yui | 1992-08-07
ai | 1992-08-08

これらのデータを用いて計算したところ

SELECT
user_name,
birthday,
DATEDIFF (YEAR, birthday, GETDATE ()) + (CASE WHEN DATE_PART (doy, birthday) > DATE_PART (doy, GETDATE ()) THEN - 1 ELSE 0 END) as age,
TO_CHAR(getdate(),'YYYY-MM-DD') as today
FROM public.users;

user_name | birthday | age | today
---------+-----------+-----------+-----------
takashi | 1990-08-06 | 29 | 2019-08-07
izumi | 1990-08-07 | 29 | 2019-08-07
hanako | 1990-08-08 | 28 | 2019-08-07
ken | 1992-08-06 | 27 | 2019-08-07
yui | 1992-08-07 | 26 | 2019-08-07
ai | 1992-08-08 | 26 | 2019-08-07

一見正しく計算できているようですが、計算日が８月７日なのでyuiの年齢が正しく計算できていないようです。 これは以下のSQLで原因がわかります。

SELECT
user_name,
birthday,
TO_CHAR(getdate(),'YYYY-MM-DD') as today,
DATE_PART (doy, birthday) as doy_of_birthyear,
DATE_PART (doy, GETDATE ()) as doy_of_thisyear

FROM public.users;

user_name | birthday | age | today | doy_of_birthyear | doy_of_birthyear
---------+-----------+-----------+-----------+-----------+-----------
takashi | 1990-08-06 | 29 | 2019-08-07 | 218 | 219
izumi | 1990-08-07 | 29 | 2019-08-07 | 219 | 219
hanako | 1990-08-08 | 28 | 2019-08-07 | 220 | 219
ken | 1992-08-06 | 27 | 2019-08-07 | 219 | 219
yui | 1992-08-07 | 26 | 2019-08-07 | 220 | 219
ai | 1992-08-08 | 26 | 2019-08-07 | 221 | 219

doy(day of year)は年間積算日を出してくれますが、1992年は閏年なので一日ずれてしまっているのです。 従ってこの方法ですと誕生日もしくは年齢を求める年が閏年の場合で誕生日当日に計算すると正しく年齢が計算されません。

解決方法

単純に月日の比較で対応します。

TO_CHAR(birthday, 'MMDD') > TO_CHAR(getdate(), 'MMDD')

で月日を抽出してその比較で誕生日を迎えたかどうかを判断します。

SELECT
user_name,
birthday,
DATEDIFF (YEAR, birthday, GETDATE ()) + (CASE WHEN DATE_PART (doy, birthday) > DATE_PART (doy, GETDATE ()) THEN - 1 ELSE 0 END) as age,
DATEDIFF(YEAR, birthday, getdate()) +
(CASE WHEN TO_CHAR(birthday, 'MMDD') > TO_CHAR(getdate(), 'MMDD') THEN - 1 ELSE 0 END) as aeg_2,
TO_CHAR(getdate(),'YYYY-MM-DD') as today
FROM public.users;

user_name | birthday | age | age_2 | today
---------+-----------+-----------+-----------
takashi | 1990-08-06 | 29 | 29 | 2019-08-07
izumi | 1990-08-07 | 29 | 29 | 2019-08-07
hanako | 1990-08-08 | 28 | 28 | 2019-08-07
ken | 1992-08-06 | 27 | 27 | 2019-08-07
yui | 1992-08-07 | 26 | 27 | 2019-08-07
ai | 1992-08-08 | 26 | 26 | 2019-08-07

これで閏年でも問題なく日付が計算できるようになりました。

まとめ

非常に細かい内容でしたが厳密に年齢を計算するには必要なことだと思いますし、これをベースに会社の期を求める際にもたった一日とはいえバグの温床になるので気をつけたいです。

最後まで読んで頂いてありがとうございました。