GA4

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

2024.05.22

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_1とTimeStamp_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を結果として返します。