[빅쿼리/SQL] 날짜 및 시간 함수 기본기 다잡기

이번 포스팅은 빅쿼리에서 날짜 및 시간 함수를 다루는 기본적인 개념 및 방법들에 대해 정리한 내용을 담았습니다.
 

---

0. 들어가며

SQL을 다루는 다양한 DMBS가 존재하고 요즘 들어 빅쿼리를 사용하는 회사가 늘어나고 있는데요.
저도 재직 중인 회사에서 빅쿼리를 도입하게 되어 처음으로 빅쿼리를 다뤄보게 되었습니다.
그런데 지금까지 몇 가지 DBMS를 사용해 봤지만 빅쿼리는 유달리 날짜 및 시간을 다루는 부분에 민감한(?) 느낌이 들더라구요.
그래서 이번 포스팅에서는 빅쿼리에서의 날짜 및 시간 타입을 정리해 보고, 날짜 및 시간을 다루는 방법에 대해서 살펴볼 예정입니다.
 
 

---

1. 빅쿼리에서의 날짜/시간 데이터 타입

우선 날짜/시간 함수를 다루기 전에 결국 날짜/시간도 숫자로 이루어져 있으니, 빅쿼리에서 숫자와 관련된 데이터 타입을 먼저 간단하게 살펴보겠습니다.
 

1.1. 숫자 타입

데이터 타입	특징	예시
INT64	정수형 숫자 타입으로 소수점을 포함하지 않음 (INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT 포함)	100, -150
FLOAT64	부동 소수점을 가지는 숫자 타입으로 가장 가까운 근사치의 소수점을 나타냄 ('근사치'이므로 100% 정확한 소수점을 나타내는 것은 아님)	123.456e-67
NUMERIC	소수점을 포함하며 소수를 정확하게 표현할 수 있음  (소수점 아래 38개 자릿수까지 표현 가능)	13.134684151
BIGNUMERIC	소수점을 포함하며 소수를 정확하게 표현할 수 있음  (소수점 아래 76개 자릿수까지 표현 가능)	15.584684535153

 

1.2. 날짜 및 시간 타입

데이터 타입	특징	예시
DATE	날짜를 표현하는 데이터 타입 (0001-01-01 ~ 9999-12-31 까지 표현할 수 있음)	2023-03-12
DATETIME	날짜와 시간을 함께 표현하는 데이터 타입 (0001-01-01 00:00:00~9999-12-31 23:59:59.999999)	2023-03-12 20:21:00.000
TIME	시간을 표현하는 데이터 타입	20:21:00.000
TIMESTAMP	날짜와 시간을 함께 표현하며, 타임존 정보까지 포함 (빅쿼리에서는 기본적으로 UTC 기준 시간이 표현됨)	2023-03-12 20:21:00.000UTC

UTC
     국제사회가 사용하는 과학적 시간의 표준 (= 영국 기준 시간), 한국 시간은 +9 시간 해주어야 함

 
이렇게 숫자와 시간에 대한 데이터 타입을 간략하게 살펴봤는데요.
사실 주의할 점은 빅쿼리에서 TIMESTAMP는 기본적으로 UTC 기준 시간을 나타내므로, 한국 시간을 보려면 +9 시간을 해주어야 한다는 점입니다. 
이 부분을 기억하고 날짜/시간을 다루는 함수들을 살펴봅시다.
 
 

---

2. 빅쿼리에서 날짜/시간을 다루는 방법

이번에는 날짜/시간을 다루는 함수들에 대해서 살펴봅시다.
각각의 함수들을 보실 때, date / datetime / time / timestamp 가 들어가는 자리에는 다른 date / datetime / time / timestamp 가 대체되어 사용될 수 있다는 점을 알아두고 아래 내용들을 접하면 좋을 것 같습니다.
 

2.1. current_datetime()  : 현재 시간을 다루기

함수명	사용법	반환 데이터타입
current_date()	current_date() 입력하면 현재 날짜(UTC기준)가 추출됨	DATE
current_datetime()	current_datetime() 입력하면 현재 날짜 및 시간(UTC기준)이 추출됨	DATETIME
current_time()	current_datetime() 입력하면 현재 날짜 및 시간(UTC기준)이 추출됨	TIME
current_timestamp()	current_datetime() 입력하면 현재 날짜 및 시간(UTC기준)이 추출됨	TIMESTAMP

현재 날짜 및 시간을 다루는 함수는 보통 실시간 데이터를 다룰 때 where 절에서 많이 사용되는 것 같습니다.
이때 주의할 점은 함수를 사용하면 UTC 기준의 날짜/시간이 출력된다는 점입니다.
그렇기 때문에 함수의 인자로 시간대에 대한 정보를 함께 입력해주어야 합니다.
 
시간대에 대한 정보를 표현하는 방법은 크게 2가지가 있는데요.
지역 정보를 입력해 주는 방법과 더하고 빼줄 시간에 대한 정보를 입력해 주는 방법이 있습니다.
예를 들면 이런 식으로 표현할 수 있습니다.

select
    current_date() as current_d -- 현재 날짜(기본, 영국시간 기준임)
  , current_datetime() as current_dt -- 현재 시간(기본, 영국시간 기준임)
  , current_datetime('Asia/Seoul') as current_dt_asia -- 현재 시간 (Asia/Seoul 기준 = 한국 시간)
  , current_datetime('+09:00') as current_dt_add_9 -- 현재 시간 (+9 시간 = 한국 시간)

 
 

2.2. datetime() : 사용자 지정 날짜 및 시간을 다루기

함수명	사용법	반환 데이터타입
date()	DATE(timestamp_expression) 또는 DATE(datetime_expression) 을 입력하면 날짜가 추출됨	DATE
datetime()	DATETIME(date_expression[, time_expression]) 또는 DATETIME(timestamp_expression [, time_zone]) 을 입력하면 날짜 및 시간이 추출됨	DATETIME
time()	TIME(timestamp, [time_zone]) 또는 TIME(datetime) 을 입력하면 TIME 형태의 시분초가 추출됨	TIME
timestamp()	TIMESTAMP(date_expression[, time_zone]) 또는 TIMESTAMP(datetime_expression[, time_zone]) 를 입력하면 타임존을 포함한 timestamp 가 출출됨	TIMESTAMP

현재 날짜 및 시간이 아닌 원하는 날짜 및 시간을 기준으로 데이터를 추출할 때 사용하기 좋은 함수들입니다.
사용 방법은 어렵지 않지만 여기서 주의 깊게 보면 좋은 부분은 '반환 데이터타입'입니다.
 
빅쿼리는 비교적 데이터 타입에 민감해서 where 절에서 비교하고자 하는 데이터들의 타입이 동일하지 않으면 오류가 발생하더라구요.
그렇기 때문에 비교하고자 하는 데이터의 타입에 따라 적절한 함수를 사용해 주는 것이 필요합니다.
 
위 함수들을 실행시켜 보면 아래와 같은 결과가 추출됩니다.

   select 
   	date('2023-03-10 14:25:40') as date_t
  	, datetime(timestamp '2023-03-10 14:25:40', 'Asia/Seoul') as datetime_t -- 입력한 날짜에 +- 기준 지역 시간을 해줌
  	, time(timestamp '2023-03-10 14:25:40') as time_t
  	, timestamp('2023-03-10 14:25:40') as timestamp_t

 
 

2.3. extract() : 날짜 및 시간에서 필요한 정보만 뽑아오기

extract() 함수는 아래와 같은 Format으로 사용이 되는데요.

EXTRACT(part FROM date_expression)

여기서 알아두고 가면 좋을 부분은 part 부분입니다.
쉽게 말해서 '날짜/시간에서 어떤 부분을 가져올 건데?'라는 부분을 알려주는 자리라고 생각하면 되는데, 그 종류를 한번 살펴보고 시작해 봅시다.

Part	사용법	설명	반환 데이터 타입
YEAR	extract(year from 날짜/시간)	날짜/시간 중 연도 추출	INT64
QUARTER	extract(quarter from 날짜/시간)	날짜/시간 중 분기 추출	INT64
MONTH	extract(month from 날짜/시간)	날짜/시간 중 월 추출	INT64
DAY	extract(day from 날짜/시간)	날짜/시간 중 일자 추출	INT64
WEEK	extract(week from 날짜/시간)	날짜/시간 중 주 추출 (1년 중 몇번째 주인지?)	INT64
DAYOFWEEK	extract(dayofweek from 날짜/시간)	날짜/시간 중 요일 추출  (  1 = 일요일, 2 = 월요일 ... ,7 = 토요일)	INT64
HOUR	extract(hour from 날짜/시간)	날짜/시간 중 시간 추출	INT64
MINUTE	extract(minute from 날짜/시간)	날짜/시간 중 분 추출	INT64
SECOND	extract(second from 날짜/시간)	날짜/시간 중 초 추출	INT64
DATE	extract(date from 날짜/시간)	날짜/시간 중 날짜 추출	DATE
DATETIME	extract(datetime from 날짜/시간)	날짜/시간 중 날짜 및 시간 추출  (날짜/시간에 TIMESTAMP 형태가 들어가야 함)	DATETIME

위와 같이 Part 가 존재하는데요.
DATE, DATETIME과 같이 완성된 날짜/시간 타입을 Part에 넣는 게 아니라면 모두 INT64 형태의 데이터타입으로 값이 반환된다는 점 알아두시면 좋을 것 같습니다.
 
extract() 함수를 사용한 예시는 아래와 같습니다.

select 
  extract(year from current_datetime('+09:00')) as ext_yyyy
  , extract(quarter from current_datetime('+09:00')) as ext_qq
  , extract(month from current_datetime('+09:00')) as ext_mm
  , extract(day from current_datetime('+09:00')) as ext_dd
  , extract(week from current_datetime('+09:00')) as ext_week -- 1년의 몇 번째 주
  , extract(dayofweek from current_datetime('+09:00')) as ext_dow -- 1 = 일요일, 2 = 월요일 ... ,7 = 토요일
  , extract(hour from current_datetime('+09:00')) as ext_hh
  , extract(minute from current_datetime('+09:00')) as ext_minute
  , extract(second from current_datetime('+09:00')) as ext_second
  , extract(date from current_datetime('+09:00')) as ext_date
  , extract(datetime from current_timestamp()) as ext_dt

 
 

2.4. datetime_add(), datetime_sub() : 날짜 및 시간에 값을 더하고 빼주기

이번에는 날짜 및 시간을 계산하는 함수를 알아봅시다.
datetime_add()와 datetime_sub() 함수는 아래와 같은 format으로 사용됩니다.

datetime_add(날짜/시간 interval 더해줄 part)
datetime_sub(날짜/시간 interval 빼줄 part)

여기서 part는 위에서 살펴봤던 날짜/시간에 대한 part를 의미합니다.

함수명	사용법	반환 데이터타입
datetime_add()	datetime_add(시간/날짜 interval 더해줄 시간/날짜)	DATETIME
datetime_sub()	datetime_sub(시간/날짜 interval 빼줄 시간/날짜)	DATETIME

 
datetime_add() 함수를 사용한 예시는 아래와 같습니다.

select
  datetime_add(current_datetime('+09:00'), interval 30 minute) as datetime_add_10m
  , datetime_add(current_datetime('+09:00'), interval 1 week) as datetime_add_1w -- (1 week 의 결과값 = 7 day 의 결과값 )
  , current_date('+09:00') as now
  , datetime_add(current_date('+09:00'), interval 1 week) as date_add_1w -- date 형식으로 넣어도 datetime_add 를 거치면 datetime 형태로 반환됨
  , date_add(current_date('+09:00'), interval 1 week) as date_add_1w_2 -- date 형식으로 반환하기 위해서는 date_add() 함수를 써야함
  , date_add(current_datetime('+09:00'), interval 1 week) as date_add_1w_3 -- datetime 형식으로 넣으면 datetime 으로 나옴

 
위와 동일한 방식으로 datetime_sub() 함수도 사용할 수 있습니다.
 
 

2.5. datetime_diff() : 날짜 및 시간의 차이를 구하기

함수명	사용법	반환 데이터타입
datetime_diff()	datetime_diff(날짜/시간 A, 날짜/시간 B, part) 형태로 사용되며, '날짜/시간 A' - '날짜/시간 B' 의 값이 추출됩니다.	INT64

datetime_diff() 함수의 사용은 직관적으로 이해가 잘 되기 때문에 어렵지 않게 사용할 수 있는데요.
part 자리에 week를 사용할 때에는 주의할 부분이 있습니다.
week를 인식할 때 토요일에서 일요일로 넘어가는 순간을 1로 count 하기 때문에 꼭 '날짜/시간 A'와 '날짜/시간 B'의 일자 차이가 7일이 아니더라도 datetime_diff()가 1을 출력할 수 있다는 점입니다.
 
이 부분은 datetime_diff() 함수 사용 예시를 보며 살펴보시죠

  select 
  -- week 할 때 주의할 부분은, 토요일 ~ 일요일 구간에서 1 이 생기기 때문에 딱 7일이 되어야 차이 = 1 이런식으로 가지는 않음
  	current_datetime('Asia/Seoul') as current_dt
   , datetime_diff(current_datetime('+09:00'), datetime_add(current_datetime('+09:00'), interval 5 day), week) as date_diff_week1
   , datetime_diff(current_datetime('+09:00'), datetime_add(current_datetime('+09:00'), interval 3 day), week) as date_diff_week2
   , datetime_diff(current_datetime('+09:00'), datetime_add(current_datetime('+09:00'), interval 1 week), hour) as date_diff_hour

위 쿼리에 대해 간략하게 설명을 해보자면, 현재 추출 기준일은 2023년 3월 22일 수요일입니다.
5일 차이가 나는 날짜의 차이를 구한 date_diff_week_1 칼럼과 3일 차이가 나는 날짜의 차이를 구한 date_diff_week_2의 결과값이 -1과 0으로 다르다는 점을 확인할 수 있습니다.
이 부분이 '토요일과 일요일이 넘어가는 시점이 포함되었느냐? 그렇지 않느냐?'의 차이이고,
이러한 특징으로 part 자리에 week를 사용할 땐 이 부분을 고려해서 사용해야 합니다.
 
 

2.6. format_datetime() : 날짜/시간의 형태를 바꿔주기

format_datetime() 함수의 사용방법은 아래와 같습니다.

format_datetime(format string, 날짜 및 시간)

format_datetime() 함수의 인자엔 format string라는 인자가 들어가기 때문에 이 부분을 먼저 살펴봅시다.
(해당 포스팅에서는 모든 format_string 유형을 담아놓지 않고 자주 사용할만한 유형들만 넣어뒀기 때문에 모든 유형에 대해 알아보고 싶다면 링크로 이동하셔서 살펴보시면 됩니다!) 

Format String	설명	반환 예시	반환 데이터타입
%F	%Y-%m-%d 형식으로 표현한 날짜	2023-03-23	STRING
%Y	연도를 10진수로 표현	2023	STRING
%m	월을 10진수(01~12)로 표현	03	STRING
%d	한 달의 일을 10진수(01~31)로 표현	23	STRING
%k	시간(24시간제)을 10진수(0~23)로 표현 (한 자릿수 앞에는 공백이 생김)	18	STRING
%M	분을 10진수(00~59)로 표현	03	STRING
%Q	분기를 십진수(1~4)로 표현	1	STRING
%R	%H:%M 형식으로 표현한 시간	18:04	STRING
%H	시간(24시간제)을 10진수(00~23)로 표현	18	STRING
%T	%H:%M:%S 형식으로 표현한 시간	18:04:27	STRING

위와 같은 Format을 이용해서 원하는 형태의 시간/날짜를 만들어낼 수 있습니다.
예시를 한번 살펴봅시다.

select
  -- format_datetime (반환 데이터 타입 : string)
  format_datetime('%F', '2023-03-24 14:16:00.000')
  , format_datetime('%R', '2023-03-24 14:16:00.000')
  , format_datetime('%H시 %M분', '2023-03-24 14:16:00.000')

위 예시를 보면 동일한 TIMESTAMP에서 다양한 방식으로 값을 추출하는 모습을 볼 수 있습니다.
그리고 원하는 요소들을 Format string에 더 추가하여 값을 만들어낼 수도 있습니다. (위의 f2_ 열처럼 말이죠. )
기억해 둘 부분은 format_datetime()의 결과는 저렇게 날짜/시간의 모습으로 생겼지만 STRING 타입이라는 점입니다.
이렇게 format_date() 함수는 대시보드를 만들거나 시각화할 때, 시간/날짜를 원하는 방식으로 표현하기 위해 활용하기 좋은 함수라고 생각이 됩니다.
 
그런데 작업을 하다 보면 날짜/시간 데이터 타입이 아닌 숫자를 이용해서 날짜/시간 형태의 데이터를 만들어야 하는 경우가 생기는데요.
아쉽게도 그런 경우엔 format_date() 함수를 사용할 수 없습니다.
왜냐하면 format_date() 함수의 인자엔 날짜/시간 데이터타입만 들어갈 수 있기 때문입니다.
 
그러니 이러한 경우에 사용할 함수를 알아봅시다.
 
 

2.6. parse_datetime() : 문자열을 날짜/시간의 형태로 바꿔주기

parse_datetime() 함수의 사용 방법은 아래와 같습니다.

parse_datetime(Format string, 날짜/시간 양식을 갖춘 string)

parse_datetime() 함수는 format_datetime() 함수와 유사한 방법으로 사용되지만 서로 반대의 용도를 갖고 있습니다.

• format_datetime()  : 날짜/시간 타입의 데이터를 원하는 형식으로 만들어 STRING으로 추출함
• parse_datetime() : 날짜/시간 양식을 갖춘 STRING을 DATETIME 형태로 추출함

 
parse_datetime() 함수를 사용할 때 주의할 점은 format string과 날짜/시간 양식을 갖춘 string 이 동일한 형태를 가지고 있어야 한다는 점입니다.
예를 들어보겠습니다.

SELECT PARSE_DATETIME('%Y %m %d %H:%M:%S', '2023 03 24 13:45:55')
  , PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2023-03-24 13:45:55')
  , PARSE_DATETIME('%Y-%m-%d %H %M %S', '2023-03-24 13 45 55')
  , PARSE_DATETIME('%Y %m %d %H %M %S', '2023 03 24 13 45 55')

위 코드를 살펴보면 format string에 - 나 : 가 들어있다면 날짜/시간을 나타내는 string 에도 동일하게 구분자가 들어가 있는 것을 확인할 수 있습니다. (심지어 공백도 동일하게 들어가 있어야 합니다.)
만약 동일하게 구분자가 들어가지 않는다면 빅쿼리는 오류를 출력합니다.
 
만약 아래와 같이 양식이 다르다면,

SELECT 
  PARSE_DATETIME('%H %M %S', '2023 03 24 13 45 55')

아래와 같은 오류가 발생합니다.

즉, parse_datetime() 함수를 사용하고 싶다면, 

SELECT 
  PARSE_DATETIME('%H %M %S', '13 45 55')

위와 같이 동일한 양식이 입력되어야 하고,

그렇게 되면 이러한 DATETIME 형태의 데이터가 출력됩니다.
여기서 난데없이 1970-01-01 이 추가된 이유는 parse_datetime() 은 DATETIME 형태로 값을 출력하는 함수인데,
날짜에 해당하는 값이 없으니 디폴트값인 1970-01-01 이 함께 출력된 것입니다.
 
만약 시간만을 출력하고 싶다면 parse_time() 함수를 사용하면 됩니다.

SELECT 
  PARSE_TIME('%H %M %S', '13 45 55')

 
 

3. 끝마치며

이렇게 이번에는 빅쿼리에서 날짜/시간을 다루는 방법에 대해 알아봤습니다.
저의 경우에는 데이터를 다룰 때 날짜/시간에 대한 부분들을 제대로 이해하고 넘어가지 않으면 문득문득 '이게 맞나?' 하는 의심 및 불안이 생기더라고요.
사실상 모든 데이터에는 날짜/시간이 따라오기 때문에 이렇게 한번 정리를 해보는 것이 다음 스텝으로 넘어가기 위한 필요한 과정이라고 생각이 됩니다.
특히 날짜/시간 타입에 민감한 빅쿼리의 경우엔 더더욱 그렇겠죠.
 
아무튼 이번 글도 도움이 되셨긴 바라며 글을 마칩니다!
 

참고 자료

날짜/시간 함수  |  BigQuery  |  Google Cloud