(SQL) 빅쿼리 날짜 함수
(SQL) 빅쿼리 날짜 함수
1. 날짜 함수란?
날짜 함수란 날짜 타입 ( DATE, DATETIME ,TIMESTAMP…) 의 데이터를 다룰때 사용하는 함수이다.
날짜의 출력 형식을 지정하거나 날짜를 계산할 때 사용한다.
- Case 1 ) 날짜 출력 형식 :
- YYYY-MM-DD 형식으로 값이 입력되어 있는 날짜 필드를 YYYYMMDD 형식으로 바꾸고 싶다.
- ( ex. 2024-08-02 -> 20240802 )
- YYYY-MM-DD 형식으로 값이 입력되어 있는 날짜 필드를 YYYYMMDD 형식으로 바꾸고 싶다.
- Case 2 ) 날짜 계산 :
- 현재 날짜 기준, n일전 데이터에서 전일자 데이터까지 조회하고 싶다.
- ( ex. 2024년 8월 28일 기준 , 35일전인 데이터에서 전일자 데이터까지 조회하고 싶다 )
- 현재 날짜 기준, n일전 데이터에서 전일자 데이터까지 조회하고 싶다.
날짜 출력 형식을 지정할때는 어떤 함수를 쓰는지, 날짜를 계산할때는 어떤 함수를 쓰는지 아래에서 알아보자.
2. 날짜 함수 소개
앞서 소개했듯이, 날짜 함수는 크게 두가지로 분류할 수 있다.
( 내가 만든 분류이다. 공식적인 분류는 아니다. )
- 출력 형식 지정 함수: 데이터 타입이나 출력 형태를 지정하는 함수
- 대표 함수 : FORMAT_DATE , PARSE_DATE , DATE_TRUNC , EXTRACT
- 대표 함수 : FORMAT_DATE , PARSE_DATE , DATE_TRUNC , EXTRACT
- 계산 함수 : 날짜를 계산하는 함수
- 대표 함수 : DATE_ADD , DATE_SUB , DATE_DIFF
- 대표 함수 : 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 |
