やりたいこと
- Athenaを使うにあたり、SQLで日付の比較をしたい
- 「先月以降のデータが欲しい」みたいなことをしたい
説明
※ 文中 base_date は date型
MySQLなどのノリで、日付をtimestamp型にして
base_date >='2019-05-01'
と言う感じにしても
'>=' cannot be applied to timestamp
と怒られます。
base_date>= CAST('2019-05-01' AS TIMESTAMP)
とかにすれば出来ます。
また、同じPrestoでもTreasure Dataの場合は日付の独自関数がありますが、Amazon Athenaには勿論そういったものは無いので、Presto 0.172 に沿って何とかして行く必要があります。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html
https://prestodb.github.io/docs/0.172/functions/datetime.html
先月以降のデータを取得
date型のカラムに対して、先月以降のデータを取得
select
base_date -- ★date型で持ってる
from tablename
where base_date >= date_trunc('month', current_timestamp)- interval '1' month
order by base_date
limit 10;
string型の日付(?)に対して、先月以降のデータを取得
select
base_date_string -- ★string型で持ってる
from tablename
where CAST(base_date_string AS TIMESTAMP) >= date_trunc('month', CAST(current_timestamp AS TIMESTAMP)- interval '1' month)
order by base_date_string
limit 10;
色々試したクエリ
文字列の日付を月に変換したり、今月先月を取得したり、それを文字列に戻したり。
select
base_date_string ,-- ★string型で持ってる
date_trunc('month', CAST(base_date_string AS TIMESTAMP)) as trunc_month,
date_trunc('month', current_timestamp) as thismonth,
date_trunc('month', current_timestamp)- interval '1' month as lastmonth,
substr( cast( date_trunc('month', CAST(current_timestamp AS TIMESTAMP)- interval '1' month ) as varchar) ,1,10) as "yyyy-mm-dd-string"
from tablename
order by base_date_string
limit 10
↓結果↓
ハマった点（追記）
応用的に「昨日までのデータ」が欲しいようなクエリを書いていて、何回「動作検証は上手く行くが、朝のバッチ処理経由で実行すると上手くデータが取れない」という事が起きていた。
ふと
select current_timezone()
_col0
1 UTC
解決
ようは日中検証をすると判定されるが、朝だとまだUTCで日が変わっていないので必ず1日ズレると・・・。
タイムゾーンの指定が出来るようなので
current_timestamp AT TIME ZONE 'Asia/Tokyo'
や
now() AT TIME ZONE 'Asia/Tokyo'
とすることで解決。