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

結果

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

解説

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

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

関連クエリ

• TIMESTAMP_SECONDS
• TIMESTAMP_MILLIS
• TIMESTAMP_MICROS
• FORMAT_TIMESTAMP
• CAST

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

ビッグデータ分析

ビッグデータ分析・活用のためのSQLレシピ

created by Rinker

• Amazon

図解即戦力　ビッグデータ分析のシステムと開発がこれ1冊でしっかりわかる教科書

created by Rinker

技術評論社

• Amazon