[BigQuery] unixtime 을 timestamp 로 변환

웹사이트 분석에 없어서는 안될 분석 도구인 Google Analytics 가 기존 Universal Analytics (GA3) 에서 GA4 로 강제 전환이 된다.

2023년 7월부터는 GA3를 이용할 수 없게된다.

그리고 GA4로 전환이 되면서 여러 가지 장점이 있는데 그 중에 하나가 BigQuery를 무료로 이용할 수 있는 것이다. 

 

그래서 BigQuery에 대해서 여러 회차에 나누어 글을 써보고자 한다.

이번에 다룰 내용은 숫자(int64 타입) 형태로 되어있는 시간 컬럼(event_timestamp)을 timestamp (날짜 형식 yyyy-mm-dd ) 으로 변환하는 것을 얘기하려 한다.

 

GA4 를 Bigquery 와 연결하고 데이터를 살펴보면 다음과 같다.

이벤트의 발생 시간인 event_timestamp 가 위처럼 숫자형식으로 보인다. 

저 숫자를 100만으로 나누어주고, TIMESTAMP_SECONDS 라는 함수를 이용하여 변환해주어야 한다.

쿼리로 살펴보면 아래와 같다.

 

select TIMESTAMP_SECONDS(CAST(CAST(event_timestamp as INT64)/1000000 as INT64)) + interval '9' hour as dt, *
from analytics_308890446.events_20220624
limit 10

위 쿼리에서 + interval '9' hour 가 시간변환 이후에 있는 이유는 unix_time 자체가 UTC 기준이기 때문에 9시간을 더하여 한국시간대로 바꾸어주는 부분이다.

 

 

위의 결과를 보면 event_timestamp 가 맨 앞의 dt 에서 yyyy-mm-dd HH:mi:ss 형식으로 잘 변환되어 있다. 


다음 시간에는 BigQuery의 Array 와 Struct 에 대해서 적어보도록 하겠다.
https://walkingfox.tistory.com/169

[BigQuery] Array, Struct 그리고 unnest