はじめに
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であることには注意が必要です。
この記事が開発や分析のご助力となれば幸いです。