Hive と Presto で ISO8601 な文字列を様々なフォーマットに変換する方法
Written by @ryysud Mar 12, 2018 21:00 · 494 words · 1 minute read
前提条件
以下のクエリで出てくる “2018-03-12T21:00:00+09:00” は利用する際にはカラムを指定して下さい。また、Hive と Presto のバージョン間の違いで各関数で返される値の型が異なる場合もありますので、ご利用の際は該当バージョンのドキュメントを確認して下さい。もちろん抽出後に CAST して型変換してもらうのでも大丈夫です。
Hive での変換方法（ Hive 2.3.2 ）
どんな形にするにも一度 unix_timestamp() で ISO8601 な文字列をパースして bigint の unix timestamp にすれば from_unixtime(bigint unixtime[, string format]) で自由なフォーマットが可能です。
1. 2018-03-12T21:00:00+09:00 (string) → 2018-03-12 (string) の変換
select from_unixtime(unix_timestamp('2018-03-12T21:00:00+09:00', "yyyy-MM-dd'T'HH:mm:ssX"), 'yyyy-MM-dd')
以下は間違えやすいパターンなので要注意
-- 以下クエリもフォーマットされているように見えますが unix timestamp に変換していないため単なる文字列の加工です
select to_date('2018-03-12T21:00:00+09:00') => 2018-03-12
select date_format('2018-03-12T21:00:00+09:00', 'yyyy-MM-dd') => 2018-03-12
-- timezone を変更してやるとわかりやすいかも（ 2018-03-12T21:00:00+06:00 と 2018-03-13T00:00:00+09:00 は同値 ）
select to_date('2018-03-12T21:00:00+06:00') => 2018-03-13 ではなく 2018-03-12 になってしまう
select date_format('2018-03-12T21:00:00+06:00', 'yyyy-MM-dd') => 2018-03-13 ではなく 2018-03-12 になってしまう
2. 2018-03-12T21:00:00+09:00 (string) → 2018-03-12 21:00:00 (string) の変換
select from_unixtime(unix_timestamp('2018-03-12T21:00:00+09:00', "yyyy-MM-dd'T'HH:mm:ssX"))
Presto での変換方法（ Presto 0.188 ）
Presto も Hive と同様に一度 unix timestamp に変換してからフォーマットする流れとなります。
1. 2018-03-12T21:00:00+09:00 (varchar) → 2018-03-12 (varchar) の変換
-- use Java Date Functions
select format_datetime(from_unixtime(to_unixtime(from_iso8601_timestamp('2018-03-12T21:00:00+09:00'))), 'yyyy-MM-dd')
-- use MySQL Date Functions
select date_format(from_unixtime(to_unixtime(from_iso8601_timestamp('2018-03-12T21:00:00+09:00'))), '%Y-%m-%d')
2. 2018-03-12T21:00:00+09:00 (varchar) → 2018-03-12 21:00:00 (varchar) の変換
-- use Java Date Functions
select format_datetime(from_unixtime(to_unixtime(from_iso8601_timestamp('2018-03-12T21:00:00+09:00'))), 'yyyy-MM-dd HH:mm:ss')
-- use MySQL Date Functions
select date_format(from_unixtime(to_unixtime(from_iso8601_timestamp('2018-03-12T21:00:00+09:00'))), '%Y-%m-%d %H:%i:%s')
3. 2018-03-12T21:00:00+09:00 (varchar) → 2018-03-12 21:00:00.000 (timestamp) の変換
select from_unixtime(to_unixtime(from_iso8601_timestamp('2018-03-12T21:00:00+09:00')))
