【BigQuery】時刻を数字から日付の形に直す（UNIXをTIMESTAMPに変換する）

概要

BigQueryで「1673349621363453」を「2023/01/10 20:20:21」に直す、というような方法について説明します。

1
2


SELECT user_first_touch_timestamp
FROM  テーブル

このような、時刻が数字で表現されているが「yyyy/mm/dd HH:MM:SS」のような表記に直したいときは、UNIXをTimestampに変換する必要があります。BigQueryでは以下の関数を使用します。

1
2
3
4
5
6
7
8


-- セカンド秒をタイムスタンプに
TIMESTAMP_SECONDS(hoge)

--ミリ秒をタイムスタンプに
TIMESTAMP_MILLIS(hoge)

--マイクロ秒をタイムスタンプに
TIMESTAMP_MICROS(hoge)

今回、先ほどの値はマイクロ秒単位であったため、TIMESTAMP_MICROS関数を使用します。

1

TIMESTAMP_MICROS(user_first_touch_timestamp)

直せました。しかしここでUTCと記載されていることに注意してください。UTCは世界標準時間であるため日本とは時差があります。あなたが日本にいる場合は、JSTに変更する必要があります。

DATEおよびDATETIME関数を使うと簡潔に書くことができます。

1

DATETIME(TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo')

自分好みのフォーマットに合わせたいかたはFORMAT_TIMESTAMP関数を使用してください。

1

FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(user_first_touch_timestamp), 'Asia/Tokyo')

綺麗に修正することができました。

変換された日付が本当に合っているのか確かめたい方はツールを使用すると便利です。

余談ですが、このuser_first_touch_timestampは実際にGoogle Analytics4にある変数です。ユーザーが初めてサイトに訪問した時刻をマイクロ秒単位で記録します。型はintegerです。