BigQueryの日付関数

BigQueryの日付関数についてよく使うものを備忘録としてまとめました。

##### 現在の日時

```
SELECT CURRENT_DATE() AS cur_d,
       CURRENT_DATETIME() AS cur_dt,
       CURRENT_TIMESTAMP() AS cur_ts

+-------------+----------------------------+--------------------------------+
| cur_d       | cur_dt                     | cur_ts                         |
+-------------+----------------------------+--------------------------------+
| 2020-09-20  | 2020-09-21T12:34:56.123456 | 2020-09-21 12:34:56.123456 UTC |
+-------------+----------------------------+--------------------------------+
```

##### 現在の日時を YYYY-MM-DD HH:MM:SS形式で表示

```
SELECT FORMAT_DATETIME("%Y-%m-%d %H:%M:%S", CURRENT_DATE()) AS fmt_d,
       FORMAT_DATETIME("%Y-%m-%d %H:%M:%S", CURRENT_DATETIME()) AS fmt_dt,
       FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", CURRENT_TIMESTAMP()) AS fmt_ts

+---------------------+---------------------+---------------------+
| fmt_d               | fmt_dt              | fmt_ts              |
+---------------------+---------------------+---------------------+
| 2020-09-20 00:00:00 | 2020-09-20 12:34:56 | 2020-09-20 12:34:56 |
+---------------------+---------------------+---------------------+
```

##### タイムゾーンを日本にする

```
SELECT CURRENT_DATETIME() AS before, 
       CURRENT_DATETIME('Asia/Tokyo') AS after_dt, 
       FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'Asia/Tokyo') AS after_ts,
       CURRENT_DATE('Asia/Tokyo') AS after_d

+----------------------------+----------------------------+---------------------+---------------------+
| before                     | after_dt                   | after_ts            | after_d             |
+----------------------------+----------------------------+---------------------+---------------------+
| 2020-09-21T12:34:56.123456 | 2020-09-21T21:34:56.123456 | 2020-09-21 21:34:56 | 2020-09-21          |
+----------------------------+----------------------------+---------------------+---------------------+
```

##### 現在の日時を日（時分秒は全て0）で丸める

```
SELECT DATETIME_TRUNC(CURRENT_DATETIME(), DAY) AS dt_trc,
       TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS ts_trc

+---------------------+-------------------------+
| dt_trc              | ts_trc                  |
+---------------------+-------------------------+
| 2020-09-20T00:00:00 | 2020-09-21 00:00:00 UTC |
+---------------------+-------------------------+
```

##### 現在の日時から `年` 、 `月` 、 `日` を抜き出す

```
SELECT EXTRACT(YEAR FROM CURRENT_DATETIME()) AS year,
       EXTRACT(MONTH FROM CURRENT_DATETIME()) AS month,
       EXTRACT(DAY FROM CURRENT_DATETIME()) AS day

+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2020 |  09   | 20  |
+------+-------+-----+

※TIMESTAMP も同様
```

##### 現在の日時に日を加算

```
SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AS d_add,
       DATETIME_ADD(CURRENT_DATETIME(), INTERVAL 1 DAY) AS dt_add,
       TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AS ts_add

+-------------+----------------------------+--------------------------------+
| d_add       | dt_add                     | ts_add                         |
+-------------+----------------------------+--------------------------------+
| 2020-09-21  | 2020-09-21T12:34:56.123456 | 2020-09-21 12:34:56.123456 UTC |
+-------------+----------------------------+--------------------------------+
```

##### 日付型から文字列へ変換

```
SELECT CURRENT_DATETIME() AS before,
       CAST(CURRENT_DATETIME() AS STRING) AS after

+----------------------------+----------------------------+
| before                     | after                      |
+----------------------------+----------------------------+
| 2020-09-21T12:34:56.123456 | 2020-09-21 12:34:56.123456 |
+----------------------------+----------------------------+
```

##### 文字列から日付型（DATE型、DATETIME型、TIMESTAMP型）へ変換

```
SELECT PARSE_DATE("%Y%m%d", "20200920") AS prs_d,
       PARSE_DATETIME("%Y%m%d %H%M%S", "20200920 123456") AS prs_dt,
       PARSE_TIMESTAMP("%Y%m%d %H%M%S", "20200920 123456") AS prs_ts

+-------------+---------------------+-------------------------+
| prs_d       | prs_dt              | prs_ts                  |
+-------------+---------------------+-------------------------+
| 2020-09-21  | 2020-09-21T12:34:56 | 2020-09-21 12:34:56 UTC |
+-------------+---------------------+-------------------------+
```

##### DATETIME型 から TIMESTAMP型へ変換

```
SELECT CURRENT_DATETIME() AS before,
       TIMESTAMP(CURRENT_DATETIME()) AS after

+----------------------------+--------------------------------+
| before                     | after                          |
+----------------------------+--------------------------------+
| 2020-09-21T12:34:56.123456 | 2020-09-21 12:34:56.123456 UTC |
+----------------------------+--------------------------------+
```

##### TIMESTAMP型 から DATE型へ変換

```
SELECT CURRENT_TIMESTAMP() AS before,
       DATE(CURRENT_TIMESTAMP(), "Asia/Tokyo") AS after

+----------------------------+-------------+
| before                     | after       |
+----------------------------+-------------+
| 2020-09-21T15:00:00.000000 | 2020-09-22  |
+----------------------------+-------------+
```

##### TIMESTAMP型 から DATETIME型へ変換

```
SELECT CURRENT_TIMESTAMP() AS before,
       DATETIME(CURRENT_TIMESTAMP(), "Asia/Tokyo") AS after

+----------------------------+-------------+
| before                     | after       |
+----------------------------+-------------+
| 2020-09-21T15:00:00.000000 | 2020-09-22  |
+----------------------------+-------------+
```