前提条件
- standardSQL使用
- created_atはtimestamp型
SQL
SELECT
created_at AS UTC,
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at, 'Asia/Tokyo') AS JST
FROM `PROJECT.DATASET.table_*`
WHERE _TABLE_SUFFIX BETWEEN '20180801' AND '20180801'
ORDER BY created_at
LIMIT 5
結果
| 行 | UTC | JST |
|---|---|---|
| 1 | 2018-08-01 00:00:00 UTC | 2018-08-01 09:00:00 |
| 2 | 2018-08-01 00:00:01 UTC | 2018-08-01 09:00:01 |
| 3 | 2018-08-01 00:00:01 UTC | 2018-08-01 09:00:01 |
| 4 | 2018-08-01 00:00:02 UTC | 2018-08-01 09:00:02 |
| 5 | 2018-08-01 00:00:02 UTC | 2018-08-01 09:00:02 |