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

# はじめに

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

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

[参照元](https://prestodb.io/docs/current/functions/datetime.html)



# タイムゾーンの設定

````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)に実行した結果になります。

```sql
SELECT current_timestamp
```

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

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

````sql
SELECT current_timestamp AT TIME ZONE 'Asia/Tokyo'
````

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



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

```sql
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)     |



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

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

```
> 2020/11/20
```

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



# 演算処理

intervalを用いて加算

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

```
> 2020-11-20 16:00:00.778
```



intervalを用いて減算

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

```
> 2020-11-19 22:00:00.000
```



`date_add` を用いて演算

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

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



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

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

```sql
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からタイムスタンプ型へ変換する

```sql
SELECT from_unixtime(1605793223)
```

```
> 2020-11-19 13:40:23.000
```



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

```sql
SELECT from_unixtime(1605793223, 'Asia/Tokyo')
```

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





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

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

```
> 1.605793223E9
```





# タイムスタンプの一部を抜き取る

年を抜き取る場合

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

```
> 2020
```



月を抜き取る場合

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

```
> 11
```



日を抜き取る場合

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

```
> 19
```



曜日を抜き取る場合

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

```
> 4
```

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



時間を抜き取る場合

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

```
> 22
```



分を抜き取る場合

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

```
> 4
```



秒を抜き取る場合

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

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