Amazon Athena で Application Load Balancer (ALB) のアクセスログを検索するときにタイムスタンプ time のタイムゾーンは UTC になっている💡検索するときに JST に変換したい❗️と思う場面があって，最近クエリを作っていたため，今後すぐに再利用できるようにクエリサンプルをメモしておこうと思う＼( 'ω')／

サンプル: `SELECT`

単純に time を JST で表示するなら parse_datetime 関数と AT TIME ZONE オペレータを組み合わせる👌

SELECT
    parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' AS time,
    request_verb,
    request_url,
    elb_status_code
FROM alb_logs
WHERE
    day = '2023/09/01'
LIMIT 100

記述量はほぼ同じだけど AT TIME ZONE オペレータではなく parse_datetime 関数と at_timezone 関数を組み合わせることもできる👌

SELECT
    at_timezone(parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z'), 'Asia/Tokyo') AS time,
    request_verb,
    request_url,
    elb_status_code
FROM alb_logs
WHERE
    day = '2023/09/01'
LIMIT 100

サンプル: `WHERE`

time を JST で絞り込む場合は少し冗長だけど parse_datetime 関数と timestamp 型を組み合わせる👌

SELECT
    parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' AS time,
    request_verb,
    request_url,
    elb_status_code
FROM alb_logs
WHERE
    day = '2023/09/01' AND
    parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') < timestamp '2023-09-01 01:00:00 Asia/Tokyo'
LIMIT 100

もちろん BETWEEN を使って範囲検索もできる👌

SELECT
    parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') AT TIME ZONE 'Asia/Tokyo' AS time,
    request_verb,
    request_url,
    elb_status_code
FROM alb_logs
WHERE
    day = '2023/09/01' AND
    parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
        BETWEEN timestamp '2023-09-01 10:00:00 Asia/Tokyo' AND timestamp '2023-09-01 10:59:59 Asia/Tokyo'
LIMIT 100