[bigquery]UCT時刻をJSTに変換する

2021-08-01 2021-12-18

-- 事前にDECLAREで変数定義
DECLARE created_at TIMESTAMP;
DECLARE created_at_str STRING;

-- 変数に値を設定
SET created_at = TIMESTAMP('2019-11-01 00:00:00');
SET created_at_str = '2019-11-01 00:00:00';


SELECT
  created_at AS UTC

  -- created_atがtimestamp型の時
  , FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at, 'Asia/Tokyo')
     AS JST
 
　 -- created_atがstring型の時はこちらを利用
  , FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP(SUBSTR(created_at_str, 1, 19)),"Asia/Tokyo") 
    AS JST_ex2

◆実行結果