MySQL에서 문자열을 타임스탬프 형식으로 변환하는 방법
MySQL에서 VARCHAR로 저장된 날짜 문자열을 타임스탬프(날짜·시간) 형식으로 변환하려면 STR_TO_DATE() 함수와 DATE_FORMAT() 함수를 함께 사용하면 됩니다. STR_TO_DATE()는 지정된 형식에 맞춰 문자열을 날짜/시간 값으로 파싱하고, DATE_FORMAT()은 그 값을 원하는 출력 형식으로 다시 포맷해 줍니다.
아래 예제를 통해 단계별로 살펴보겠습니다.
1단계: 테이블 생성
먼저 날짜 문자열을 저장할 테이블을 만듭니다.
mysql> create table DemoTable1602
-> (
-> ReportingDate varchar(40)
-> );
Query OK, 0 rows affected (0.51 sec)
ReportingDate 컬럼은 varchar(40) 타입으로 선언되어 있으며, 'Wed Oct 02 16:10:45 IST 2019' 같은 형식의 문자열이 저장됩니다.
2단계: 데이터 삽입
INSERT 문을 사용해 샘플 데이터를 입력합니다.
mysql> insert into DemoTable1602 values('Wed Oct 02 16:10:45 IST 2019');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1602 values('Fri May 31 13:00:10 IST 2019');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1602 values('Mon Dec 31 14:20:00 IST 2018');
Query OK, 1 row affected (0.23 sec)3단계: 전체 레코드 조회
SELECT 문으로 테이블의 모든 레코드를 확인합니다.
mysql> select * from DemoTable1602;
실행 결과는 다음과 같습니다.
+------------------------------+
| ReportingDate |
+------------------------------+
| Wed Oct 02 16:10:45 IST 2019 |
| Fri May 31 13:00:10 IST 2019 |
| Mon Dec 31 14:20:00 IST 2018 |
+------------------------------+
3 rows in set (0.00 sec)
4단계: 문자열을 타임스탬프 형식으로 변환
다음 쿼리는 STR_TO_DATE()로 문자열을 파싱한 뒤, DATE_FORMAT()으로 '일-월-년 시:분:초' 형식으로 변환합니다.
mysql> select date_format(str_to_date(ReportingDate,'%a %b %d %T IST %Y'),'%d-%m-%Y %H:%i:%s') from DemoTable1602;
실행 결과:
+----------------------------------------------------------------------------------+
| date_format(str_to_date(ReportingDate,'%a %b %d %T IST %Y'),'%d-%m-%Y %H:%i:%s') |
+----------------------------------------------------------------------------------+
| 02-10-2019 16:10:45 |
| 31-05-2019 13:00:10 |
| 31-12-2018 14:20:00 |
+----------------------------------------------------------------------------------+
3 rows in set (0.03 sec)
형식 지정자 설명
쿼리에 사용된 주요 형식 지정자(format specifier)는 다음과 같습니다.
- %a — 요일 약어(Sun~Sat), 예: Wed
- %b — 월 이름 약어(Jan~Dec), 예: Oct
- %d — 일(01~31)
- %T — 24시간제 시간(hh:mm:ss)
- %Y — 4자리 연도
- %H:%i:%s — 시(00~23):분(00~59):초(00~59)
'IST'처럼 문자열에 포함된 일반 텍스트는 형식 문자열에 그대로 넣어주면 자동으로 무시됩니다.
마무리
이처럼 STR_TO_DATE()와 DATE_FORMAT()을 조합하면 'Wed Oct 02 16:10:45 IST 2019' 같은 복잡한 형식의 문자열도 손쉽게 원하는 날짜·시간 형식으로 변환할 수 있습니다. 변환된 값은 DATETIME 컬럼에 저장하거나 날짜 비교 연산 등에 활용할 수 있습니다.