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 に変換されます。