こんにちは、技術部のMです。
今回は、BigQuery内のGA4データで扱われる、event_timestampの値を、人間が理解できる「YYYY-MM-DD」のような日付型に変換したい場合の方法を解説します。
結論
以下のクエリを記述します。
SQL
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), SECOND), "Asia/Tokyo")
実行結果
例として、「1705392000000000マイクロ秒」を変換してみましょう。
SQL
FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(1705392000000000), SECOND), "Asia/Tokyo")
実行結果は以下となります。
yml
2024-01-01 00:00:00
解説
event_timestampとは
event_timestampは、Google Analytics 4（GA4）のイベントが発生した時刻を表すタイムスタンプで、UNIXエポックからの経過時間をマイクロ秒単位で表しています。
UNIXエポックとは、1970年1月1日午前0時（協定世界時）を基準とした日時を表す一般的な方法です。
上記のクエリでは、event_timestampを3つの関数が使って変換しています。TIMESTAMP_MICROS
TIMESTAMP_MICROSは、マイクロ秒単位のタイムスタンプを表す整数値を引数に受け取り、それをBigQueryのタイムスタンプデータ型（TIMESTAMP）に変換します。
これにより、マイクロ秒単位のタイムスタンプをBigQueryのクエリで使用できるタイムスタンプ形式に変換することができます。TIMESTAMP_TRUNC
TIMESTAMP_TRUNCは、指定されたタイムスタンプの精度を切り捨てるために使用されます。
時間を切り捨てる対象の時刻(TIMESTAMP型、TIME型、DATETIME型)の値と切り捨てる時間の単位(HOUR, MINUTE, SECONDなど)を引数として指定します。FORMAT_TIMESTAMP
FORMAT_TIMESTAMPは、指定されたフォーマットでタイムスタンプを文字列としてフォーマットするために使用されます。
タイムスタンプを指定したいフォーマット文字列（'%Y-%m-%d %H:%M:%S'など）、フォーマットしたい対象の時刻(TIMESTAMP型、TIME型、DATETIME型)の値を引数として受け取り、指定された形式に変換します。
また、オプションとして、タイムゾーンを指定することもできます。
終わりに
最後までお読みいただきありがとうございました！
関連記事
- 2024-03-18
- テクノロジー