[빅쿼리] FORMAT_DATE / FORMAT_DATETIME

 

정의 

FORMAT_DATE / FORMAT_DATETIME (이하 FORMAT) 은 

내가 추출하고자 하는 값의 "날짜표현 형태"를 지정해주는 함수이다.

형태

빅쿼리 : FORMAT_DATETIME(format_string, datetime_expression)

Mysql : DATETIME_FORMAT(datetime_expression, format_string)

>> format_string : datetime에 지원되는 형식요소 (아래 설명 참조)

설명 

FORMAT 함수에 들어가는 "format_string" 은 datetime에 지원되는 "형식요소"를 의미한다. 

구글 표준 SQL에서는, 아래의 요소들을 지원한다고 한다. (더보기 클릭)

형식 요소	설명
%A	요일 이름입니다.
%a	요일 이름의 약어입니다.
%B	월 이름입니다.
%b 또는 %h	월 이름의 약어입니다.
%C	10진수(00~99)로 표현한 세기(연도를 100으로 나누어 정수로 자른 것)입니다.
%c	날짜 및 시간 표현입니다.
%D	%m/%d/%y 형식으로 표현한 날짜입니다.
%d	한 달의 일을 10진수(01~31)로 표현한 것입니다.
%e	한 달의 일을 10진수(1~31)로 표현한 것입니다. 한 자릿수 앞에는 공백이 옵니다.
%F	%Y-%m-%d 형식으로 표현한 날짜입니다.
%G	ISO 8601 연도를 세기와 함께 10진수로 표현한 것입니다. 각 ISO 연도는 태양력 연도의 첫 번째 목요일 전 월요일에 시작됩니다. 태양력 연도와 ISO 연도가 달라질 수 있는 태양력 연도 경계 근처에서 %G와 %Y가 서로 다른 결과를 생성할 수 있습니다.
%g	ISO 8601 연도를 세기 없이 10진수(00~99)로 표현한 것입니다. 각 ISO 연도는 태양력 연도의 첫 번째 목요일 전 월요일에 시작됩니다. 태양력 연도와 ISO 연도가 달라질 수 있는 태양력 연도 경계 근처에서 %g와 %y가 서로 다른 결과를 생성할 수 있습니다.
%H	시간(24시간제)을 10진수(00~23)로 표현한 것입니다.
%I	시간(12시간제)을 10진수(01~12)로 표현한 것입니다.
%j	연중 일을 10진수(001~366)로 표현한 것입니다.
%k	시간(24시간제)을 10진수(0~23)로 표현한 것입니다. 한 자릿수 앞에는 공백이 옵니다.
%l	시간(12시간제)을 10진수(1~12)로 표현한 것입니다. 한 자릿수 앞에는 공백이 옵니다.
%M	분을 10진수(00~59)로 표현한 것입니다.
%m	월을 10진수(01~12)로 표현한 것입니다.
%n	줄바꿈 문자입니다.
%P	am 또는 pm입니다.
%p	AM 또는 PM입니다.
%Q	분기를 십진수(1~4)로 표현한 것입니다.
%R	%H:%M 형식으로 표현한 시간입니다.
%r	AM/PM 표기법을 사용한 12시간제 시간입니다.
%S	초를 10진수(00~60)로 표현한 것입니다.
%s	1970-01-01 00:00:00 이후의 초 수입니다. 문자열에서 %s가 나타나는 위치에 상관없이 항상 다른 모든 형식 요소보다 우선합니다. 여러 개의 %s 요소가 나타날 경우, 마지막 요소가 우선합니다.
%T	%H:%M:%S 형식으로 표현한 시간입니다.
%t	탭 문자입니다.
%U	한 해의 주 번호(일요일이 일주일의 첫 번째 날임)를 10진수(00~53)로 표현한 것입니다.
%u	요일(월요일이 일주일의 첫 번째 날임)을 10진수(1~7)로 표현한 것입니다.
%V	ISO 8601 한 해의 주 번호(월요일이 일주일의 첫 번째 날임)를 10진수(01~53)로 표현한 것입니다. 새해에 1월 1일이 포함된 주의 일수가 4일 이상인 경우, 그 주가 첫 번째 주이고, 그렇지 않은 경우에는 그 주가 이전 연도의 53번째 주이고 그 다음 주가 첫 번째 주입니다.
%W	한 해의 주 번호(월요일이 일주일의 첫 번째 날임)를 10진수(00~53)로 표현한 것입니다.
%w	요일(일요일이 일주일의 첫 번째 날임)을 10진수(0~6)로 표현한 것입니다.
%X	시간을 HH:MM:SS 형식으로 표현한 것입니다.
%x	날짜를 MM/DD/YY 형식으로 표현한 것입니다.
%Y	연도를 세기와 함께 10진수로 표현한 것입니다.
%y	연도를 세기 없이 10진수(00~99)로 표현한 것입니다. 앞의 0 표기 여부는 선택할 수 있습니다. %C와 함께 사용할 수 있습니다. %C를 지정하지 않으면, 00~68년은 2000년대이고 69~99년은 1900년대입니다.
%%	단일 % 문자입니다.
%E#S	초를 #자리 소수의 정밀도로 표현한 것입니다.
%E*S	초를 전체 소수 자릿수로 표현한 것입니다(리터럴 '*').
%E4Y	4자릿수 연도(0001~9999). %Y는 연도를 완전히 렌더링하는 데 필요한 만큼의 문자 수를 생성하니 유의하세요.

출처 : 구글 표준 SQL 쿼리 참조 부문

즉 다음과 같은 형태를 띄게 되는 것이다.

> FORMAT_DATETIME('%F', datetime_expression)

 

주의해야 할 것 :

FORMAT_DATETIME 와 FORMAT_DATE 는 데이터타입 사용에 차이가 있다. (expression의 차이)

FORMAT_DATETIME('%F',datetime_expression)
FORMAT_DATE('%F',date_expression)
위의 형태를 맞추지 않게 되면, 정상적으로 작동하지 않는다.

필요에 따라 타임존 설정을 해야 할 수 있다. (타임존을 별도로 설정하지 않을 경우, UTC 값으로 반환된다.)
타임존 설정은 아래 포스팅에 추가해두었다.

2020/05/06 - [SQL] - [빅쿼리] 타임존 설정하기

---

형식요소(format_string) 에 따른 비교

쿼리

# 빅쿼리의 날짜 형식요소 

    SELECT '2020-05-04 14:26:38' AS standard
          ,FORMAT_DATETIME('%F %X','2020-05-04 14:26:38') as F_X
          ,FORMAT_DATETIME('%B','2020-05-04 14:26:38') as B
          ,FORMAT_DATETIME('%D','2020-05-04 14:26:38') as D
          ,FORMAT_DATETIME('%G','2020-05-04 14:26:38') as G
          ,FORMAT_DATETIME('%Y','2020-05-04 14:26:38') as Y
          ,FORMAT_DATETIME('%H','2020-05-04 14:26:38') as H
          ,FORMAT_DATETIME('%U','2020-05-04 14:26:38') as U_1
          ,FORMAT_DATETIME('%u','2020-05-04 14:26:38') as u_2 #이거슨 요일을 나타냄 (월요일부터 1번)
          ,FORMAT_DATETIME('%V','2020-05-04 14:26:38') as V

결과값

---

위의 쿼리에서 datetime_expression 값은 모두 동일하게 입력했다.

하지만 형식요소 표현에 따라 각각 다른 값들이 반환 되고 있음을 확인할 수 있다.

%F %X : standard 와 완전히 같은 값. 연-월-일 시:분:초
%B: 월의 이름. 5월 의 값만 반환하여 "May" 라는 값을 꺼내 주었다.
%D: 월/일/연 의 형태로 반환
%G: 연도를 반환. 단, %G는 ISO 8601을 사용하여 태양력을 기준으로 반영되는 연도이다. 따라서 %Y와 다른 값이 나타날 수 있으므로 주의해야 한다. 태양력에 대해서는 조금 더 공부해서 추후에 업데이트 하겠다.
%Y: 연도를 반환. 연도를 "세기(century)" 와 함께 10진수로 표현한 것.
%H: "시간" 을 반환. 14시 라는 값만 꺼내 주었다.
%U: 일요일이 한주의 첫 시작이라는 기준으로 "주차"를 산정한 것이다. 주차는 00주 ~ 53주까지 존재한다.
%u: 요일값을 반환한다. 월요일부터 1번이다. (화요일은 2번, 수요일은 3번 .. )
%V: 월요일이 한주의 첫 시작이라는 기준으로 "주차"를 산정한 것이다. 주차는 01주 ~ 53주까지 존재한다.       단, %G 와 마찬가지로 태양력을 기준으로 ISO 8601을 사용하므로, 주의해야 한다.

* ISO 8601 의 태양력을 기준으로 활용되는 형식요소는, 같은 기준끼리 묶는 것이 보다 정확한 값이 나타나는 것으로 보였다. 예를 들어, %G-%V 를 묶어서 사용하는 것과, %Y-%V 를 묶어서 사용하는 것은 서로 다른 결과 값을 나타낼 수 있다는 것이다. 이에 관하여는 다음에 포스팅하도록 하겠다.

 

오늘도 고생했습니다. :)