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

12214 ワード

はじめに

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

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

Author And Source

この問題について(AWS Athenaでのタイムスタンプの扱い方まとめ), 我々は、より多くの情報をここで見つけました https://qiita.com/yoshiyama_hana/items/98360d34227cfcdd00b2

著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。

Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .

C++学習7:staticとthis、および単例モード

WebPack使用

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