timestamp型で困ったこと
2020-04-21 04:51:25 UTC
2020-04-26 16:01:22 UTC
といった時刻表記をAthenaでjstの時刻でtimestamp型に変換する際に調べたことを記録しておく。
上記表記はUTCという余計な文字列が入っていたりとそのままではtimestamp型に変換できないのでいくらか変換の手順を踏んだ。
今回はカラム名をtime_columnとする
結論
cast(substr(time, 1, 20) as timestamp) + interval '9' hour as time
これで変換できた
変換手順
文字列削除
まず、UTCといった文字列を削除するためにsubstr関数で文字列を消去する。
substr(time_column, 1, 19)
キャスト
余計な文字列の削除ができたのでtimestamp型にキャストする
cast(substr(time, 1, 20) as timestamp)
utcからjstに変換
- intervalで時間の加減算が可能となる。今回はhourだがdayなども可能。
参考
cast(substr(time, 1, 20) as timestamp) + interval '9' hour
timestamp型を指定すると値が空白になってしまう
今回の元データがもし
2020-04-21 04:51:25
2020-04-26 16:01:22
だったとしても元からtimestamp型を選択したとしても、エラーはでないが値がとれない（空白となる）。
これはYYYY-MM-DD HH:MM:SS.fffffffffという形式でないといけないかららしい。
AWSのドキュメントにもその対応がある
Amazon Athena のテーブルにクエリを実行すると、TIMESTAMP の結果が空になる
リンク先の解決でも良いが、自分は今回parquetにも変換したかったのでcreate table asで対応した
AthenaでCSVファイルからParquet形式へ変換
こんなドキュメント用意するくらいならYYYY-MM-DD HH:MM:SSに対応してくれないのかな…とはちょっと思う（キャストはうまくいくわけだし）。