テック

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

方法

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

結果

unixtimejst
16195318932021-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

結果

unixtimejst
16195318930002021-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

結果

unixtimejst
16195318930000002021-04-27T22:58:13

解説

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

  1. unixtime → TIMESTAMP 変換
  2. TIMESTAMP → 日付文字列 変換
  3. 日付文字列 → 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

関連クエリ

より実務的なクエリを身につけるには「ビッグデータ分析・活用のためのSQLレシピ」がおすすめ。

ビッグデータ分析