【AWS Athena】JSONデータの時刻をtimestamp型で取り込む方法

結論から言うと、文字列（string）で取り込んでから、timestamp型に変更する方法である。

これ以外に手軽な方法があるなら教えてほすぃ

S3からAthenaへデータを取り込む記事はこちらでは割愛するが、以下の記事で紹介している。

上記の内容のCSVをJSON、timestampにしている部分をstringにすれば良い。

AthenaでJSONファイルから日時（文字列）を読み込んで、timestamp型に変更する方法

SELECT date_parse(date1, '%Y/%m/%d %H:%i:%s.%f') as dt,
data2, data3
FROM "csv_database_221209"."athena_csv_read_221209" limit10

こんな感じである。

データ名は適当に読み替えて欲しい。

SELECT date_parse(date1, '%Y/%m/%d %H:%i:%s.%f') as dt

肝はこの部分で、取り込むデータ形式にあわせて「%Y/%m/%d %H:%i:%s.%f」の部分を変えてあげる必要がある。

上記の場合は「2023/01/04 16:15:08.044」のようなデータを取り込む。

「23/01/04 16:15:08」なデータなら「%y/%m/%d %H:%i:%s」になる。

参考　AWS Athenaでのタイムスタンプの扱い方まとめ

注意点としては、data_parseを使うと「SELECT * FROM～」が使えなくなるので、全ての項目を列挙したい場合はいちいち全て書かなければいけない。

また、これはAthenaの仕様なのか、asで定義した別名（上記例だと「dt」）は使えないので注意しよう。

なので、whereなどで使う場合は、下記のようにいちいちdata_parseする必要がある。

SELECT date_parse(date1, '%Y/%m/%d %H:%i:%s.%f') as dt, 
date2, date3
FROM "csv_database_221209"."athena_csv_read_221209"
where date_parse(date, '%Y/%m/%d %H:%i:%s.%f') 
BETWEEN CAST('2023-01-04 00:00:00' as timestamp)
AND (CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo') + interval '9' hour
order by date desc

なんで「interval ‘9’ hour」しているん？　と思った人は、以下の記事を読んで欲しい。

本当に文字列型がtimestamp型になっているんだろうな？　と疑う方は以下のSQLをやってみよう。

SELECT data1,
        date_parse(data1,'%Y-%m-%dT%H:%i:%s.%f'),
         typeof(date_parse(data1,'%Y-%m-%dT%H:%i:%s.%f'))
FROM "csv_database_221209"."athena_csv_read_221209";

上から、文字列で読み込んだ日時、timestamp型に変更した日時、←の型が表示されtimestamp型と表示されるはずだ。