bigquery timestampのチートシート
具体例
今の時間から差分を計算
TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_login, DAY) as days_from_last_login
STRFTIME, FORMAT_TIMESTAMP(timestampをstring型のデータへ変換)
FORMAT_TIMESTAMP("%Y-%m-%d", timestamp)
TIMESTAMP; unix time, epoch timeをtimestamp型に変換する
SELECT TIMESTAMP_SECONDS(1230219000) -- 2008-12-25 15:30:00 UTC
SELECT TIMESTAMP_MILLIS(1230219000000) -- 2008-12-25 15:30:00 UTC
SELECT TIMESTAMP_MICROS(1230219000000000) -- 2008-12-25 15:30:00 UTC
STRING型をdate型に変換する
SELECT DATE(TIMESTAMP_SECONDS(1230219000)) -- 2008-12-25
SELECT CAST('2008-12-25' AS DATE) -- 2008-12-25
SELECT DATE('2008-12-25', 'UTC') -- 2008-12-25