GA4

【GA4 SQL】2つのevent_timestampの日付比較

GA4のevent_timestampの数値の羅列だけでは、いつの日付なのかは分からず、event_timestamp同士で時系列で比較したいケースがたまにあるので、メモとして記載する。

CREATE TEMP FUNCTION TimeStamp_1() RETURNS INT64 AS (1712565394871831);
CREATE TEMP FUNCTION TimeStamp_2() RETURNS INT64 AS (1712565387510754);

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp1, 'Asia/Tokyo') AS timestamp1_jst,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp2, 'Asia/Tokyo') AS timestamp2_jst,
  CASE
    WHEN timestamp1 > timestamp2 THEN 'timestamp1 is later'
    WHEN timestamp1 < timestamp2 THEN 'timestamp2 is later'
    ELSE 'Both timestamps are equal'
  END AS result_comparison
FROM (
  SELECT 
    TIMESTAMP_MICROS(TimeStamp_1()) AS timestamp1,
    TIMESTAMP_MICROS(TimeStamp_2()) AS timestamp2
)

ChatGPTによるクエリ解説

CREATE TEMP FUNCTION TimeStamp_1() RETURNS INT64 AS (1712565394871831);
CREATE TEMP FUNCTION TimeStamp_2() RETURNS INT64 AS (1712565387510754);

ここでは2つの一時的な関数TimeStamp_1TimeStamp_2を作成しています。これらの関数は引数を取らず、特定のハードコードされた整数値(UNIXエポックタイムスタンプのマイクロ秒単位)を返します。これにより、指定されたタイムスタンプをクエリ内で直接使用できます。

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp1, 'Asia/Tokyo') AS timestamp1_jst,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', timestamp2, 'Asia/Tokyo') AS timestamp2_jst,
  CASE
    WHEN timestamp1 > timestamp2 THEN 'timestamp1 is later'
    WHEN timestamp1 < timestamp2 THEN 'timestamp2 is later'
    ELSE 'Both timestamps are equal'
  END AS result_comparison
FROM (
  SELECT 
    TIMESTAMP_MICROS(TimeStamp_1()) AS timestamp1,
    TIMESTAMP_MICROS(TimeStamp_2()) AS timestamp2
)
  • TIMESTAMP_MICROS関数を使用して、各関数から得られる整数値をTIMESTAMP型に変換しています。これにより、UNIXエポックからのマイクロ秒単位の値を実際の日時に変換します。
  • FORMAT_TIMESTAMP関数を使用して、TIMESTAMP型の値を所定のフォーマット('%Y-%m-%d %H:%M:%S')に変換し、さらに'Asia/Tokyo'タイムゾーン(日本時間)に調整します。これにより、タイムスタンプが人間が読みやすい形式で表示されます。
  • CASE文を使って2つのタイムスタンプを比較し、どちらが新しいか(または等しいか)を判定しています。timestamp1 > timestamp2の場合はtimestamp1 is laterを、timestamp1 < timestamp2の場合はtimestamp2 is laterを、それ以外の場合はBoth timestamps are equalを結果として返します。

関連記事

【GA4SQL】最新のページタイトル(page_title)を取得

【GA4 SQL】ページごとのページビュー数をBigQueryから集計

【GA4 SQL】ページ単位のエンゲージメント分析

PAGE TOP