12
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated at

Organization

Amazon Athena ( Presto 0.172 ) で日付の比較をして先月以降のデータを取得する

やりたいこと

  • 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

↓結果↓

qiita_20190522.png

ハマった点(追記)

応用的に「昨日までのデータ」が欲しいようなクエリを書いていて、何回「動作検証は上手く行くが、朝のバッチ処理経由で実行すると上手くデータが取れない」という事が起きていた。

ふと

select current_timezone()

    _col0
1   UTC

:innocent:

解決

ようは日中検証をすると判定されるが、朝だとまだUTCで日が変わっていないので必ず1日ズレると・・・。

タイムゾーンの指定が出来るようなので

current_timestamp AT TIME ZONE 'Asia/Tokyo'

now() AT TIME ZONE 'Asia/Tokyo'
とすることで解決。

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
12
Help us understand the problem. What are the problem?