BigQuery でミリ秒のタイムスタンプの値を日時表記に変換するメモです。

背景

とりあえず、データを蓄積するときにタイムスタンプのミリ秒を入れておくのですが、データを後で眺めるときに 2022-11-22 23:17 のような表記で見たいときに、いつも書き方を調べてしまうのでメモしておきます。

このようなデータがあるとします

timestamp の値を変換

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_MILLIS(CAST(timestamp AS INT64))) as timestampConverted
FROM
  `table`
LIMIT
  1000

こちらを SQL ワークスペースで実行します。

1669126627711 というミリ秒のタイムスタンプの値が、UTC 時間の 2022-11-22 14:17 に変換されます。

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_MILLIS(CAST(timestamp AS INT64)),"Asia/Tokyo") as timestampConverted
FROM
  `table`
LIMIT
  1000

こちらを SQL ワークスペースで実行します。

1669126627711 というミリ秒のタイムスタンプの値が、日本時間の 2022-11-22 23:17 に変換されます。

data の JSON の中の createdAt を変換

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_MILLIS(CAST(JSON_EXTRACT(data,"$.createdAt") AS INT64))) as createdAt
FROM
  `table`
LIMIT
  1000

こちらを SQL ワークスペースで実行します。

1669126627711 というミリ秒のタイムスタンプの値が、UTC 時間の 2022-11-22 14:17 に変換されます。

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_MILLIS(CAST(JSON_EXTRACT(data,"$.createdAt") AS INT64)),"Asia/Tokyo") as createdAtJST
FROM
  `table`
LIMIT
  1000

こちらを SQL ワークスペースで実行します。

1669126627711 というミリ秒のタイムスタンプの値が、日本時間の 2022-11-22 23:17 に変換されます。

参考文献

• タイムスタンプ関数  |  BigQuery  |  Google Cloud