AWS - Athenaで先週分がJSTで取れるビューを作る

良いやり方を教えていただきました

• https://qiita.com/sakamossan/items/adc8668038226a65436b#comment-bbfef1ec7866ff507f3a

---

week_of_year で取りたいならもう少し楽だけど、昨日からの直近7日間が欲しい
たとえば日本時間の8月4日にクエリしたら (2018-07-28 < 2018-08-03) の範囲が欲しい

• テーブルのパーティションが (year, month, day) で切られているのでそれを使いたい
• ミリ秒単位のタイムスタンプが入っているカラム(timestamp)がある

CREATE VIEW v AS
SELECT
    t.*,
    from_unixtime(t.timestamp / 1000) utc_timestamp
FROM t
WHERE
    (
        (
            year = year(current_date - interval '1' day) AND
            month = month(current_date - interval '1' day) AND
            day = day(current_date - interval '1' day)
        ) OR (
            year = year(current_date - interval '2' day) AND
            month = month(current_date - interval '2' day) AND
            day = day(current_date - interval '2' day)
        ) OR (
            year = year(current_date - interval '3' day) AND
            month = month(current_date - interval '3' day) AND
            day = day(current_date - interval '3' day)
        ) OR (
            year = year(current_date - interval '4' day) AND
            month = month(current_date - interval '4' day) AND
            day = day(current_date - interval '4' day)
        ) OR (
            year = year(current_date - interval '5' day) AND
            month = month(current_date - interval '5' day) AND
            day = day(current_date - interval '5' day)
        ) OR (
            year = year(current_date - interval '6' day) AND
            month = month(current_date - interval '6' day) AND
            day = day(current_date - interval '6' day)
        ) OR (
            year = year(current_date - interval '7' day) AND
            month = month(current_date - interval '7' day) AND
            day = day(current_date - interval '7' day)
        ) OR (
            year = year(current_date - interval '8' day) AND
            month = month(current_date - interval '8' day) AND
            day = day(current_date - interval '8' day)
        )
    )
    AND date_trunc('day', current_timestamp AT TIME ZONE 'Asia/Tokyo')
        > from_unixtime(t.timestamp / 1000) AT TIME ZONE 'Asia/Tokyo'
    AND from_unixtime(t.timestamp / 1000) AT TIME ZONE 'Asia/Tokyo'
        >= date_trunc('day', current_timestamp AT TIME ZONE 'Asia/Tokyo' - interval '7' day)

もう少し簡潔に書けるような感じもするけど、パーティション使おうとすると難しい

テーブルのパーティションが (year, month, day) で切られているのでそれを使いたい

が、良いやり方あれば教えてください 🙏

ドキュメント

• 6.10. Date and Time Functions and Operators — Presto 0.172 Documentation
  • 2018 年 1 月 19 日 - Amazon Athena