AWS JAWS 登壇の記事から、数ヶ月ぶりの更新となります。
本日は株式会社オズビジョン、ハピタス事業部、開発ユニットマネージャーの寺嶋から「Amazon Athena SQL で事故が起きた話」と題してお送りします。
※なお、Amazon Athena に非は一切ありません。
発生したこと
さすがに実例は使いませんが「2024年1月に会員登録し、とある条件を満たした会員リストを取得したところ、想定と違う」ということが発生しました。
SELECT user_id
FROM behavior_log
WHERE registered_at AT TIME ZONE 'Asia/Tokyo'
BETWEEN CAST('2024-01-01 00:00:00' AS TIMESTAMP)
AND CAST('2024-01-31 23:59:59' AS TIMESTAMP)
AND condition_a = true
このクエリのどこに問題があるかわかるでしょうか？
registered_at は TIMESTAMP 型、condition_a は BOOLEAN 型です。
動くクエリ
以下のようにすると正しい結果を得ることができました。
SELECT user_id
FROM behavior_log
WHERE registered_at + INTERVAL '9' HOUR
BETWEEN CAST('2024-01-01 00:00:00' AS TIMESTAMP)
AND CAST('2024-01-31 23:59:59' AS TIMESTAMP)
AND condition_a = true
原因
TIMESTAMP 型を AT TIME ZONE で日本時間に合わせても、それはあくまで表示だけの話であり、内部的には UTC 時間で評価されているということがわかりました。
これは以下の結果でも確認することができます。
SELECT
CAST('2024-01-01 18:00:00' AS TIMESTAMP),
CAST('2024-01-01 18:00:00' AS TIMESTAMP) AT TIME ZONE 'Asia/Tokyo',
CAST('2024-01-01 18:00:00' AS TIMESTAMP) + INTERVAL '9' HOUR,
CAST('2024-01-01 18:00:00' AS TIMESTAMP) = CAST('2024-01-01 18:00:00' AS TIMESTAMP) AT TIME ZONE 'Asia/Tokyo',
CAST('2024-01-01 18:00:00' AS TIMESTAMP) = CAST('2024-01-01 18:00:00' AS TIMESTAMP) + INTERVAL '9' HOUR
なお、DATE 関数は見た目を処理しているようで、SELECT DATE(CAST('2024-01-01 18:00:00' AS TIMESTAMP) AT TIME ZONE 'Asia/Tokyo') の結果は 2024-01-02 となります。
学び
ハピタスのバックエンドの MySQL はタイムゾーンが歴史的な背景により UTC になっています。（怖くて直せない）
そのため、datetime ではなく timestamp で定義されているカラムについては、デイリーエクスポートを athena でクエリする際にタイムゾーンの考慮が必要となります。
この際、可読性が高いタイムゾーン構文をよく利用していましたが、他の日付カラムではなく、テキスト記載の日付との比較の際には INTERVAL で強制的に値を変更する必要がありそうです。
最後に
MySQL の全日付カラムを datetime にできる日を夢見ながら、今日も抽出に邁進したいと思います。
次の機会があればぜひお会いしましょう！
ここまで読んでいただき、ありがとうございました。
Discussion