DATE_FORMAT 함수로 날짜만 추출하기
MySQL에서 datetime 형태로 저장된 값 중 날짜 부분만 따로 조회해야 하는 경우가 종종 있습니다. 이럴 때 DATE_FORMAT 함수를 활용하면 원하는 형식으로 날짜만 손쉽게 추출할 수 있습니다.
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table DemoTable
(
ShippingDate varchar(200)
);
Query OK, 0 rows affected (0.25 sec)
INSERT 명령을 사용하여 테이블에 몇 개의 레코드를 삽입합니다.
mysql> insert into DemoTable values('04:58 PM 10/31/2018');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('02:30 AM 01/01/2019');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values('12:01 AM 05/03/2019');
Query OK, 1 row affected (0.06 sec)SELECT 문으로 테이블의 모든 레코드를 확인해 보겠습니다.
mysql> select *from DemoTable;
실행 결과는 다음과 같습니다.
+---------------------+
| ShippingDate |
+---------------------+
| 04:58 PM 10/31/2018 |
| 02:30 AM 01/01/2019 |
| 12:01 AM 05/03/2019 |
+---------------------+
3 rows in set (0.00 sec)
ShippingDate 컬럼에는 시간과 날짜가 함께 문자열로 저장되어 있습니다. 여기서 날짜만 추출하려면 먼저 STR_TO_DATE 함수로 문자열을 날짜 형식으로 변환한 뒤, DATE_FORMAT 함수를 적용하면 됩니다.
날짜만 선택하는 쿼리
mysql> SELECT DATE_FORMAT(STR_TO_DATE(ShippingDate, '%h:%i %p %m/%d/%Y'), '%m/%d/%Y') from DemoTable;
위 쿼리를 실행하면 시간 정보는 제외된 순수한 날짜 값만 출력됩니다.
+-------------------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE(ShippingDate, '%h:%i %p %m/%d/%Y'), '%m/%d/%Y') |
+-------------------------------------------------------------------------+
| 10/31/2018 |
| 01/01/2019 |
| 05/03/2019 |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)
형식 지정자 정리
쿼리에 사용된 주요 형식 지정자는 다음과 같습니다.
- %h : 12시간제 기준의 시간 (01~12)
- %i : 분 (00~59)
- %p : 오전/오후 표시 (AM 또는 PM)
- %m : 월 (01~12)
- %d : 일 (01~31)
- %Y : 4자리 연도
즉, STR_TO_DATE(ShippingDate, '%h:%i %p %m/%d/%Y')는 '04:58 PM 10/31/2018'과 같은 문자열을 MySQL이 인식할 수 있는 datetime 값으로 변환하고, 이후 DATE_FORMAT(..., '%m/%d/%Y')가 이 값을 '월/일/연도' 형태의 날짜 문자열로 다시 포맷팅합니다.
이처럼 두 함수를 조합하면 저장 형식이 복잡한 문자열 데이터에서도 원하는 날짜 정보만 깔끔하게 추출할 수 있습니다.