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

はじめに

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

フォーマット表

※よく使うものを抜粋

format	意味
%Y	4桁の年
%y	2桁の年
%m	月 [numeric 01~12]
%W	曜日名 [Sunday, Monday, ....]
%w	曜日 [numeric 0~6 0が日曜日]
%d	日 [01~31]
%e	日 [1~31]
%H	時間 [00~23]
%h	時間 [01~12]
%p	AM or PM
%i	分 [numeric 00~59]
%s	秒 [numeric 00~59]
%f	秒の小数部分(0~999999999)

タイムスタンプ型から文字列型へ変換する

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 で使える単位はこちら

• millisecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

unixtimeからタイムスタンプ型へ変換する

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

タイムスタンプ型からunixtimeへ変換する

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

TRUNC処理

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であることには注意が必要です。

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