【GoogleBigQueryTips】standardSQLで、秒・ミリ秒・マイクロ秒タイムスタンプを->日付に変換する..他

はじめに

タイムスタンプを日付に変換するというのは、分析をしているとしょっちゅう使うクエリではあるのものの、毎回忘れてしまうのでこちらに残しておきます。

特に、FirebaseAnalyticsから、BigQueryにデータをエクスポートしている場合には、"初めてアプリを開いた日"がミリ秒のタイムスタンプとして保持されているので、ユーザー分析を行う上で以下の変換を行うことは良くあるかと思います。

ご参考までに!

秒 --> 日付に変換

EXTRACT(DATE FROM TIMESTAMP_SECONDS("タイムスタンプ[秒]")　AT TIME ZONE 'Asia/Tokyo') AS date

ミリ秒 --> 日付に変換

EXTRACT(DATE FROM TIMESTAMP_MILLIS("タイムスタンプ[ミリ秒]")　AT TIME ZONE 'Asia/Tokyo') AS date

マイクロ秒 --> 日付に変換

EXTRACT(DATE FROM TIMESTAMP_MICROS("タイムスタンプ[マイクロ秒]")　AT TIME ZONE 'Asia/Tokyo') AS date

おまけ

UTCタイムスタンプ --> 　時間帯を抽出

FORMAT_TIMESTAMP("%H", timestamp, 'Asia/Tokyo') as hour

UTCタイムスタンプ --> 　　週を抽出

EXTRACT(DAYOFWEEK FROM date(timestamp, 'Asia/Tokyo')) as week

FROM句で○○日前を指定

FROM XXX
WHERE
 _TABLE_SUFFIX BETWEEN REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 8 day) AS string), '-', '')
  AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')

timestampと本日との差分を抽出する

*以下は、年変換する事で年齢を抽出

ROUND(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),birthday,DAY)/365,0) as AGE