こんにちは。データサイエンティスト兼、データ基盤エンジニアのshobyです。
RedShiftの日時処理、紛らわしいですよね。 今回は、RedShiftで日時処理をする際の注意点をご紹介します。
概要
- 現在日時取得の注意点
- 曜日取得の注意点
- 日時間隔取得の注意点
現在日時取得の注意点
現在日時を取得する際は、紛らわしい関数名と、タイムゾーンに注意する必要があります。
まず、現在の日時をDATE型で取得するのがCURRENT_DATE、 TIMESTAMP型で取得するのがGETDATEです。
上記関数で取得できる現在時刻は、UTCであるため、JSTで日時を格納しているカラムと比較するには、以下のようにCONVERT_TIMEZONEでJSTに変換する必要があります。
SELECT CONVERT_TIMEZONE('JST', CURRENT_DATE());
曜日取得の注意点
TIMESTAMPから曜日を取得する場合、シンタックスの異なる同等の関数二つが存在することに注意が必要です。 また、それらの出力結果はISO 8601の曜日の仕様とは異なる点にも注意が必要です。
まず、TIMESTAMPから日付部分を取得するには、EXTRACTと、DATE_PARTという二つの関数が存在します。
機能は同等で、シンタックスのみが異なります。*1 使用方法は以下の通りです。
SELECT EXTRACT(DAYOFWEEK FROM created_at)
SELECT DATE_PART(DAYOFWEEK, created_at)
また、これらの抽出結果は、ISO 8601の曜日の仕様である、月曜日が 1、日曜日は 7という定義とは異なり、0–6 の整数 (0 は日曜日) という定義になっています。
つまり、日曜日を0に割り当てるか、7に割り当てるかが異なっています。 ISO互換に値を変更する場合には、CASE文等で変換する必要があります。
日時間隔取得の注意点
RedShiftで日時間隔の取得を行う場合、日時の減算を用いる方法と、DATEDIFFを用いる方法があります。 どの型に対してどの方法を用いるかによって、結果の値が変わるため、注意が必要です。
日付の減算
SELECT '2018-09-03'::DATE - '2018-09-01'::DATE; -- 2（BIGINT） SELECT '2018-09-03 00:00:00'::TIMESTAMP - '2018-09-01 00:00:00'::TIMESTAMP; -- 2 days（INTERVAL）
DATEDIFF関数
SELECT DATEDIFF(DAY, '2018-09-01'::DATE, '2018-09-03'::DATE); -- 2（BIGINT） SELECT DATEDIFF(DAY, '2018-09-01 00:00:00'::TIMESTAMP, '2018-09-03 00:00:00'::TIMESTAMP); -- 2（BIGINT）
以上のように、DATE型同士であればどちらの方法を用いても結果がBIGINTで返ってきますが、 TIMESTAMP型の場合は結果がINTERVAL型になります。
日時間隔の取得結果に対し「結果が2日以上か」といった比較をする場合には注意が必要です。
まとめ
RedShiftで日時処理を行う際には、タイムゾーンや、ISOの仕様とのズレ、型の違いなどを意識する必要があるため、ご注意ください。
*1:EXTRACTの方が標準SQLの関数で、DATE_PARTの方がPostgresの前身となるIngressから引き継がれたtraditionalな関数なようです https://www.postgresql.org/docs/current/static/functions-datetime.html
