(SQL) 빅쿼리 날짜 함수

04 Sep 2024 in 가이던스 on SQL

(SQL) 빅쿼리 날짜 함수

---

1. 날짜 함수란?

날짜 함수란 날짜 타입 ( DATE, DATETIME ,TIMESTAMP…) 의 데이터를 다룰때 사용하는 함수이다.

날짜의 출력 형식을 지정하거나 날짜를 계산할 때 사용한다.

• Case 1 ) 날짜 출력 형식 :
  
  • YYYY-MM-DD 형식으로 값이 입력되어 있는 날짜 필드를 YYYYMMDD 형식으로 바꾸고 싶다.
    
  • ( ex. 2024-08-02 -> 20240802 )
    
• Case 2 ) 날짜 계산 :
  
  • 현재 날짜 기준, n일전 데이터에서 전일자 데이터까지 조회하고 싶다.
    
  • ( ex. 2024년 8월 28일 기준 , 35일전인 데이터에서 전일자 데이터까지 조회하고 싶다 )
    

날짜 출력 형식을 지정할때는 어떤 함수를 쓰는지, 날짜를 계산할때는 어떤 함수를 쓰는지 아래에서 알아보자.

---

2. 날짜 함수 소개

앞서 소개했듯이, 날짜 함수는 크게 두가지로 분류할 수 있다.
( 내가 만든 분류이다. 공식적인 분류는 아니다. )

• 출력 형식 지정 함수: 데이터 타입이나 출력 형태를 지정하는 함수
  
  • 대표 함수 : FORMAT_DATE , PARSE_DATE , DATE_TRUNC , EXTRACT
    
• 계산 함수 : 날짜를 계산하는 함수
  
  • 대표 함수 : DATE_ADD , DATE_SUB , DATE_DIFF
    

각 대표 함수에 대한 설명은 아래 표에서 살펴 보자.

1) 출력 형식 지정 함수

함수명	정의	문법
FORMAT_DATE	date의 날짜값을 지정된 형식의 string값으로 출력한다	FORMAT_DATE ( ‘출력하고자 하는 형식’ , 날짜 필드 )
PARSE_DATE	string 타입으로 된 날짜값을 date 타입으로 출력한다	PARSE_DATE ( ‘입력된 형식’ , 날짜 필드 )
DATE_TRUNC	날짜값을 지정된 범위까지 잘라낸다.지정된 범위 아래의 값은 내림 처림한다	DATE_TRUNC(날짜 필드 , 날짜 부분 )
EXTRACT	날짜값중 지정한 부분 (ex.월)을 뽑아낸다.	EXTRACT ( 날짜 부분 from 날짜 필드 )

2) 계산 함수

함수명	정의	문법
DATE_ADD	지정한 간격만큼 날짜를 더한다.	DATE_ADD ( 날짜 필드 , interval n Day )
DATE_SUB	지정한 간격만큼 날짜를 뺀다.	DATE_SUB ( 날짜 필드, interval n Day )
DATE_DIFF	두 날짜 끼리 빼준다. 두 날짜 사이의 간격을 구할 수 있다.	DATE_DIFF ( 날짜 필드 1 , 날짜 필드 2 , 날짜 부분 )

특히 출력 형식 지정함수에서 FORMAT_DATE과 PARSE_DATE을 헷갈려하는 경우가 많다.
( ex. 20240825 -> 2024-08-25 )

둘은 서로 역의 관계라고 생각하면 된다.

FORMAT_DATE은 date를 string으로, PARSE_DATE은 string을 date로 바꿔서 출력한다.

특히 PARSE_DATE은 날짜를 나타내는, ‘%Y-%m-%d 형식이 아닌 string값을 날짜 타입으로 변경할때 유용하다.
( ex. 20240825 -> 2024-08-25 )

DATE( ) 사용해서 날짜 타입으로 변경 많이 하는데 %Y-%m-%d 형식이 아닌 string 값은 DATE( ) 함수 사용시 에러가 난다.
이 경우 PARSE_DATE( ) 를 사용해야한다. substr과 concat함수를 이용해서 조합하는 방법도 있지만
구문이 길어지기 때문에 PARSE_DATE( ) 을 사용하는 것을 권장한다.

DB를 일별로 수집한다고 했을때 테이블의 _table_suffix가 ‘%Y%m%d’ 형식으로 되어있는 경우가 많다.
불러온 _table_suffix를 그대로 날짜 필드로 넣고 싶을때 PARSE_DATE를 쓰면 된다.
( 예시: PARSE_DATE(‘%Y%m%d’,_table_suffix) AS yyyymmdd )

---

3. Appendix

1) 다루고자 하는 날짜 타입 (Date, Datetime ,Timestamp )에 따라 함수명을 변경할 수 있다.

위에서 소개한 함수들은 DATE 타입을 기준으로 함수명을 써놓았는데 예시처럼 다 변경이 가능하다
ㄴ ex) FORMAT_DATE -> FORMAT_DATETIME , FORMAT_TIMESTAMP
ㄴ ex) DATE_ADD -> DATETIME_ADD , TIMESTAMP_ADD

참고로 DATE와 DATETIME, TIMESTAMP는 표현할 수 있는 날짜 범위에 차이가 있다.

• DATE : 날짜를 년, 월 , 일 까지 저장하고 있는 날짜 타입
  
• DATETIME : 날짜를 년 , 월 , 일 , 시 , 분 , 초 ,초 미만으로 저장하고 있는 날짜 타입
  
• TIMESTAMP : 날짜를 마이크로초단위 ( 초 이하 소숫점 6자리 ) 로 정밀하게 저장하고 있는 날짜 타입
  

TIMESTAMP는 빅쿼리상에서는 타임존이 같이 출력된다. ( ex: 2024-01-01 12:30:12.000000 UTC )
빅쿼리 자체적으로는 마이크로초로 날짜값을 저장하고있는데 ( 고정된 초기 시점 이후 경과된 마이크로초 수 )
표기시에 변환해서 출력해주면서 타임존까지 같이 나타내주는것이다.

DATETIME과의 차이는 타임존을 포함하고 있다 정도로 보면 될것같다.

2) 날짜 출력 형식은 아래 표현을 참고하면 된다.

표현	설명
%Y	년도를 십진수로 표현
%m	월을 십진수로 표현
%d	일을 십진수로 표현
%H	시간을 십진수로 표현
%M	분을 십진수로 표현
%S	초를 십진수로 표현
%F	%Y-%m-%d
%T	%H:%M:%S

---