はじめに

AWS AthenaはPresto SQLに準拠しているため数々の時刻関数を使用することができます。

今回は私がよく使うものを紹介していきたいと思います。

タイムゾーンの設定

SELECT timestamp '2020-11-20 01:00 UTC' AT TIME ZONE 'Asia/Tokyo'

> 2020-11-20 10:00:00.000 Asia/Tokyo

2020/11/20 6:40 (JST)に実行した結果になります。

SELECT current_timestamp

> 2020-11-19 21:40:27.184 UTC

タイムゾーンを指定して現在時刻を取得する

SELECT current_timestamp AT TIME ZONE 'Asia/Tokyo'

> 2020-11-20 06:40:36.182 Asia/Tokyo

SELECT date_parse('2020-11-20 16', '%Y-%m-%d %H')

> 2020-11-20 16:00:00.000

※よく使うものを抜粋

SELECT date_format(timestamp '2020-11-20 01:00 UTC', '%Y/%m/%d')

> 2020/11/20

フォーマットについては文字列からタイムスタンプ型へ変換する際のフォーマットと同じになります。

intervalを用いて加算

SELECT timestamp '2020-11-20 01:00' + interval '1' month

> 2020-11-20 16:00:00.778

intervalを用いて減算

SELECT timestamp '2020-11-20 01:00' - interval '3' hour

> 2020-11-19 22:00:00.000

date_add を用いて演算

SELECT date_add('hour', 3, timestamp '2020-11-20 01:00 UTC')

> 2020-11-20 04:00:00.000 UTC

date_diff を用いてタイムスタンプの差分を計算する

出力は指定した単位となる

SELECT date_diff('day', timestamp '2020-11-17 20:00 UTC', timestamp '2020-11-20 01:00 UTC')

> 2

date_add 、date_diff で使える単位はこちら

SELECT from_unixtime(1605793223)

> 2020-11-19 13:40:23.000

タイムゾーンを指定する場合

SELECT from_unixtime(1605793223, 'Asia/Tokyo')

> 2020-11-19 22:40:23.000 Asia/Tokyo

SELECT to_unixtime(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 1.605793223E9

年を抜き取る場合

SELECT year(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 2020

月を抜き取る場合

SELECT month(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 11

日を抜き取る場合

SELECT day_of_month(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 19

曜日を抜き取る場合

SELECT day_of_week(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 4

※ 1:月曜日、2:火曜日、3:水曜日、4:木曜日、5:金曜日、6:土曜日、7:日曜日

時間を抜き取る場合

SELECT hour(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 22

分を抜き取る場合

SELECT minute(timestamp '2020-11-19 22:04:23 Asia/Tokyo')

> 4

秒を抜き取る場合

SELECT second(timestamp '2020-11-19 22:40:23 Asia/Tokyo')

> 23

date_trunc関数を用います。

引数はdate_trunc(単位, timestamp)です。

単位	関数	出力
second	`date_trunc('second', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-22 22:40:23.000 Asia/Tokyo
minute	`date_trunc('minute', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-22 22:40:00.000 Asia/Tokyo
hour	`date_trunc('hour', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-22 22:00:00.000 Asia/Tokyo
day	`date_trunc('day', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-22 00:00:00.000 Asia/Tokyo
week	`date_trunc('week', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-16 00:00:00.000 Asia/Tokyo
month	`date_trunc('month', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-11-01 00:00:00.000 Asia/Tokyo
quarter	`date_trunc('quarter', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-10-01 00:00:00.000 Asia/Tokyo
year	`date_trunc('year', timestamp '2020-11-22 22:40:23.34 Asia/Tokyo')`	2020-01-01 00:00:00.000 Asia/Tokyo

AthenaのデフォルトのタイムゾーンはUTCであることには注意が必要です。

この記事が開発や分析のご助力となれば幸いです。