[MySQL / 빅쿼리] 문자열 형태의 시간을 타임스탬프로 변경하기 (파싱)

안녕하세요, 오랜만에 돌아온 솊입니다. 

오늘은 문자열 형태의 시간을 타임스탬프 형태로 변경하는 방법을 이야기하려 해요.

저는 주로 빅쿼리를 사용하다 보니, MySQL 에서도 같은 함수로 가능할 줄 알았는데 다른 함수를 사용해야 하기에 기록겸 정보공유 겸 남겨봅니다.

 

문자열 형태의 시간?

컬럼 값이 202006100000 또는 20200610 과 같이, "날짜/시간"의 조합인 것 같은데 데이터타입이 스트링인 경우가 있습니다. 빅쿼리의 경우 타임존을 UTC 로만 제공하기에, 우리가 데이터로 활용하려면 표준한국시로 변경해주어야 하는데 스트링 형태이다보니 date / datetime 함수를 사용할 수 없습니다. 

 

그래서 이것을 다시 타임스탬프 형태로 인식하도록 변경을 해주는데요, 이 때 빅쿼리에서는 Parse_date[time] 을 사용합니다. 하지만 MySQL 에서는 Parse_date[time] 함수를 인식하지 못합니다. 어떻게 해야 할까요? 지금부터 설명해볼게요!

 

■ 빅쿼리 > PARSE_DATE[TIME]

PARSE_DATE(형식요소,컬럼명)

PARSE_DATETIME(형식요소,컬럼명)

 

빅쿼리에서는, 위 함수를 이용합니다.

예시. PARSE_DATE('%Y%m%d',YMD)

예시. PARSE_DATETIME('%Y%m%d %H%M%S',CONCAT(YMD,HMS))     -- YMD : 연월일 / HMS : 시분초 컬럼

 

결과물.

 

** 위 결과물에서, 일자와 시간 사이의 'T' 를 없애는 방법은 아래 포스팅을 참고하세요.

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

[빅쿼리] FORMAT_DATE / FORMAT_DATETIME

*형식요소는 아래 표를 참조 (빅쿼리용이며, MySQL 과는 다릅니다.)

형식 요소	설명
%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는 연도를 완전히 렌더링하는 데 필요한 만큼의 문자 수를 생성하니 유의하세요.

■ MySQL > str_to_date

str_to_date(컬럼,형식요소)

 

MySQL 은 위 함수를 이용합니다.

예시. str_to_date(YMD,'%Y%m%d')              -- YMD : 연월일 컬럼

예시. str_to_date(HMS,'%H%i%s')                -- HMS : 시분초 컬럼

 

결과물.

mysql str_to_date 결과물

*형식요소는 아래 표를 참조 (MySQL)

<!--br {mso-data-placement:same-cell;}-->

형식요소	설명
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
%u	Week (00..53), where Monday is the first day of the week; WEEK() mode 1
%V	Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v	Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal % character
%x	x, for any “x” not listed above

그럼 오늘도 좋은 하루 보내시길..^^