Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

`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`) で切られているのでそれを使いたい

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

### ドキュメント

Why not register and get more from Qiita?
1. We will deliver articles that match you
By following users and tags, you can catch up information on technical fields that you are interested in as a whole
2. you can read useful information later efficiently
By "stocking" the articles you like, you can search right away