BigQuery関数 | unixtimeをDATETIMEに変換する

Contents

方法
解説
関連クエリ

方法

unixtime（秒）→ DATETIME

WITH raw_data AS (
  SELECT 1619531893 AS unixtime
)
SELECT
  unixtime,
  -- TIMESTAMP_SECONDS関数を使う
  CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime), 'Asia/Tokyo') as DATETIME) AS jst
FROM
  raw_data

結果

unixtime	jst
1619531893	2021-04-27T22:58:13

unixtime（ミリ秒）→ DATETIME

WITH raw_data AS (
  SELECT 1619531893000 AS unixtime
)
SELECT
  unixtime,
  -- TIMESTAMP_MILLIS関数を使う
  CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MILLIS(unixtime), 'Asia/Tokyo') as DATETIME) AS jst
FROM
  raw_data

結果

unixtime	jst
1619531893000	2021-04-27T22:58:13

unixtime（マイクロ秒）→ DATETIME

WITH raw_data AS (
  SELECT 1619531893000000 AS unixtime
)
SELECT
  unixtime,
  -- TIMESTAMP_MICROS関数を使う
  CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(unixtime), 'Asia/Tokyo') as DATETIME) AS jst
FROM
  raw_data

結果

unixtime	jst
1619531893000000	2021-04-27T22:58:13

解説

以下の手順で取得しています。

unixtime → TIMESTAMP 変換
TIMESTAMP → 日付文字列変換
日付文字列 → DATETIME 変換

①
TIMESTAMP_SECONDS(unixtime)

②
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime)

③
CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime), 'Asia/Tokyo') as DATETIME)

最終的なクエリは以下となります。

WITH raw_data AS (
  SELECT 1619531893 AS unixtime
)
SELECT
  unixtime,
  -- ①、②、③
  CAST(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(unixtime), 'Asia/Tokyo') as DATETIME) AS jst
FROM
  raw_data