[小ネタ]AthenaでALBアクセスログ のtimestampのタイムゾーンの変換をする

はじめに

ALBのアクセスログなどタイムスタンプのタイムゾーンがUTCになっている場合は多くあると思います。頭の中で読み替えるのはそれほど難しくないのですが、量が多かったり、クエリの結果を共有する場合はタイムゾーンの変換をしたい場合があります。調べてみると簡単にタイムゾーンの変更ができたので、そのメモです。

前提

今回はALBのアクセスログをAthenaでクエリする場合を例に考えます。タイムスタンプのフォーマットやタイムゾーンはユースケースによって異なると思いますので、適宜読み替えてください。

タイムスタンプのパース

ALBのログをAthenaでクエリする場合そのままではタイムスタンプ(timeカラム)は文字列型です。タイムゾーンの変換前にこれをTIMESTAMP型へパースする必要があります。

パースするには以下の通りparse_datetime関数を使います。

parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')

タイムゾーンの変更

タイムゾーンの変換にはAT TIME ZONEオペレータを使います。ALBログの場合は以下のようになります。

parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo'

リテラルで指定する場合は以下のようになります。

SELECT '2020-12-01 13:00 Asia/Tokyo';
-- 検索条件として使う例;
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
      BETWEEN timestamp '2020-12-01 12:00 Asia/Tokyo' 
      AND timestamp '2020-12-01 13:00 Asia/Tokyo'

まとめ

AthenaではAT TIME ZONE オペレーターでTIMESTAMP型値のタイムゾーンを変更できます。