[빅쿼리] 타임존 설정하기

 

정의

추출하고자 하는 datetime / date 값의 시간대를 세팅하는 기능이다.

구글 빅쿼리에 적재되는 datetime / date 형태는 UTC 형태가 일반적이나, 적재되고 있는 타임존을 확인하기 위해선 샘플을 돌려보는 것이 보다 정확하겠다. 아재의 회사 데이터는 빅쿼리에선 UTC로, Mysql 에선 한국시간대로 적재되고 있어 시간대를 맞춰주는 작업이 필요하다.

* UTC 란, "협정 세계 표준시" 로 이 시간을 기준으로 각 나라의 시간은 ±a 로 설정된다. (한국은 +9h)

형태

datetime(datetime_expression, '타임존' )
date(date_expression, '타임존' )

설명

타임존은 각 국가/지역 에 따라 시간대를 설정할 수 있다. 사용하고자 하는 타임존 지역을 입력하면 이에 맞는 값을 추출할 수 있다. 링크 를 클릭하여 지역별 시간대를 확인할 수 있다.

이 글을 보고 있는 한국인들과 아재는 한국의 시간대를 사용하여 예시를 보여주도록 하겠다.

 

주의해야 할 것 :

적재된 데이터의 형태가 string 인 경우, 적재된 시간대가 UTC 가 아닐 수 있다. string 형태로 적재된 시간값을 사용할 경우, datetime 또는 date를 사용하여 'Asia/Seoul' 시간대 변환을 하면 오히려 오차가 발생할 수 있다.

예를 들어, "연/월/일"을 나타내는 string 타입의 YMD 라는 컬럼과 "시/분/초"를 나타내는 string 타입의 HMS 라는 컬럼을 합쳐서 한 개의 시간대를 만들었다고 하자.

SELECT CONCAT(YMD,HMS) AS ajae_time 
이 때 별칭으로 지정한 "ajae_time" 은 적재 당시부터 "문자형태(string)" 으로 고정된 값이므로 UTC 변환이 되어있지 않을 수 있다.

이 점을 확인하지 않고, datetime 또는 date 를 사용해 'Asia/Seoul' 시간대로 변환하면 정확한 시간에서 + 9h 를 만드는 실수를 범할 수 있다. 따라서 해당 값을 먼저 샘플로 검수하여 시간대 변환이 필요한지 여부를 꼭 확인하도록 하자.

시간대 변환 에 따른 비교

쿼리

SELECT      now_time
            ,now_date
            ,now_time_asia
            ,now_date_asia
            ,datetime(cast(now_time as timestamp),'Asia/Seoul') as Asia_Seoul
            ,datetime(cast(now_time as timestamp),'Australia/Brisbane') as Austrailia
FROM (
        SELECT current_datetime() as now_time
              ,current_date() as now_date
              ,current_datetime('Asia/Seoul') as now_time_asia
              ,current_date() as now_date_asia
       )  

결과값

시간대 변환에 따른 비교

위의 결과값을 보면 차이가 확연히 보인다. datetime 의 활용인 "current_datetime () " 도, 시간대를 설정해주느냐 그렇지 않느냐에 따라 결과값이 달라진다.

Asia/Seoul 을 입력한 것과 그렇지 않은 것은, 결과값에서 9시간의 시간차가 존재함을 확인할 수 있다.

Australia/Brisbane 을 입력한 것과 그렇지 않은 것은 10시간의 시간차가 존재한다. 

 

이처럼, 기본적으로 사용되는 시간대는 UTC 이며, 우리의 시간대에 맞추기 위해서는 타임존 지정을 해주어야 한다.

* 추가로, 위의 결과값에는 일자와 시간 사이에 " T " 가 보기싫게 존재한다. 이를 없애고 싶다면 아래 포스팅을 참고하길 바란다.

2020/05/04 - [SQL] - [빅쿼리] FORMAT_DATE / FORMAT_DATETIME

[빅쿼리] FORMAT_DATE / FORMAT_DATETIME

그럼 오늘도 굿데이 :)

출처 : GRAFOLIO