ISO 8601 형식(yyyy-mm-ddThh:mm:ss.sssZ)으로 저장된 날짜 데이터를 MySQL에서 일반적인 datetime 형식(yyyy-mm-dd hh:mm:ss)으로 변환해야 하는 경우가 종종 있습니다. 이 글에서는 STR_TO_DATE()와 DATE_FORMAT() 함수를 활용해 이러한 변환을 수행하는 방법을 단계별로 알아보겠습니다.
1. 테이블 생성하기
먼저 예제에 사용할 테이블을 생성합니다. 여기서는 날짜를 varchar 타입의 문자열로 저장하는 테이블을 만들어 보겠습니다.
mysql> create table DemoTable668(JoiningDate varchar(200));
Query OK, 0 rows affected (0.97 sec)
2. 샘플 데이터 삽입하기
INSERT 명령을 사용하여 테이블에 레코드를 추가합니다. 이때 날짜는 yyyy-mm-ddThh:mm:ss.sssZ 형식(JavaScript의 Date 객체가 출력하는 ISO 문자열과 동일한 형태)으로 삽입합니다.
mysql> insert into DemoTable668 values('2001-01-10T06:20:00.000Z');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable668 values('2019-07-20T04:00:00.000Z');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable668 values('2016-02-12T05:10:50.000Z');
Query OK, 1 row affected (0.17 sec)3. 저장된 데이터 확인하기
SELECT 문을 사용하여 테이블의 모든 레코드를 조회합니다.
mysql> select *from DemoTable668;
위 쿼리를 실행하면 다음과 같은 결과가 출력됩니다.
+--------------------------+
| JoiningDate |
+--------------------------+
| 2001-01-10T06:20:00.000Z |
| 2019-07-20T04:00:00.000Z |
| 2016-02-12T05:10:50.000Z |
+--------------------------+
3 rows in set (0.00 sec)
4. 날짜 형식 변환하기
이제 핵심 단계입니다. 아래 UPDATE 쿼리를 사용하면 yyyy-mm-ddThh:mm:ss.sssZ 형식의 날짜를 yyyy-mm-dd hh:mm:ss 형식으로 일괄 변환할 수 있습니다.
mysql> update DemoTable668
SET JoiningDate = DATE_FORMAT(STR_TO_DATE(JoiningDate,'%Y-%m-%dT%H:%i:%s.000Z'),'%Y-%m-%d %H:%i:%s');
Query OK, 3 rows affected (0.11 sec)
Rows matched: 3 Changed: 3 Warnings: 0
쿼리 동작 원리:
- STR_TO_DATE(): 문자열로 저장된 날짜를 '%Y-%m-%dT%H:%i:%s.000Z' 포맷에 맞춰 해석하여 내부 날짜/시간 값으로 변환합니다.
- DATE_FORMAT(): 변환된 날짜 값을 '%Y-%m-%d %H:%i:%s' 포맷, 즉 'yyyy-mm-dd hh:mm:ss' 형태의 문자열로 다시 출력합니다.
참고로, 밀리초 값이 고정되어 있지 않은 경우에는 '%Y-%m-%dT%H:%i:%s.%fZ'처럼 %f 지정자를 사용하거나 SUBSTRING_INDEX() 등을 조합하여 처리할 수도 있습니다.
5. 변환 결과 확인하기
변환이 완료되었으므로 테이블 레코드를 다시 한번 조회해 보겠습니다.
mysql> select *from DemoTable668;
실행 결과는 다음과 같습니다.
+---------------------+
| JoiningDate |
+---------------------+
| 2001-01-10 06:20:00 |
| 2019-07-20 04:00:00 |
| 2016-02-12 05:10:50 |
+---------------------+
3 rows in set (0.00 sec)
이처럼 STR_TO_DATE()와 DATE_FORMAT() 두 함수만 조합하면 ISO 8601 형식의 날짜 문자열도 손쉽게 원하는 MySQL datetime 형식으로 변환할 수 있습니다. 실무에서는 애초에 DATETIME 타입 컬럼을 사용하고 애플리케이션 계층에서 형식을 제어하는 것이 더 안전하지만, 이미 문자열로 저장된 기존 데이터를 정리할 때 위 방법이 매우 유용합니다.