BigQueryでUTC(米国標準時)からJST(日本標準時)へ変更する
/
igQueryでUTC(米国標準時)からJST(日本標準時)へ変更します
# データの作成
WITH purchase_log AS (
SELECT 'A' AS user_id, '2016-01-30' as last_date, '2016-01-30' as p_date, 'メロン' AS item UNION ALL
SELECT 'A', 2017-12-30, 2016-01-30, '白菜' UNION ALL
SELECT 'B', 2016-10-31, 2016-01-30, 'りんご' UNION ALL
SELECT 'B', 2017-06-30, 2016-01-30, 'りんご'
)
SELECT * FROM purchase_log;
user_id | last_date | p_date | item |
---|---|---|---|
A | 2016-01-30 | 2017-12-30 | メロン |
A | 2017-12-30 | 2017-12-31 | 白菜 |
B | 2016-10-31 | 2017-08-31 | りんご |
B | 2017-06-30 | 2018-01-31 | りんご |
# 表示をJST(日本標準時)へ変更
SELECT
user_id,
last_date,
-- 表示をJST(日本標準時)へ変更
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', last_date, 'Asia/Tokyo') AS JST
FROM
(
SELECT
user_id,
CAST(last_date AS TIMESTAMP) AS last_date,
CAST(p_date AS TIMESTAMP) AS p_date
FROM
test1.purchase_log
);
user_id | last_date | JST |
---|---|---|
A | 2016-01-30 00:00:00 UTC | 2016-01-30 09:00:00 |
A | 2017-12-30 00:00:00 UTC | 2017-12-30 09:00:00 |
B | 2016-10-31 00:00:00 UTC | 2016-10-31 09:00:00 |
B | 2017-06-30 00:00:00 UTC | 2017-06-30 09:00:00 |