STRING→TIMESTAMPでUTC扱い｜JST列との比較で+9時間ずれる

この記事で分かること

• 元テーブルが STRING、挿入先テーブルが TIMESTAMP のとき、ゾーン未指定だと日時が UTC として入ること
• 単独利用では気づきにくいこと（見た目の時刻は STRING のまま）
• 正しく JST が入った TIMESTAMP 列と結合・比較すると +9 時間ずれることが重要です
• 9時間ずれると、時間帯別の売上ピークを誤認すること
• キャスト時に 'Asia/Tokyo' などを明示する直し方

結論（先に要点）

元が STRING、挿入先が TIMESTAMP で、ゾーンを指定せず TIMESTAMP(col) すると、文字列は UTC として扱われます。

挿入先テーブルだけ見る分には気づきにくい。痛いのは、JST 正しい TIMESTAMP との比較・結合で +9 時間ずれることと、時間帯別の売上ピークを誤認すること。

直しはキャスト時に 'Asia/Tokyo' などを明示する。

※ 本稿のプロジェクトID・データセット名・テーブル名・列名は説明用の仮名です。

よくある場面

よくあるパターンは次です。

• 元テーブルからデータを抽出し、挿入先テーブルに格納する
• 元の時刻列は STRING（YYYY-MM-DD HH:MM:SS）
• 挿入先テーブル側は TIMESTAMP
• 投入時に TIMESTAMP(time_stamp_col) のように タイムゾーンを指定しない

日付や時刻の数字自体はSTRINGに入っていたままです。
そのため、この挿入先テーブルだけを見る限りでは、特に問題を感じないかもしれません。Excel 上で UTC 表示されても、無視して済むことが多いです。

1. 元テーブルの time_stamp_col（STRING）から抽出する
2. 挿入先テーブルの列は TIMESTAMP 型
3. だいたい次のような SQL で入れる

CREATE OR REPLACE TABLE `example_project.example_dataset.dest_table` AS
SELECT
  TIMESTAMP(time_stamp_col) AS time_stamp,  -- ゾーンなし → UTC として解釈
  event_id
FROM
  `example_project.example_dataset.source_events`;

4. 挿入先テーブル単体の件数確認や目視では違和感が少ない
5. 別テーブルの event_ts（正しく JST 解釈済みの TIMESTAMP）と = や範囲比較をしたとき、ずれに気づく

「通常使用している元テーブルであっても、STRINGのままではタイムゾーンが指定されていないため、これは別の問題となります。」

なぜ単独では平気で、比較で壊れるか

STRING の 2024-06-01 09:00:00 は、まだ「どのゾーンの9時か」が付いていません。
TIMESTAMP(string) でゾーンを省略すると、既定は UTC です。

• ゾーンなしキャスト: 文字列 '2024-06-01 09:00:00' を UTC 時刻として解釈する
• JST として正しく入れた列: 同じ文字面なら 09:00 Asia/Tokyo（＝00:00 UTC）という瞬間にする

同じ「09:00」でも、絶対時刻は 9時間違うことがあります。
挿入先テーブルだけ見るとどちらも「09:00…」に見えるので、比較するまで気がつきません。

実害イメージ（時間帯別のピーク誤認）

+9 時間が「比較の式の中」だけに閉じず、業務の読み取りににじむ例です。

時間帯別の売上（0時台、1時台…）を、挿入先テーブルの TIMESTAMP で切ると、山の位置が およそ9時間ずれます。
実際は昼ピークなのに朝に見えたり、夜ピークなのに昼に見えたりします。

単体の明細を眺めても文字面はそれっぽいので、時間帯のグラフやランキングで初めておかしくなります。
Excel の「UTC」表示を無視しても、ピーク帯の誤認の方が先に効きます。

直し方の例

文字列が 日本時間の壁時計 で、ゾーン表記を含まない前提です。

-- NG寄り: ゾーンなし → STRING の文字面を UTC として TIMESTAMP 化
CREATE OR REPLACE TABLE `example_project.example_dataset.dest_table` AS
SELECT
  TIMESTAMP(time_stamp_col) AS time_stamp,
  event_id
FROM
  `example_project.example_dataset.source_events`;

-- OK寄り: 日本時間として解釈してから TIMESTAMP にする
CREATE OR REPLACE TABLE `example_project.example_dataset.dest_table` AS
SELECT
  TIMESTAMP(time_stamp_col, 'Asia/Tokyo') AS time_stamp,
  event_id
FROM
  `example_project.example_dataset.source_events`;

PARSE_TIMESTAMP でもタイムゾーンを渡せます。

SELECT
  PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', time_stamp_col, 'Asia/Tokyo') AS time_stamp
FROM
  `example_project.example_dataset.source_events`;

文字列側にすでに +09 や Asia/Tokyo や UTC が含まれているなら、その情報が優先されます。
二重にゾーンを付けないよう、実データの表記を一度確認してください。

比較でずれていないかの確認イメージ

-- 挿入先テーブル（ゾーンなしキャスト）と、JST正しい別テーブルを突き合わせる例
SELECT
  r.event_id,
  r.time_stamp AS result_ts,
  j.event_ts AS jst_correct_ts,
  TIMESTAMP_DIFF(r.time_stamp, j.event_ts, HOUR) AS hour_diff
FROM
  `example_project.example_dataset.dest_table` AS r
INNER JOIN
  `example_project.example_dataset.jst_correct_events` AS j
  ON r.event_id = j.event_id;

同じ業務時刻のつもりなのに hour_diff が 9 前後なら、キャスト時のゾーンを疑います。

実行前の短いチェック

1. 元列が STRING / 挿入先列が TIMESTAMP か見る
2. TIMESTAMP(col) / CAST(col AS TIMESTAMP) に ゾーン指定があるか見る
3. 挿入先テーブルを単独で見て安心しない。JST正しい TIMESTAMP との結合・比較を想定する
4. Excel の UTC 表示だけで判断しない（無視して済むことが多い）

失敗例と回避策

• 失敗: 挿入先テーブルの文字列が一致しているため、ゾーンを指定せずにキャストを行う
  回避: あとから JST の TIMESTAMP と突き合わせる前提なら、投入時に 'Asia/Tokyo' を明示する
• 失敗: Excel 上での UTC 表示に注意しすぎ、結合ロジックを見直さない
  回避: 表示より、比較・結合・時間帯別ピークでの時差を先に疑う
• 失敗: 時間帯別売上の山がずれたのを、集計SQLのバグだけだと決めつける
  回避: 元 STRING → 挿入先 TIMESTAMP のゾーン指定を先に確認する
• 失敗: 比較側だけ TIMESTAMP_ADD(..., INTERVAL 9 HOUR) でその場しのぎする
  回避: 挿入先テーブルへの投入時の解釈を直す（しのぎは別クエリでまたずれる）

まとめ

• 元 STRING → 挿入先 TIMESTAMP でゾーン省略すると、文字列は UTC として扱われる
• 単独利用では気づきにくく、Excel の UTC 表示も無視して済むことが多い
• JST 正しい TIMESTAMP との結合や比較で +9 時間のズレが発生しやすいことが問題点である
• 実害例: 時間帯別の売上ピークを、およそ9時間ずれて誤認する
• 日本時間として扱う場合は、キャスト時に 'Asia/Tokyo' を明示的に指定すること

次に読む

• BigQueryでリファクタリング前後のテーブルが一致するか検証する｜件数が多いときのハッシュ比較
• BigQueryの重い処理は結果をテーブルに残して翌日確認する
• BigQuery Studioを閉じても大丈夫｜ジョブ履歴から検索結果を開く