데이터베이스를 운영하다 보면 날짜 정보가 문자열(CHAR) 타입 컬럼에 저장되어 있는 경우를 종종 접하게 됩니다. 이런 값들은 정렬이나 기간 비교 등의 작업에 제약이 따르기 때문에 DATETIME 형식으로 변환해 두는 것이 좋습니다. MySQL에서는 STR_TO_DATE() 함수를 활용하면 간단하게 처리할 수 있습니다. 지금부터 예제를 통해 단계별로 살펴보겠습니다.
1단계: 테이블 생성하기
먼저 테이블을 생성합니다. 아래 예제에서는 배송 날짜(ShippingDate)를 char 타입으로 선언했습니다.
mysql> create table DemoTable1472 -> ( -> ShippingDate char(35) -> ); Query OK, 0 rows affected (0.46 sec)
2단계: 샘플 데이터 삽입하기
insert 명령을 사용하여 테이블에 레코드를 추가합니다. 날짜는 'MM/DD/YYYY HH:MM' 형태의 문자열로 저장됩니다.
mysql> insert into DemoTable1472 values('12/31/2017 10:50');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1472 values('01/10/2018 12:00');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1472 values('03/20/2019 09:30');
Query OK, 1 row affected (0.14 sec)
3단계: 저장된 데이터 확인하기
select 문을 실행하여 테이블의 모든 레코드를 조회합니다.
mysql> select * from DemoTable1472;
실행 결과는 다음과 같습니다.
+------------------+ | ShippingDate | +------------------+ | 12/31/2017 10:50 | | 01/10/2018 12:00 | | 03/20/2019 09:30 | +------------------+ 3 rows in set (0.00 sec)
CHAR 필드를 DATETIME으로 변환하는 쿼리
다음은 REPLACE() 함수로 슬래시(/)를 콤마(,)로 바꾼 뒤, STR_TO_DATE() 함수를 이용해 datetime 형식으로 변환하는 쿼리입니다.
mysql> select str_to_date(replace(ShippingDate,'/',','),'%m,%d,%Y %T') from DemoTable1472;
실행 결과는 다음과 같습니다.
+----------------------------------------------------------+ | str_to_date(replace(ShippingDate,'/',','),'%m,%d,%Y %T') | +----------------------------------------------------------+ | 2017-12-31 10:50:00 | | 2018-01-10 12:00:00 | | 2019-03-20 09:30:00 | +----------------------------------------------------------+ 3 rows in set (0.00 sec)
자주 사용하는 포맷 지정자
STR_TO_DATE() 함수에서 자주 쓰이는 주요 포맷 지정자는 다음과 같습니다.
- %Y: 4자리 연도 (예: 2017)
- %m: 월 (01~12)
- %d: 일 (01~31)
- %H: 시 (00~23)
- %i: 분 (00~59)
- %T: 24시간제 시각 (hh:mm:ss)
참고: REPLACE 없이 바로 변환하기
구분자가 슬래시(/)라면 REPLACE() 함수 없이 포맷 문자열에 구분자를 직접 지정하여 변환할 수도 있습니다.
mysql> select str_to_date(ShippingDate,'%m/%d/%Y %H:%i') from DemoTable1472;
또한 단순 조회가 아니라 컬럼 자체를 DATETIME 타입으로 변경하고 싶다면, 새로운 DATETIME 컬럼을 추가한 뒤 UPDATE 문으로 STR_TO_DATE() 결과를 채워 넣고 기존 char 컬럼을 삭제하는 방식을 권장합니다.