MySQL에서 날짜가 DD/MM/YYYY 형식의 문자열(예: 20/10/2016)로 저장되어 있으면, 단순히 ORDER BY만 사용할 경우 문자열 기준으로 정렬되어 올바른 날짜 순서가 나오지 않습니다. 이럴 때 STR_TO_DATE() 함수를 활용하면 문자열을 실제 날짜 값으로 변환한 뒤 정확하게 정렬할 수 있습니다.
기본 문법
DD/MM/YYYY 형식의 날짜를 기준으로 내림차순(Descending) 정렬하려면 다음과 같은 구문을 사용합니다.
SELECT * FROM yourTableName WHERE yourDatetimeColumnName ORDER BY STR_TO_DATE(yourDatetimeColumnName, '%d/%m/%Y') DESC;
형식 지정자의 의미는 다음과 같습니다. %d는 일(day), %m은 월(month), %Y는 4자리 연도(year)를 나타냅니다.
예제 테이블 생성
먼저 예제로 사용할 테이블을 만들어 보겠습니다.
mysql> create table DateFormatWithSelect
-> (
-> UserId int,
-> UserName varchar(100),
-> UserLoginDatetime varchar(100)
-> );
Query OK, 0 rows affected (0.58 sec)여기서 주목할 점은 UserLoginDatetime 컬럼이 DATE 타입이 아닌 varchar(문자열) 타입으로 선언되어 있다는 것입니다. 날짜가 문자열로 저장된 경우에야말로 STR_TO_DATE() 함수가 빛을 발휘합니다.
데이터 삽입
INSERT 명령으로 몇 개의 레코드를 추가합니다.
mysql> insert into DateFormatWithSelect values(101,'John','20/10/2016'); Query OK, 1 row affected (0.11 sec) mysql> insert into DateFormatWithSelect values(102,'David','21/09/2015'); Query OK, 1 row affected (0.20 sec) mysql> insert into DateFormatWithSelect values(103,'Carol','21/12/2018'); Query OK, 1 row affected (0.10 sec) mysql> insert into DateFormatWithSelect values(104,'Mike','2/8/2014'); Query OK, 1 row affected (0.16 sec) mysql> insert into DateFormatWithSelect values(105,'Sam','21/11/2017'); Query OK, 1 row affected (0.12 sec) mysql> insert into DateFormatWithSelect values(106,'Bob','21/12/2013'); Query OK, 1 row affected (0.18 sec)
저장된 전체 데이터 확인
SELECT 명령으로 테이블의 모든 레코드를 조회해 보겠습니다.
mysql> select *from DateFormatWithSelect;
실행 결과는 다음과 같습니다.
+--------+----------+-------------------+ | UserId | UserName | UserLoginDatetime | +--------+----------+-------------------+ | 101 | John | 20/10/2016 | | 102 | David | 21/09/2015 | | 103 | Carol | 21/12/2018 | | 104 | Mike | 2/8/2014 | | 105 | Sam | 21/11/2017 | | 106 | Bob | 21/12/2013 | +--------+----------+-------------------+ 6 rows in set (0.00 sec)
STR_TO_DATE()로 날짜 기준 내림차순 정렬하기
이제 UserLoginDatetime을 DD/MM/YYYY 형식의 날짜로 해석하여 내림차순 정렬하는 SELECT 쿼리입니다.
mysql> select *from DateFormatWithSelect
-> where UserLoginDatetime order by str_to_date(UserLoginDatetime,'%d/%m/%Y') desc;출력 결과
+--------+----------+-------------------+ | UserId | UserName | UserLoginDatetime | +--------+----------+-------------------+ | 103 | Carol | 21/12/2018 | | 105 | Sam | 21/11/2017 | | 101 | John | 20/10/2016 | | 102 | David | 21/09/2015 | | 104 | Mike | 2/8/2014 | | 106 | Bob | 21/12/2013 | +--------+----------+-------------------+ 6 rows in set, 6 warnings (0.00 sec)
결과를 보면 가장 최근 날짜인 2018년 12월 21일(Carol)부터 가장 오래된 2013년 12월 21일(Bob)까지 날짜 순서대로 올바르게 정렬된 것을 확인할 수 있습니다.
참고 사항
- 경고(warnings) 발생 이유: WHERE 절에서 varchar 컬럼을 불린(Boolean) 조건으로 그대로 사용하면 암시적 형변환이 일어나 경고가 표시될 수 있습니다. 실무에서는 WHERE 절을 생략하거나
IS NOT NULL처럼 명시적인 조건을 사용하는 것이 안전합니다. - DATE 타입 사용 권장: 가능하다면 처음부터 컬럼을 DATE 또는 DATETIME 타입으로 설계하고, 화면에 표시할 때만
DATE_FORMAT()함수로 원하는 형식을 지정하는 것이 성능과 데이터 정합성 측면에서 훨씬 유리합니다.