MySQL에서 날짜 형식이 아닌 컬럼을 ORDER BY로 정렬하는 방법

MySQL에서 날짜 데이터가 DATE 타입이 아닌 문자열(VARCHAR)로 저장되어 있으면, 단순히 ORDER BY를 사용할 경우 문자열 기준으로 정렬되어 의도한 날짜 순서와 다른 결과가 나올 수 있습니다. 이럴 때는 STR_TO_DATE() 함수를 활용하면 됩니다.

해결 방법: STR_TO_DATE 함수 사용

날짜 형식이 아닌 컬럼을 날짜순으로 정렬하려면 아래와 같은 문법을 사용합니다.

select * from yourTableName
order by
STR_TO_DATE(yourColumnName,'%d/%m/%Y') DESC;

STR_TO_DATE()는 문자열을 지정한 포맷의 날짜 값으로 변환해 주는 함수입니다. 위 예제에서는 '일/월/년(%d/%m/%Y)' 형태로 저장된 문자열을 실제 날짜로 변환한 뒤 내림차순(DESC)으로 정렬합니다.

예제 테이블 생성

문법을 이해하기 위해 먼저 테이블을 생성해 보겠습니다.

mysql> create table orderByDateFormatDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ArrivalDatetime varchar(100)
    -> );
Query OK, 0 rows affected (0.73 sec)

위 테이블은 도착 날짜를 varchar 타입으로 저장하므로, 날짜 형식이 아닌 상태로 데이터가 들어가게 됩니다.

데이터 삽입

INSERT 명령으로 몇 개의 레코드를 추가합니다.

mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('01/10/2012');
Query OK, 1 row affected (0.20 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('03/11/2010');
Query OK, 1 row affected (0.15 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('04/09/2018');
Query OK, 1 row affected (0.14 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('31/01/2019');
Query OK, 1 row affected (0.17 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('21/04/2017');
Query OK, 1 row affected (0.21 sec)
mysql> insert into orderByDateFormatDemo(ArrivalDatetime) values('23/05/2016');
Query OK, 1 row affected (0.12 sec)

저장된 데이터 확인

SELECT 문으로 테이블의 전체 레코드를 조회합니다.

mysql> select *from orderByDateFormatDemo;

실행 결과는 다음과 같습니다.

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
|  1 | 01/10/2012      |
|  2 | 03/11/2010      |
|  3 | 04/09/2018      |
|  4 | 31/01/2019      |
|  5 | 21/04/2017      |
|  6 | 23/05/2016      |
+----+-----------------+
6 rows in set (0.00 sec)

날짜순으로 정렬하기

이제 STR_TO_DATE()를 적용해 날짜 형식이 아닌 컬럼을 기준으로 내림차순 정렬해 보겠습니다.

mysql> select *from orderByDateFormatDemo
    -> order by
    -> STR_TO_DATE(ArrivalDatetime,'%d/%m/%Y') DESC;

실행 결과는 다음과 같습니다.

+----+-----------------+
| Id | ArrivalDatetime |
+----+-----------------+
|  4 | 31/01/2019      |
|  3 | 04/09/2018      |
|  5 | 21/04/2017      |
|  6 | 23/05/2016      |
|  1 | 01/10/2012      |
|  2 | 03/11/2010      |
+----+-----------------+
6 rows in set (0.04 sec)

정리 및 참고 사항

위 결과를 보면 문자열 그대로 정렬했을 때와 달리, 실제 날짜 기준으로 최신 날짜부터 과거 날짜 순서대로 올바르게 정렬된 것을 확인할 수 있습니다.

추가로 알아두면 좋은 점은 다음과 같습니다.

- 포맷 지정자 확인: 데이터가 'mm/dd/yyyy' 형식이라면 '%m/%d/%Y'처럼 포맷을 데이터에 맞게 변경해야 합니다.
- 오름차순 정렬: 오래된 날짜부터 정렬하려면 DESC 대신 ASC를 사용하면 됩니다.
- 성능 고려: STR_TO_DATE()는 행마다 함수를 실행하므로 인덱스를 활용할 수 없습니다. 데이터 양이 많다면 컬럼 타입 자체를 DATE 또는 DATETIME으로 변경하는 것이 장기적으로 더 효율적입니다.