Amazon Redshift: システム日付と誕生日データからタイムゾーンを考慮した現在の年齢を求める

Amazon Redshiftで良く使いそうな便利系SQL

#ビッグデータ

#Amazon Redshift

#AWS

しんや

2015.11.19

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

超々小ネタです。

やりたい事の内容はタイトルで示しているものが全てであり、調べた過程で当ブログ内に関連エントリがあったので大体問題は解決したのですが、そこにもう一手間加える必要が要件としてあったので別エントリとして投稿してみたいと思います。

PostgreSQLにおける年齢計算

Amazon Redshiftが準拠しているPostgreSQLにおいては、AGE関数を使ってその情報を算出する事が出来ます。下記エントリではそのものズバリな出し方を行っており、まさに『これでエエやん』な感じですね。

PostgreSQLで数え年計算 | Fusic Developers Weblog

Amazon Redshiftにおける当該テーマのサポート状況

がしかし、Amazon RedshiftではAGE関数は廃止されており、使えないようです。

AGE 関数 - Amazon Redshift

代わりに、DATEDIFF関数を使うようにとの記述がありました。

DATEDIFF 関数 - Amazon Redshift

検証用に簡単なテーブルを作ってみました。ここでのbirth_jstの値は、『日本国内でのタイムゾーンにおける誕生日』が設定されているという想定です。

# CREATE TABLE public.user_birth (
    id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    birth_jst DATE NOT NULL
  );
#
# SELECT * FROM public.user_birth ORDER BY id;
 id | name | birth_jst  
----+------+------------
  1 | AAA  | 1985-11-19
  2 | BBB  | 1985-11-20
(2 rows)

まずはここに、計算用に必要な『現時点におけるシステム日付』を追加してみます。単純なシステム日付であればSYSDATEを使えばそのまま出てきますが、こちらの情報はデフォルトではタイムゾーン：UTCでの時刻であり、日本国内におけるタイムゾーンでの情報ではありません。

# SELECT id, name, birth_jst, SYSDATE FROM public.user_birth ORDER BY id;
 id | name | birth_jst  |         timestamp          
----+------+------------+----------------------------
  1 | AAA  | 1985-11-19 | 2015-11-19 06:17:23.317917
  2 | BBB  | 1985-11-20 | 2015-11-19 06:17:23.317917
(2 rows)

なので、ここはCONVERT_TIMEZONE関数を使い、システム日付(時刻)を日本国内のタイムゾーン('Asia/Tokyo')に置き換えておく必要があるかと思います。

CONVERT_TIMEZONE 関数 - Amazon Redshift

# SELECT
    id,
    name,
    birth_jst,
    SYSDATE as timestamp_utc,
    CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE) AS timestamp_jst
  FROM
    public.user_birth ORDER BY id;
    
 id | name | birth_jst  |       timestamp_utc        |       timestamp_jst        
----+------+------------+----------------------------+----------------------------
  1 | AAA  | 1985-11-19 | 2015-11-19 06:22:38.037574 | 2015-11-19 15:22:38.037574
  2 | BBB  | 1985-11-20 | 2015-11-19 06:22:38.037574 | 2015-11-19 15:22:38.037574
(2 rows)

DATEDIFF関数を使って年齢を抽出してみます。....がしかし、挙動がおかしいようです。id=1の人については30歳で問題ありませんが、id=2の人はまだ誕生日を迎えていないので29歳でなければなりません。

# SELECT
    birthdaydata.id,
    birthdaydata.name,
    birthdaydata.birth_jst,
    birthdaydata.sysdate_jst,
    DATEDIFF(year, birthdaydata.birth_jst, birthdaydata.sysdate_jst)
  FROM
    (SELECT
       id,
       name,
       birth_jst,
       CAST(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE) AS DATE) AS sysdate_jst
     FROM
       public.user_birth ORDER BY id) birthdaydata;
       
 id | name | birth_jst  | sysdate_jst | date_diff 
----+------+------------+-------------+-----------
  1 | AAA  | 1985-11-19 | 2015-11-19  |        30
  2 | BBB  | 1985-11-20 | 2015-11-19  |        30
(2 rows)

DATEDIFF関数のdatapartの部分の解説を読んでみます。どうやらこの部分の仕様が認識とズレているようですね。

DATEDIFF は 2 つの式の間で越える日付部分の境界の数を決定します。例えば、2 つの日付（12-31-2008 と 01-01-2009）の間で年数の差を計算する場合、実際のこれらの日付には 1 日の違いしかありませんが、関数は 1 年を返します。2 つのタイムスタンプ（01-01-2009 8:30:00 と 01-01-2009 10:00:00）の間で時間の差が分かっている場合、結果は 2 時間になります。

タイムゾーンを踏まえた誕生日の算出方法 in Amazon Redshift

解法は近いところにありました。まさに灯台下暗し。こちらに記載されているSQLでは上記で問題となっている箇所が解決されています。

[Redshift] 誕生日から年齢を計算する｜ Developers.IO

上記エントリでの内容を踏まえつつ、更にはタイムゾーンを考慮した(今回の場合では『日本国内に於ける』)現在の年齢を求めるSQLの例が以下となります。参考用にタイムスタンプ型の情報も幾つか追加しています。

# SELECT
    DATEDIFF(year, birthdaydata.birth_jst, birthdaydata.sysdate_jst)
    +
    (CASE WHEN DATE_PART(doy, birthdaydata.birth_jst) > DATE_PART(doy, birthdaydata.sysdate_jst)
       THEN - 1
       ELSE 0
    END) AS real_age,
    birthdaydata.id,
    birthdaydata.name,
    birthdaydata.birth_jst,
    birthdaydata.systimestmp_utc,
    birthdaydata.systimestmp_jst,
    birthdaydata.sysdate_jst
  FROM
    (SELECT
      id,
      name,
      birth_jst,
      SYSDATE as systimestmp_utc,
      CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE) AS systimestmp_jst,
      CAST(CONVERT_TIMEZONE('Asia/Tokyo', SYSDATE) AS DATE) AS sysdate_jst
    FROM
      public.user_birth ORDER BY id
    ) birthdaydata;
    
 real_age | id | name | birth_jst  |      systimestmp_utc      |      systimestmp_jst      | sysdate_jst 
----------+----+------+------------+---------------------------+---------------------------+-------------
       30 |  1 | AAA  | 1985-11-19 | 2015-11-19 07:02:31.08206 | 2015-11-19 16:02:31.08206 | 2015-11-19
       29 |  2 | BBB  | 1985-11-20 | 2015-11-19 07:02:31.08206 | 2015-11-19 16:02:31.08206 | 2015-11-19
(2 rows)