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 |
+----------------------------+-------------+